slide1
Download
Skip this Video
Download Presentation
Introducing the SQL Server 2008 Performance Data Collector by Brad McGehee August 20, 2008

Loading in 2 Seconds...

play fullscreen
1 / 23

Fall speaker line up: - PowerPoint PPT Presentation


  • 225 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Fall speaker line up:' - mike_john


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide1

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

fall speaker line up
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
sponsorship
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
today s speaker
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…

cssug follow up
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

introduction to the sql server 2008 performance data collector

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

[email protected]

here is what we are going to learn today
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)…
slide10
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.
what is the data collector
What is the Data Collector
  • Acts as a Central Data Repository (MDW data warehouse)
  • Collects Selected SQL Server Performance Data
  • Displays Performance Reports
system data collection sets
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…
disk usage collection set
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.
server activity collection set
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
query statistics collection set
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
how does the data collector work
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…
high level overview of data collector
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

how much overhead is used by the data collector
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…
how do you set up the data collector
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
what kind of reports can be produced with the data collector
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 …
take homes for today
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…
slide22
Q & A
  • Time to answer your questions.
find out more
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:
ad