350 likes | 503 Views
Performance Tuning Redefined with SQL 2008. Praveen Srvatsa Director | AsthraSoft Consulting Microsoft Regional Director, Bangalore Microsoft MVP, ASP.NET http://blogs.asthrasoft.com/praveens | praveens@asthrasoft.com. What We Will Cover.
E N D
Performance Tuning Redefined with SQL 2008 Praveen Srvatsa Director | AsthraSoft Consulting Microsoft Regional Director, Bangalore Microsoft MVP, ASP.NET http://blogs.asthrasoft.com/praveens | praveens@asthrasoft.com
What We Will Cover Performance Studio concepts (data collection, management data warehouse) How to monitor/troubleshoot performance issues using Performance Studio New performance monitoring features in SQL Server® 2008 Long-term investment: Foundations for performance monitoring in the future
Helpful Experience SQL Server Management Studio Performance Monitoring Transact-SQL (T-SQL) Level 200
Agenda Overview Architecture Components Future Plans
What Is Performance Studio? A framework that ties together collection, analysis, troubleshooting, and persistence of SQL Server diagnostics information. It consists of a suite of tools for: • Low overhead data collection • Performance monitoring, troubleshooting, tuning • Persistence of diagnostics data • Reporting Short term goals: Provide enhanced data collection and reports out of the box
Agenda Overview Architecture Components Future Plans
Performance Studio Architecture Target Target Target Target Data Collection UI (Object Explorer) Management Data Warehouse Data Collection Configuration Collection Set Reports Data Collector Data Collector Data Collector Data Collector
Agenda Overview Architecture Components Future Plans
Performance Studio Components Server Side: Data Collector • Extensible data collection infrastructure • Includes out of the box data collections required to identify and troubleshoot the most common problems for the relational engine • Support for SQL Server relational engine only, but other SQL Server services can be added in the future
Performance Studio Components Server Side: Management Data Warehouse • Data repository for baseline and historical comparisons • Aggregated reporting for multiple SQL Server instances
Performance Studio Components Client Side: Data collection configuration UI • Management data warehouse properties • General data collection properties • Collection set configuration • SQL Server dashboard based on system collection sets reports • Performance monitoring and troubleshooting • Historical data analysis based on warehouse information
Data Collector Unified data collection infrastructure • Performance and general diagnostics data Configurable and extensible • Support for adding new data providers Centralized storage Always on, low overhead • Tools for monitoring provide optimal database environment performance
Data Collector Concepts Data Provider • Source of information (for example, SQL Trace, Perform counters, DMVs, T-SQL queries, logs) Collector Type • Knows how to read and expose data from specific data providers Collection Item • Instance of a collector type • Determines data inputs and frequency
Data Collector Concepts • Collection Set • Group of collection items • Logic unit of data collection • Management Data Warehouse • Repository for data being collected (historical and baseline analysis)
Data Collector Concepts select * from dm_os_performance_counters T-SQL Queries T-SQL Collector Data Provider Collector Type Collection Item
Data Collector Concepts Collection Item 1 Collection Set … Collection Item N
Data Collector Architecture msdb SSMS SQL Agent SQL Server MDW Data Collector (dcexec.exe) Operating System Data Collector Cache
Data Collector Architecture Collection Job (SSIS runtime) Collected Data Data Collector Cache MDW Upload Job (SSIS runtime)
Data Collection Providers SQL Server 2008 • T-SQL based queries (such as DMV data) • SQL Trace • Perfmon Counters Potential candidates for future releases • WMI events • Additional collector types that may have direct insight into SQL Server process
Data Collector Extensibility Custom collector types • Provided by ISVs Collections from known sources can be easily extended • Specify new SQL Trace events for collection • Specify new performance counters for collection Custom collection sets • Based on existing Collector Types
Adding Value Collector types that can be developed: • SANs (Storage Area Network) • Health • Activity • Performance impact • Communication channels • NAS (Network Attached Storage) • Network Monitoring • VMWare (Virtualization)
Management Data Warehouse Relational database stores collect data Management Data Warehouse (MDW) objects are grouped in schemas, each schema serving a different purpose: • Core • Snapshot • Custom_Snapshot
Management Data Warehouse Core: • Defines tables and stored procedures used for organizing and identifying collected data Snapshot: • Objects (data tables, views) needed to store collected data from standard, out-of-the-box data collector types, and system collection sets Custom_Snapshot: • New data tables can be added for standard collector types or third-party collector types for user-defined collection sets
MOM Integration Microsoft Operations Manager (MOM) Server MOM Server MOM Data Warehouse MOM Data Warehouse SQL Server MOM Agent MDW MOM Agent Data Collector
MOM Integration MOM agent uses Data Collector to gather SQL Server diagnostics information Data Collector provides a coherent collection set definition for specific issues Data Collector cached data can either: • Be uploaded to the MOM data warehouse directly by the MOM agent (no MDW) • Be uploaded to MDW first, and then to the MOM data warehouse when required
Relational Engine Data Collection Sets Based on top PSS reported issues Problem characterization through out-of-the-box Data Collection Sets Working closely with Product Support Services (PSS) and Engine teams • Address most important problems first • Use right metrics for performance issues
Performance Dashboard Based on System Data Collection Sets • Server Activity • High level SQL Server and system information • Should be turned on all the time • Low overhead (that is, <5 percent) • Query Statistics • Detailed SQL Server information • Drill down to performance issues root cause • Disk Usage • General disk activity information per database
Agenda Overview Architecture Components Future Plans
Future Plans • Advanced troubleshooting • Automatic problem detection • Enhanced monitoring and alerting • Improved dynamic baselining • Tools suite
Session Summary Performance Studio concepts New performance monitoring features in SQL Server 2008 Long-term investment
For More Information Visit TechNet at:www.microsoft.com/technet
Feedback / QnA • Your Feedback is Important! Please take a few moments to fill out our online feedback form For detailed feedback, use the form at http://www.connectwithlife.co.in/vtd/helpdesk.aspx Or email us at vtd@microsoft.com • Use the Question Manager on LiveMeeting to ask your questions now!
Contact (optional slide) • Blog Address http://blogs.asthrasoft.com/praveens • Email Address praveens@asthrasoft.com