Praveen Srvatsa Director | AsthraSoft Consulting Microsoft Regional Director, Bangalore Microsoft MVP, ASP.NET

1. 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

2. 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

3. Helpful Experience SQL Server Management Studio Performance Monitoring Transact-SQL (T-SQL) Level 200

4. Agenda Overview Architecture Components Future Plans

5. 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

6. Performance Studio Overview

7. Agenda Overview Architecture Components Future Plans

8. 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

9. Agenda Overview Architecture Components Future Plans

10. 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

11. Performance Studio Components Server Side: Management Data Warehouse • Data repository for baseline and historical comparisons • Aggregated reporting for multiple SQL Server instances

12. 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

13. 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

14. 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

15. 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)

16. Data Collector Concepts select * from dm_os_performance_counters T-SQL Queries T-SQL Collector Data Provider Collector Type Collection Item

17. Data Collector Concepts Collection Item 1 Collection Set … Collection Item N

18. Data Collector Architecture msdb SSMS SQL Agent SQL Server MDW Data Collector (dcexec.exe) Operating System Data Collector Cache

19. Data Collector Architecture Collection Job (SSIS runtime) Collected Data Data Collector Cache MDW Upload Job (SSIS runtime)

20. 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

21. 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

22. 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)

23. 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

24. 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

25. MOM Integration Microsoft Operations Manager (MOM) Server MOM Server MOM Data Warehouse MOM Data Warehouse SQL Server MOM Agent MDW MOM Agent Data Collector

26. 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

27. 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

28. 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

29. Agenda Overview Architecture Components Future Plans

30. Future Plans • Advanced troubleshooting • Automatic problem detection • Enhanced monitoring and alerting • Improved dynamic baselining • Tools suite

31. Session Summary Performance Studio concepts New performance monitoring features in SQL Server 2008 Long-term investment

32. For More Information Visit TechNet at:www.microsoft.com/technet

33. 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!

34. Contact (optional slide) • Blog Address http://blogs.asthrasoft.com/praveens • Email Address praveens@asthrasoft.com