Performance Monitoring in SQL Server 2008

One of the problems that SQL Server DBAs face is the fact that performance problems can sometimes be very hard to track down unless you happen to be at the right place at the right time. Microsoft made this a little bit easier with the SQL Server 2005 Performance Dashboard (which I wrote about back in May 2007 in this blog entry) but that still didn't address many of the problems.

In SQL Server 2008, Microsoft has included an entire infrastructure devoted to ensuring DBAs have the information necessary to perform adequate performance tuning. A key component of this new infrastructure is the new Performance Data Collector. The Data Collector is made up of the following components: (Yes, this is direct out of Books Online, I didn't feel like re-creating the diagram)

One of the more interesting components is the Management Data Warehouse(MDW). The MDW is a user database that is used as a repository for performance data collection and is also used to generate reports.

Data Collector Architecture

The Data Collector itself is basically a series of SQLAgent jobs, SSIS packages and a specific API that allows client tools to connect and read/report on the data collected. Broken down into components, the architecture looks like this:

If you want to read more about the Data Collector architecture and how it all fits together, check out the following SQL Server 2008 books online (local copy) article: ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_4deptrbl/html/b2c2bd5e-62c5-4129-a252-ef9439a05487.htm

Configuring the Data Collector

In order to use the Data Collector in SQL Server 2008, you must first initialize the Management Data Warehouse. You do this through Management Studio, under Management/Data Collection, right-click and select "Configure Management Data Warehouse" which brings up the following wizard page:

Click Next, and then select the local instance of SQL Server, and select "New" to create a new Database. Give it a name (I chose MgmtDataWarehouse) and then select a file system location to be used as a cache location (in order to ensure that SQL Server doesn't get overloaded collecting performance data, the filesystem is used as a temporary cache location for all "external" performance collections). The wizard page should look like this:

Click Next, and then select the appropriate permissions on the Data Warehouse (Basically you want to give system administrators full access to the warehouse and any DBAs read access) as shown in this wizard page:

Once you configure security properly, select next and then Finish, which should report success on all tasks as shown in the summary page:

Once you do this, Data Collection is now working on the server. You can verify this in Management Studio by looking in the Management/Data Collection/System Data Collection Sets as shown below:

Once the data collector is configured and operational, the system will start collecting performance information and uploading the data to the management data warehouse.

I will continue this blog post later after the server has time to collect some meaningful performance statistics.