SQL Server performance (2008)

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

SELECT *
FROM sys.dm_os_performance_counters

but the results are vast so it’s better to hone in on the area you are interested in. e.g.

SELECT *
FROM sys.dm_os_performance_counters
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…
1073939712
1073874176
537003264
65792
272696576

but there is no SQL documentation to show what these are.

I found the following useful

http://troubleshootingsql.com/2011/03/03/what-does-cntr_type-mean/

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.

e.g.
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

http://msdn.microsoft.com/en-us/library/aa394569%28VS.85%29.aspx

Advertisements

About davidbridge

I am a contractor for David Bridge Technology Limited specialising in database design and Web development using Microsoft technologies such as c#, MVC .net and SQL Server (full stack)
This entry was posted in Developer stuff, SQL Stuff and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s