Performance Data Collector
The Performance Data Collector (PDC) unlike the Activity Monitor which is great at giving us an accurate representation of what is happening currently with our SQL Server. The PDC provides trend information, such as how fast your log file is growing or what the most expensive query is over the past week.
The PDC collects performance information gathered from various T-SQL statements, Windows System Monitor counters, and Windows Management Instrumentation (WMI) classes.
This information is stored on the local server, and periodically, it will upload this information to a management data warehouse.
To start collecting performance data, we need to first define the management database.
This is done by:
selecting the Configure Management Data Warehouse menu item from the Data Collection node of the Management node in Object Explorer and launching the wizard that will create the database used to store the performance results.
Once this database is created, we will need to rerun this wizard on each instance for which we want to collect data, selecting the “Set up data collection” option and where the management data warehouse is.
The wizard will then it will create the four system collection sets:
- Disk Usage
- Query Statistics
- Server Activity
- Utility Information
The default setting for data retention is 14 days but this can be modified in the Properties dialog box of each collection set.