Windows Performance Monitor Template


By:
Overview

With the manual nature of adding counters it could be quite time consuming to have to do this manually on many servers. Luckily Perfmon actually provides a method for saving the data collector set as a template.  This template can then be used to create the same data collector set on another server.  If we right-click on the data collector set we want to export we can see in the screenshot below the option to "Save Template...".  After selecting this, pick a folder and file name and the XML template will be exported.

save perfmon template

Below is an excerpt from the template .xml file which shows the counters associated with the performance counter collector and the alert.

<PerformanceCounterDataCollector>
  <DataCollectorType>0</DataCollectorType>
  <Name>System Monitor Log</Name>
  <FileName>System Monitor Log</FileName>
  <FileNameFormat>0</FileNameFormat>
  <FileNameFormatPattern>
  </FileNameFormatPattern>
  <LogAppend>0</LogAppend>
  <LogCircular>0</LogCircular>
  <LogOverwrite>0</LogOverwrite>
  <LatestOutputLocation>C:\PerfLogs\Admin\SQL Server-System Monitor\System Monitor Log.blg</LatestOutputLocation>
  <DataSourceName>
  </DataSourceName>
  <SampleInterval>1</SampleInterval>
  <SegmentMaxRecords>0</SegmentMaxRecords>
  <LogFileFormat>3</LogFileFormat>
  <Counter>\Memory\% Committed Bytes In Use</Counter>
  <Counter>\Memory\Available MBytes</Counter>
  <Counter>\PhysicalDisk(_Total)\% Disk Time</Counter>
  <Counter>\Processor Information(_Total)\% Processor Time</Counter>
  <Counter>\SQLServer:Buffer Manager\Buffer cache hit ratio</Counter>
  <Counter>\SQLServer:General Statistics\Logins/sec</Counter>
  <Counter>\SQLServer:Locks(_Total)\Number of Deadlocks/sec</Counter>
  <Counter>\SQLServer:SQL Statistics\Batch Requests/sec</Counter>
  <CounterDisplayName>\Memory\% Committed Bytes In Use</CounterDisplayName>
  <CounterDisplayName>\Memory\Available MBytes</CounterDisplayName>
  <CounterDisplayName>\PhysicalDisk(_Total)\% Disk Time</CounterDisplayName>
  <CounterDisplayName>\Processor Information(_Total)\% Processor Time</CounterDisplayName>
  <CounterDisplayName>\SQLServer:Buffer Manager\Buffer cache hit ratio</CounterDisplayName>
  <CounterDisplayName>\SQLServer:General Statistics\Logins/sec</CounterDisplayName>
  <CounterDisplayName>\SQLServer:Locks(_Total)\Number of Deadlocks/sec</CounterDisplayName>
  <CounterDisplayName>\SQLServer:SQL Statistics\Batch Requests/sec</CounterDisplayName>
</PerformanceCounterDataCollector>
<AlertDataCollector>
  <DataCollectorType>3</DataCollectorType>
  <Name>CPU over 90%</Name>
  <Alert>\Processor(_Total)\% Processor Time&gt;90</Alert>
  <AlertDisplayName>\Processor(_Total)\% Processor Time&gt;90</AlertDisplayName>
  <EventLog>0</EventLog>
  <SampleInterval>15</SampleInterval>
  <Task>
  </Task>
  <TaskRunAsSelf>0</TaskRunAsSelf>
  <TaskArguments>
  </TaskArguments>
  <TaskUserTextArguments>
  </TaskUserTextArguments>
  <TriggerDataCollectorSet>
  </TriggerDataCollectorSet>
</AlertDataCollector> 

Once you have a template defined you can then use the following steps to create a new data collector set on another server.  You can also follow these steps to create other data collector sets using a few different predefined templates.  To start this process we need to right-click on the "Data Collector Sets"> "User- Defined" folder and select "New" > "Data Collector Set".

new perfmon data collector set 

Once selected the following window will open where you, as we did before, give the data collector set a name and select "Create from template".  Note: selecting "Create manually" will simply guide you through a process of manually selecting performance counters (or alert types) similar to what we did above for real-time data collection.

create new data collector set

After clicking "Next" you will be presented with the following window where you can either select a predefined template or click "Browse" and select the .xml template you exported earlier. 

create new data collector set

Once that is done click "Finish" and you should see another data collector set in the "User Defined" folder as shown below.

perfmon data collector sets
Additional Information

Last Update: 3/13/2019




Comments For This Article





download














get free sql tips
agree to terms