Perfmon: Creating a SQL Server Data Collector Set

A Performance Monitor Data Collector (DC) Set is used to automatically log performance counters into a binary file for analysis later. We will configure the DC to automatically start each data and after server restarts. It will also be configured to create a compressed file (CAB) of the binary log file for smaller storage. This example will use a DC template that RKL uses for SQL Server performance counter logging.

Configure Base Data Collector Set

1. Copy Perf_SQL_Tempate.xml to target machine
2. Replace SQL Instance name in template.
3. Open Perfmon and open the new Data Collector Set wizard
4. Name the DC, keep create from template then click next

Figure 1 - Name the Data Collector Set

5. Browse to the Perf_SQL_Template.xml and click next

Figure 2 - Selecting Template

6. Choose a location to save the DC logs and click next

Figure 3 - Select the Log Location

7. Choose Run As Default and Open Properties for this data collector set and click finish
8. You can review the settings that were imported from the template. This will setup the DC to run starting every day at midnight and run for 1439 minutes. Close the properties dialog when completed.
9. Verify Data Management Settings – Right click on the new Data Collector Set and click Data Manager. The Retention actions and disk limits will be set per the template.

Figure 4 - Handling Account Dialog

Note: If you get the Account Dialog with the User Name of NT AUTHORITY\SYSTEM. At this point I haven’t determined what permission the logged in user needs in the case. However, if you clear the NT AUTHORITY\SYSTEM from the dialog and click ok, windows accepts the change.

Configure Task Scheduler

Task Scheduler needs to be configured to ensure that the perfmon data collector set starts up after a machine restart and at the beginning of each day.

1. Open Task Scheduler
2. Navigate to the PLA node in the console tree, under Task Scheduler Library > Microsoft > Windows > PLA
3. Find the Scheduled task that was created when the DC was created. Right click on this task and click properties
4. Add a new Trigger – Set this to Begin the Task: At Start Up

Figure 5 - Task Scheduler Trigger

5. On the settings tab check the Stop the Task if it runs longer than 3 days and change the already running rule to “Stop the existing instance”

Figure 6 - Task Scheduler Settings

At this point you can run the task from task scheduler and it will start collecting data. The next morning just after midnight it will stop the running task and start it up again logging to a new file.