1 / 23

Introducing the SQL Server 2008 Performance Data Collector by Brad McGehee August 20, 2008

Introducing the SQL Server 2008 Performance Data Collector by Brad McGehee August 20, 2008. Audio via phone conference (866) 218- 3342, the dial in code is 540226. Local News. SQL Saturday #9 SQL Server Innovators Guild Greenville, SC – Oct. 11, 2008 SSWUG vConference

mike_john
Télécharger la présentation

Introducing the SQL Server 2008 Performance Data Collector by Brad McGehee August 20, 2008

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Introducing the SQL Server 2008 Performance Data Collector by Brad McGehee August 20, 2008 Audio via phone conference (866) 218- 3342, the dial in code is 540226

  2. Local News SQL Saturday #9 SQL Server Innovators Guild Greenville, SC – Oct. 11, 2008 SSWUG vConference High Quality virtual event – October 1-3 Great content, speakers and production

  3. Fall speaker line up: • All face-to-face meetings hosted by CSSUG members • Oct. 1 Jason Hall, MCPD, SQL Sentry • “Introducing Resource Governor” • Oct. 22 Andy Korczynski, MCSD, Red Bull Racing • “Fast Times at SQL Server High: Data Management Inside a NASCAR Race Team.” • Nov. & Dec. – Potential Speakers • Wayne Snyder – Current President of PASS • A representative from Microsoft Premier Support

  4. Sponsorship • We also have Great Sponsors • SQL Sentry • Award Winning Software • Metrotek Learning • Courseware, labs, simulations and custom solutions. • SQLonCall • Remote DBA services and consulting

  5. Direct to You!

  6. Today’s Speaker Brad McGehee Experienced DBA Noted SQL Server MVP Founder of SQL-Server-Performance.com Director of DBA Education for Red Gate Software Renowned Speaker PASS Community Summit, Tech-Ed, etc…

  7. CSSUG Follow Up Invite friends & associates to join Email w/ PPT and Survey links Check the web Site for meeting updates www.Charlotte-SQL.org Thanks

  8. Introduction to the SQL Server 2008 Performance Data Collector Brad M. McGehee, SQL Server MVP Director of DBA Education, Red Gate Software Founder, SQL-Server-Performance.Com bradmcgehee@hotmail.com

  9. Here is What We are Going to Learn Today • How Does the SQL Server 2008 Data Collector Fit Into the Overall Picture of SQL Server Performance Tuning • What is the Data Collector • How Does the Data Collector Work • How Much Overhead is Used by the Data Collector • How Do You Set Up the Data Collector (Demo) • Data Collector Properties, SSIS Packages, Database Schema, and Jobs (Demo) • What Kind of Reports are Included with the Data Collector (Demo)…

  10. How Does the SQL Server 2008 Data Collector Fit Into the Overall Picture of SQL Server Performance Tuning • The Data Collector is just one more tool, among the many performance-related tools that come with SQL Server, that can be used by DBAs to collect and analyze performance information. • Will not replace other built-in SQL Server tools. • Great tool for DBAs who can’t afford third-party performance tools. • But, if you have third-party tools, or have written your own, the Performance Data Collector may be redundant.

  11. What is the Data Collector • Acts as a Central Data Repository (MDW data warehouse) • Collects Selected SQL Server Performance Data • Displays Performance Reports

  12. System Data Collection Sets • System Data Collection Sets are Used to Collect Performance Data. They include: • Disk Usage Collection Set • Server Activity Collection Set • Query Statistics Collection Set…

  13. Disk Usage Collection Set • Tracks the growth of database and log files and maintains historical file-related statistics. • Captures this data: • Snapshots of data file sizes obtained from sys.partitions and sys.allocation_units. • Snapshots of log file sizes obtained from DBCC SQLPERF (LOGSPACE). • Snapshots of I/O statistics from sys.dm_io_virtual_file_stats.

  14. Server Activity Collection Set • Provides an overview of SQL Server activity, SQL Server resource utilization and SQL Server resource contention. • Captures data from: • sys.dm_os_wait_stats • sys.dm_os_latch_stats • sys.dm_os_schedulers • sys.dm_exec_sessions • sys.dm_exec_requests • sys.dm_os_waiting_tasks • sys.dm_os_process_memory • sys.dm_os_memory_nodes • Additional OS and SQL Server performance counters

  15. Query Statistics Collection Set • Gathers data about query statistics, including query text, graphics execution plans, and other related data, such as CPU time, disk I/O, execution time, etc. • Captures this data: • sys.dm_exec_query_stats view • Virtually every query that runs through your server

  16. How Does the Data Collector Work • The Data Collector must be running on each instance of SQL Server 2008 that is to be monitored. • SQL Server Agent jobs, stored procedures, and SSIS packages are used to: • Collect performance data and move it to a local cache • Then move the data from the local cache to the DMW • Once the data is in the Management Data Warehouse, then reports can be run against the data. • Three reports are built-in, or you can create your own…

  17. High Level Overview of Data Collector SQL Server Instance Dedicated for Management Warehouse Databases SQL Server Instance Running the Data Collector SQL Server Instance Running the Data Collector SQL Server Instance Running the Data Collector SQL Server Instance Designated for Storing Data Management Warehouses

  18. How Much Overhead is Used bythe Data Collector • According to the latest Microsoft documentation, the Data Collector will add about 4% to the current CPU load of the SQL Server instance being monitored. • Using typical data collection settings, the Data Collector can generate about 250-300 MB of data every day per SQL Server instance being monitored. • Older data is automatically purged. You can control the schedule or accept the defaults…

  19. How Do You Set Up the Data Collector • Setting up the Data Collector is a simple process. All you have to do is to follow a wizard. • Demo Setup • Demo Results of Setup: • Jobs • SSIS Packages • DMW Tables and Schemas

  20. What Kind of Reports Can be Produced with the Data Collector • Each of the three pre-configured collection sets has its own report. • Demo each report …

  21. Take homes for today • The SQL Server 2008 Data Collector has a lot of potential for helping DBAs track performance data over time. • To take full advantage of the tool will take some time and effort to master, especially if you want to create your own data collection sets. • I suggest you begin testing on test servers first. Don’t roll out to production servers until you are comfortable that the benefit of using this tool outweigh its advantages. • We still don’t fully understand the amount of resources needed by the Data Collector and how this will affect production SQL Server instances…

  22. Q & A • Time to answer your questions.

  23. Find out more • Check these out: • SQL Server 2008 Books Online (of course) • www.SQL-Server-Performance.Com • www.SQLServerCentral.com • www.Simple-Talk.com • http://www.microsoft.com/sql/2008/default.mspx • Contact me at: • bradmcgehee@hotmail.com • www.sqlHawaii.com

More Related