Share via

  • Article

Performance Counters Collector Type

You can use the Performance Counters collector type to collect specific performance counter information from a computer that is running SQL Server 2008. You can use this data later as a baseline for troubleshooting or for capacity planning. This collector type is registered in the core.supported_collector_types view.

This collector type has the following input parameters:

  • Objects. The SQL Server objects running in an instance of SQL Server.

  • Counters. The counters associated with a SQL Server object.

  • Instances. The instances of the specified object.

For more information about objects and counters, see Using SQL Server Objects.

Note

Some input parameters support wildcard characters, which enable you to include multiple counters in one statement. However, wildcard character support is limited. You cannot use a wildcard character at the Objects level. At the Counters and Instances levels, support is only provided for a wildcard character at the beginning of the string (for example, '* Processor') or at the end of the string (for example, 'Memory *').

Performance Counters Input Schema

The Performance Counters collector type uses the following schema for input parameters.

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" targetNamespace="DataCollectorType">
  <xs:element name="PerformanceCountersCollector">
    <xs:complexType>
      <xs:sequence>
        <xs:element minOccurs="0" maxOccurs="unbounded" name="PerformanceCounters">
          <xs:complexType>
            <xs:attribute name="Objects" type="xs:string" use="required" />
            <xs:attribute name="Counters" type="xs:string" use="required" />
            <xs:attribute name="Instances" type="xs:string" use="optional" />
          </xs:complexType>
        </xs:element>
      </xs:sequence>
      <xs:attribute name="StoreLocalizedCounterNames" type="xs:boolean" use="optional" default="false" />
    </xs:complexType>
  </xs:element>
</xs:schema>

The following code example shows how to use the schema.

<ns:PerformanceCountersCollector xmlns:ns="DataCollectorType">  
<PerformanceCounters 
      Objects="SQLServer:Locks" 
      Counters="Lock *" 
      Instances="_Total" 
      /> 
<PerformanceCounters 
      Objects="SQLServer:SQL Statistics" 
      Counters="*/sec" 
      /> 
<PerformanceCounters 
      Objects="Processor" 
      Counters="% Processor Time" 
      Instances="*"
      /> 
</ns:PerformanceCountersCollector>

This example will result in counters for the "_Total" instance, with names that begin with "Lock", to be retrieved for the SQLServer:Locks object. It will also retrieve all instances of all counters that end with "/sec" for the SQLServer:SQLStatistics object. Finally, it will retrieve the "% Processor Time" counter for all instances of the Processor object.

Processing and Output

The Performance Counters collector type uses the collection and upload packages provided by the data collector. This collector type depends on the Performance Data Helper API for querying and obtaining data from performance counters. For more information, see Using the Performance Data Helper Library on MSDN.

When data collection is finished, the data collector bulk uploads the data to the snapshots.performance_counters view in the management data warehouse. For more information, see The Management Data Warehouse.