Have you ever wondered if the cntr_type column value in the sys.sysperfinfo or sys.dm_os_performance_counters output has a significant meaning or not. Well since the column value is there, it obviously has a meaning. Additionally, if the raw values represented by the output of some of the counter types is considered at face value, then your performance base lining can take a severe beating.
Each cntr_type value meaning can be found from the WMI Performance Counter Type or Windows Server Performance Counter Type documentation on MSDN.
The common counter types in SQL Server are:
PERF_COUNTER_RAWCOUNT | Decimal | 65536
Raw counter value that does not require calculations, and represents one sample.
PERF_COUNTER_LARGE_RAWCOUNT | Decimal | 65792
Same as PERF_COUNTER_RAWCOUNT, but a 64-bit representation for larger values.
PERF_COUNTER_COUNTER | Decimal | 272696320
Average number of operations completed during each second of the sample interval. NOTE: For "per-second counters", this value is cumulative. The rate value must be calculated by sampling the value at discrete time intervals. The difference between any two successive sample values is equal to the rate for the time interval used. For example, batch requests/sec is a per-second counter, it would show cumulative values.
PERF_COUNTER_BULK_COUNT | Decimal | 272696576
Average number of operations completed during each second of the sample interval. This counter type is the same as the PERF_COUNTER_COUNTER type, but it uses larger fields to accommodate larger values.
PERF_AVERAGE_BULK | Decimal | 1073874176 | Decimal | 537003264
Number of items processed, on average, during an operation. This counter type displays a ratio of the items processed (such as bytes sent) to the number of operations completed, and requires a base property with PERF_AVERAGE_BASE as the counter type.
PERF_LARGE_RAW_BASE | Decimal | 1073939712
Base value found in the calculation of PERF_RAW_FRACTION, 64 bits.
Example:
If you had the following values:
SQLServer:Plan Cache | Cache Hit Ratio | Temporary Tables & Table Variables | 381
SQLServer:Plan Cache | Cache Hit Ratio Base | Temporary Tables & Table Variables | 386
Then the Temp Table/Variable cache hit ratio percentage would be: 98.7% (approx.)
You can use the query below to get the comments for each counter type as discussed above:
select object_name,counter_name,instance_name,cntr_value,
case cntr_type
when 65792 then 'Absolute Meaning'
when 65536 then 'Absolute Meaning'
when 272696576 then 'Per Second counter and is Cumulative in Nature'
when 1073874176 then 'Bulk Counter. To get correct value, this value needs to be divided by Base Counter value'
when 537003264 then 'Bulk Counter. To get correct value, this value needs to be divided by Base Counter value'
end as counter_comments
from sys.dm_os_performance_counters
where cntr_type not in (1073939712)
Documentation on MSDN:
WMI Performance Counter Types
http://msdn.microsoft.com/en-us/library/aa394569(VS.85).aspx
SQL Server 2005 BOL Topic
sys.dm_os_performance_counters (Transact-SQL)
The broad classes of counters are as follows:
Non-computational Counter Types
http://msdn.microsoft.com/en-us/library/aa392713(VS.85).aspx
Basic Algorithm Counter Types
http://msdn.microsoft.com/en-us/library/aa384813(VS.85).aspx
Counter Algorithm Counter Types
http://msdn.microsoft.com/en-us/library/aa389384(VS.85).aspx
Timer Algorithm Counter Types
http://msdn.microsoft.com/en-us/library/aa393909(VS.85).aspx
Precision Timer Algorithm Counter Types
http://msdn.microsoft.com/en-us/library/aa392755(VS.85).aspx
Queue-length Algorithm Counter Types
http://msdn.microsoft.com/en-us/library/aa392905(VS.85).aspx
Base Counter Types
http://msdn.microsoft.com/en-us/library/aa384811(VS.85).aspx
Statistical Counter Types
http://msdn.microsoft.com/en-us/library/aa393663(VS.85).aspx
