Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Performance Data Collection and Warehouse Feature of SQL Server 2008 Part 2


By:   |   Read Comments (1)   |   Related Tips: 1 | 2 | More > Performance Data Warehouse

Attend these FREE MSSQLTips webcasts >> click to register


Problem

As we are looking through the new features in SQL Server 2008 we found a potentially interesting one called Performance Data Collection and Warehouse feature which we can use to store performance data from multiple SQL Servers Instances within an organization into a single central location. In a previous tip you discussed how to configure this feature in SQL Server 2008. Can you give us a detailed explanation of how to analyze the data which is captured using this feature?

Solution

The Performance Data Collection and Warehouse is a new feature in SQL Server 2008 which can be used by database administrators to gather performance related data using the built-in data collectors which are also known as data collection containers. Using data collectors you can gather performance data from multiple SQL Servers and store it within a Management Data Warehouse (MDW).

Enabling System Data Collection Sets

To start collecting the performance data you need to expand "Management" node in Object Explorer, expand "Data Collection", expand "System Data Collection Sets" and then right click "Disk Usage" and select "Start Data Collection Set" option from the drop down list as shown in the snippet below.

Similarly you need to "Start Data Collection Set" for both "Query Statistics" and "Server Activity".

Reporting Capabilities of Management Data Warehouse (MDW)

Once you have successfully configured the Management Data Warehouse (MDW) with three different Data Collection Sets which are Disk Space, Query Statistics and Server Activity. There are many built in reports which you can use for analysis. These reports are basically created using SQL Server Reporting Services and they can be run from within the SQL Server Management Studio (SSMS). One good thing about these reports is that they will run even if you haven't installed SQL Server 2008 Reporting Services.

Each System Data Collection Set has one parent report which provides a high level overview of data which is stored within the Management Data Warehouse. Let's go through these reports one by one.

Disk Usage Summary Report

The "Disk Usage Summary" report provides an overview of the disk space used by all the databases on the server and the growth trends for each of the data and log files for each database during the sample interval. Within the ManagementDB database all the necessary tables for a specific report are created the very first time when the collected data is uploaded. You can upload the collected data for "Disk Usage" collection set by expanding the "Management" node in Object Explorer, expand "Data Collection", expand "System Data Collection Sets" and then right click "Disk Usage" and select "Collect and Upload Now" option from the drop down list as shown in the snippet below.

You can view the Disk Usage Report by expanding the "Management" Node and then right click on "Data Collection" and in the drop down list point to "Reports", point to "Management Data Warehouse", and then click on "Disk Usage Summary" as shown in the snippet below.

Using the Disk Usage Summary Report you can quickly identify the growth trend for the data and log files for each database during the sample interval.

Moreover, if you are interested in knowing more detailed information for a particular database then you can click the database to open up the detailed report for the specific database.

Query Statistics History Report

The Query Statistics History report provides an overview of query execution statistics for the SQL Server instance during the sampled interval. Within the ManagementDB database all the necessary tables for a specific report are created the very first time when the collected data is uploaded. You can upload the collected data for "Query Statistics" by expanding Management node in Object Explorer, expand "Data Collection", expand "System Data Collection Sets" and then right click "Query Statistics" and select "Collect and Upload Now" option from the drop down list.

You can view the Query Statistics History report by expanding "Management" Node and then right click "Data Collection" and in the drop down list point to "Reports", point to "Management Data Warehouse", and then click "Query Statistics History".

The Query Statistics History report has three different sections. In the top section you can navigate through the historical snapshots of the data using the time line. The second section represents the graphical representation for the resources. The five different types of resource usages are CPU, Duration, Total I/O, Physical Reads and Logical Writes. In the last section you will be able to see top 10 queries based on the resource usage which you selected in the second section. To know more details about the queries resource usage you can click on the hyperlink in the Query column.

Server Activity History Report

The Server Activity History report provides an overview of resource consumption and server activity for the SQL Server instance and for the host operating system during the sampled interval. Within the ManagementDB database all the necessary tables for a specific report are created the very first time when the collected data is uploaded. You can upload the collected data for Server Activity by expanding "Management" node in Object Explorer, expand "Data Collection", expand "System Data Collection Sets" and then right click "Server Activity" and select "Collect and Upload Now" option from the drop down list.

You can view the "Server Activity History" report by expanding "Management" Node and then right click "Data Collection" and in the drop down list point to "Reports", point to "Management Data Warehouse", and then click "Server Activity History".

The Server Activity History report displays graphs for CPU Usage, Memory Usage, Disk I/O Usage, Network Usage, SQL Server Waits Statistics and SQL Server Activity. These graphs will help you to identify the resource consumption trend on the SQL Server Instance. You can click on the graphs to drill down and get more detailed information.

Managing the Data Retention Period in Management Data Warehouse (MDW)

The default Data Retention Period in MDW is 4 weeks. If you want to reduce the data retention period in MDW then you can do this by expanding Management node, expand "Data Collections", expand "System Data Collections Sets" and then right click the particular data collection and select Properties from the drop down list. This will open up Data Collection Set Properties dialog box as shown in the below snippet. Here, you can change the Collection Frequency (sec) value and you can also change the default retention period by altering the value for Retain data for 28 days.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Ashish Kumar Mehta Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, October 18, 2017 - 1:51:09 PM - Andrea Back To Top

 

I have the following error when I try to consult the information stored for the collector Server Activity:

 

"NO WAIT STATISTICS DATA HAS BEEN UPLOADED FOR THE SPECIFIED TIME INTERVAL"

 

The graphics its showed incomplete. No error in the jobs history for Data collector set 2 and 3. Just for Data collector set 1 job


Learn more about SQL Server tools