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 * FROM sys.dm_os_performance_counters
WHERE object_name= 'MSSQL$TEST1:Buffer Manager'
ANDcounter_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 * FROM sys.dm_os_performance_counters
WHERE object_name= 'MSSQL$TEST1:Latches'
ANDcounter_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.
