This is the first part of a series of posts about the data collector in SQL Server 2008. The data collector captures key Windows Performance Monitor counters and internal SQL Server counters every minute in a SQL Server database. In this post, I'll show you how to use the data collector to examine the affect that modifying settings and the environment has on SQL Server performance. The focus of this post is on using this tool to determine the impact of the modifications and not on tuning SQL Server.

What Does the Data Collector Capture?

The data collector captures approximately 1,000 measurements or more every minute. The measurements are Performance Monitor counters and operating system wait statistics. The second part of this series will list the measurements. The tool's default behavior is to purge data after one week. This behavior is not always ideal, and I will revisit this issue in part 3.

How to Set up for Performance Analysis

First, I'll demonstrate the "replay with variation" option.

Method: I capture a snapshot of a database and a database load (for example, I capture from SQL Server Profiler). Assume that I am using a virtual machine to:

  • Restore the image.
  • Set the clock back to the same time.
  • Run the workload based on the time it originally went through.
  • Use the data collector to record the information.

Then, I shut down the computer and repeat these steps after I make some change. My goal is to determine which items the change affected.

How to Set up the Data Collector

Because many of you have never used the data collector, I'll walk through the steps of how to set it up:

  1. In SQL Server Management Studio Object Explorer, expand the Management node, right-click Data Collection, and then click Configure Management Data Warehouse.
    Note: This starts the Configure Management Data Warehouse Wizard.

    clip_image002

  2. In the Configure Management Data Warehouse Wizard, if the Welcome page is displayed, click Next.
  3. On the Select configuration task page, click Create or upgrade a management data warehouse, and then click Next.
    clip_image004
  4. On the Configure Management Data Warehouse Storage page, click New.
    clip_image006
  5. In the New Database dialog box, enter a database name in the Database name box, and then click OK. For example, if you are doing analysis on an Adventure Works Cycles database on March 12, enter AdventureWorks20080312 in the Database name box, and then click OK.
    clip_image008
  6. On the Configure Management Data Warehouse Storage page, click Next.
    clip_image010
  7. If you are an administrator, you do not need to add a mapping. Therefore, on the Map Logins and Users page, click Next.
    clip_image012
  8. On the Complete the Wizard page, click Finish.
    clip_image014
  9. On the Configure Data Collection Wizard Progress page, click Close when the operation is complete.
    clip_image016
  10. In Object Explorer, you can see that a new database has been added.
    clip_image018
  11. In Object Explorer, expand Management, right-click Data Collection, and then click Configure Management Data Warehouse.
  12. On the Select configuration task page of the Configure Management Data Warehouse Wizard, click Set up data collection, and then click Next.
    clip_image020
  13. On the Configure Management Data Warehouse Storage page, select today's database in the Database name box, and then click Next.
    clip_image022
  14. On the Complete the Wizard page, click Finish.
    clip_image024
  15. On the Configure Data Collection Wizard Progress page, click Close when the operation is complete.
    clip_image026
  16. In SQL Server Management Studio, expand Management, and then expand Data Collection to see new items.
    clip_image028
  17. Expand SQL Server Agent, and then expand Jobs. You will see that a new job has been created. This job cleans up the database once a day. You may want to disable this job if you want to retain the data or to collect data from multiple days.
    clip_image030

For the next day's data, follow these steps:

  1. a. Right-click Data Collection, and then click Disable Data Collection. (See the image in step 16.) The Disable Data Collection dialog box opens.
    clip_image032
  2. b. Repeat steps 1 through 6 to create a new database for the second day. clip_image034
  3. c. Click through the rest of the wizard pages.
  4. d. Return to step 11, and point the data collector to the new database.
    clip_image036

    Note: You must click the ellipsis button (…) next to the Server name box to enable the Database name box.

Remember that collecting data is automatically enabled when you finish the wizard.

Preview of Where We Are Going

The following chart shows some data that the data collector collected for three different environments. The environments were:

  • Base: A workload generator or SQL Trace replay with RCSI turned off
  • Base + Ad hoc: The above, with the addition of some super users' ad-hoc queries running concurrently with RCSI turned off
  • RCSI + Ad hoc: The Base + Ad hoc with RCSI turned on

This chart sourced data directly from the data collector database tables. If you have ever tried to make comparison charts from Performance Monitor data, you realize that this is a better approach.

clip_image038

Coming Soon

In part 2 of this series, I will list what measurements are captured. In part 3, I will discuss viewing the information and extracting the data for custom charting.

Ken Lassesen is part of the original team that created Dr. GUI of MSDN and specializes in new and resurrected commercial product architecture. He developed architecture for several Microsoft websites, including the original MSDN site and the current Microsoft Partner Network site. He's equally at home with SQL Server, XHTML, Section 508 accessibility standards, globalization, Security Content Automation Protocol (SCAP) security, C#, and ASP.NET server controls. When he is not having fun with technology, he enjoys taking lunch-break hikes in the North Cascades.