RKL eSolutionsBlogs

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.

Perfmon 05 - Creating a SQL Server Data Collector Set_edit

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

Perfmon 05 - Figure 01 - Name the Data Collector Set

Figure 1 - Name the Data Collector Set

  1. Browse to the Perf_SQL_Template.xml and click next

Perfmon 05 - Figure 02 - Selecting The Template.

Figure 2 - Selecting Template

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

Perfmon 05 - Figure 03 - Select Log Location.

Figure 3 - Select the Log Location

  1. Choose Run As Default and Open Properties for this data collector set and click finish
  2. 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.
  3. 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.

Perfmon 05 - Figure 04 - Handling Account Dialog

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

Perfmon 05 - Figure 05 - Task Scheduler Trigger

Figure 5 - Task Scheduler Trigger

  1. 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”

Perfmon 05 - Figure 06 - Task Scheduler Settings

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.

Tagged With: Sage 500, Sage X3, How-To
John Puttman

Written by John Puttman

Questions? Need Support?
Get in Touch