Performance Data Collector
Applies: SQL Server 2008 (Katmai) CTP November 2007, SQL Server 2008 Enterprise Edition
Planning for Configuring Performance Data Collector.
Configuring Performance Data Collector.
Now, let's configuring Performance Data Collector.
First, let's create the Management Data Warehouse.
Now, let's configure the Management Data Warehouse.
Please click on Next on the Welcome screen below.
In the screen below, you may want to specify a unique Management Data Warehouse, (MDW) a database where you want to collect performance data for all instances in your organization. Also, you need to specify where the performance data collected from this instance will be placed locally prior to be uploaded to MDWehouse. In our example, we will have the MDW located in the same server, however this is not a best practice.
Here we are giving the mdw_admin role to the SQLServiceAgent service account on this instance.
On the screen below, let's verify the configuration prior to complete the wizard.
Configuration is in progress ...
At the end, we have a successful MDW configuration.
Now, after stressing the server a little bit while executing some queries on the AdventureWorks database, we have some data available for our historical reports. To access them, expand Management on SQL Server Management Studio (SSMS), and later expand Data Collection.
Below you see how the performance data is stored in the local cache prior to uploading the data to the MDW.
On the screen below, we are running the Disk Usage Summary report which is part of the Disk Usage collection set.
As you can see, disk usage per database is shown.
Next, let's take a look to the Query Statistics History report.
This report shows the most expensive queries in 4 categories: CPU, duration, physical reads and logical writes.
You can click on any query and get a detailed information for that query.
At the bottom of the Query Details report, you can choose to take a look at the details for the query plans used by the query specified.
On the Query Plan Details sometimes recommendations are given to add indexes where needed based on the query specified.
Finally, let's see the reports about performance data collected from the Server, the operating system and SQL Server.
Please appreciate the detailed information related to SQL Server Waits that this report is bringing to us.
Performance Data Collector is another DBA's dream come true, and let me remind you that the data collections and the reports are extensible.
.Send mail to
questions or comments about this web site.