SQL uses dynamic management views to show loads of very useful information about itself and its performance.
One of the more useful dmv’s for performance tuning is sys.dm_os_performance_counters
You can query this simply like this
but the results are vast so it’s better to hone in on the area you are interested in. e.g.
where counter_name = 'Page Life expectancy'
One area for confusion however, is cntr_type. What does it mean?
On my system (64 bit Windows 2008 server with SQL 2008 R2 Enterprise) these values exist…
but there is no SQL documentation to show what these are.
I found the following useful
Basically this explains that this “type” column shows how the data is stored.
i.e. is it a point in time counter, one which accumulates, a min or max value or one that requires further calculations.
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.
The following link shows the main page for the WMI performance counters in Windows