CSSUG

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
Todays 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_at_hotmail.com

9
My assumptions about you

• You have been a SQL Server DBA or developer for
  six months or longer.
• You want to learn how the new features of the SQL
  Server 2008 Performance Data Collector may be
  able to help you in future performance tuning
  efforts

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

11
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 cant afford third-party
  performance tools.
• But, if you have third-party tools, or have
  written your own, the Performance Data Collector
  may be redundant.

12
What is the Data Collector

• Acts as a Central Data Repository (MDW data
  warehouse)
• Collects Selected SQL Server Performance Data
• Displays Performance Reports

13
What is the Data Collector

• The Data Collector is an optional component of
  SQL Server 2008 used to collect selected SQL
  Server performance data.
• It can collect a variety of information using
  Collection Sets. It currently comes configured to
  capture
• Disk Usage
• Query Statistics
• Server Activity
• Besides the above, additional Collection Sets can
  be manually configured to collect other data as
  well.
• Collected data is stored in a Management Data
  Warehouse, and pre-defined reports can be run
  against it from Management Studio, or custom
  reports using Reporting Services

14
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

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

16
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

17
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

18
Data Collection Types You Can Use to Create Your
Own Data Collection Sets

• Data Collection Types are built-in features of
  the Data Collector you can use to create your own
  Data Collection Sets. Data Collection Types
  include
• T-SQL Query Collector Type
• SQL Trace (Profiler) Collector Type
• Performance Counters Collector Type
• Query Activity Collector Type
• Creating your own Data Collection Sets using
  these Data Collection Types can require a fairly
  steep learning curve. Demo later

19
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

20
High Level Overview of Data Collector
SQL Server Instance Running the Data Collector
SQL Server Instance Running the Data Collector
SQL Server Instance Running the Data Collector

• SQL Server Instance Dedicated for Management
  Warehouse Databases

SQL Server Instance Designated for Storing Data
Management Warehouses
21
From Books Online
22
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

23
How to Create Your Own Profiler Data Collection
Set

• Besides the data collector sets built into SQL
  Server 2008, you can create your own.
• Creating your own data collection sets is not
  particularly easy, but can be done with a little
  work.
• As an example, lets see how you can create your
  own Profiler data collection set.
• Demo

24
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

25
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

26
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. Dont roll out to production servers until
  you are comfortable that the benefit of using
  this tool outweigh its advantages.
• We still dont fully understand the amount of
  resources needed by the Data Collector and how
  this will affect production SQL Server instances

27
Q A

• Time to answer your questions.

28
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_at_hotmail.com
• www.sqlHawaii.com