Pages

Tuesday, December 4, 2012

Get Performance Counters from within SQL Server

I think most DBAs know that you can use the Windows
Performance Monitor to capture performance metrics for your SQL Server. But did you know that you also have access to
all the SQL Server performance counters from within SQL Server?

By using using the DMV sys.dm_os_performance_counters, you
can view that performance data. As with
all DMVs, there are a few drawbacks. The
data collected is only valid since the last time SQL Server was started, and
this DMV does not allow access to any of the Window OS performance counters.

For example, we can query the DMV to find the Page Life
Expectancy.

SELECT*FROMsys.dm_os_performance_counters

WHEREobject_name='MSSQL$TEST1:Buffer
Manager'

AND
counter_name ='Page
life expectancy';

The DMV returns five columns:

object_name - Category of the counter.

counter_name - Name of the counter.

instance_name - Name of a specific instance of the counter. For example, the database name.

cntr_value - The current value of the counter.

cntr_type - The type of the counter and will vary depending on the Windows architecture.

For this example, the counter value is 3074 which means a
data page will stay in the buffer pool for 3074 seconds before being
removed. The cntr_type is very important
for each of the counters. A type of 65792
is PERF_COUNTER_LARGE_ROWCOUNT which means the cntr_value is the absolute
meaning and no more calculations are needed.

Now let's looks at another one that does require extra calculations.

SELECT*FROMsys.dm_os_performance_counters

WHEREobject_name='MSSQL$TEST1:Latches'

AND
counter_name LIKE'Average
Latch Wait Time%';

The Average Latch Wait Time (ms) is type 1073874176 -
PERF_AVERAGE_BULK. This is essentially
the same as the total wait time for all latches. The Average Latch Wait Time Base is type
1073939712 - PERF_LARGE_ROW_BASE. It's
the number of processes that had to wait for a latch. In order to find the true "average"
wait time, you will need to divide the "Average Latch Wait Time (ms)"
by its base value. In this case, 16296/378 =
43.1ms.

For more information about all the counter type definitions, check out MSDN.

As you can see, there is wealth of information within this DMV,
but you need to understand the types of each counter and don't just look at the
overall value.