The “sys.dm_os_performance_counters” Dynamic Management View

SQL Server 2005 performance can be tracked and monitored by using performance counters.  The performance counters can be displayed by either the System Monitor (PERFMON) tool, or by using the “sys.os_exec_performance_counters” Dynamic Management View (DMV).  This DMV is new with SQL Server 2005.  This article will explore the different counters exposed by this DMV and how you can use this DMV to monitor various performance aspects of SQL Server.

sys.os_exec_performance_counters DMV

The “sys.os_exec_performance_counters” DMV, lets you use simple TSQL to obtain different SQL Server performance counters.   This view contains both instance level and database specific counters.  Some counters provided valuable information by themselves, while other counters require you to compare the difference between multiple counters, to obtain a meaningful counter value.  Below is list of the different SQL Server objects available within this DMV.  For each of these objects multiple counters exist:

SQLServer:Buffer Partition
SQLServer:User Settable
SQLServer:Databases
SQLServer:CLR
SQLServer:Cursor Manager by Type
SQLServer:Exec Statistics
SQLServer:Transactions
SQLServer:Memory Manager
SQLServer:SQL Errors
SQLServer:Buffer Node
SQLServer:Plan Cache
SQLServer:Access Methods
SQLServer:Cursor Manager Total
SQLServer:Broker Activation
SQLServer:Latches
SQLServer:Wait Statistics
SQLServer:Broker/DBM Transport
SQLServer:General Statistics
SQLServer:SQL Statistics
SQLServer:Catalog Metadata
SQLServer:Broker Statistics
SQLServer:Locks
SQLServer:Buffer Manage

As you can see, there are quite a few SQL Server objects that contain performance counters, which are exposed by this DMV.  For more information about each of these objects, please refer to the Books Online topic titled “Using SQL Server Object”.

These performance counters were available in SQL Server 2000 by retrieving information from the “master.dbo.sysperfinfo” table.  A view that represents this table has been provided in SQL Server 2005, for backwards compatibility with SQL Server 2000.  This view allows your old SQL Server 2000 code to still work and retrieve information about performance counters.  But as with any backwards compatible feature you should consider re-writing your code to use the new “sys.os_exec_performance_counters” DMV.

Examples of how to use sys.dm_os_exec_performance_counters DMV

The “sys.dm_os_exec_performance_counters” DMV can be used to retrieve a number of different SQL Server performance counters.  Depending on what performance information you are trying to retrieve will determine which counters you should use.  In order for you to better understand how to use this DMV let me go through a few examples.

For the first example, let me show you how to use this DMV to calculate the buffer cache hit ratio.  The buffer cache hit ratio value measures how well your pages are staying in the buffer cache.  The closer the buffer cache hit ratio is to 100 % the better your buffer cache is being utilized, and the better performance you will get out of your SQL Server.   Here is how you would use this DMV to calculate the buffer cache hit ratio:

SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 [BufferCacheHitRatio]

FROM (SELECT *, 1 x FROM sys.dm_os_performance_counters  

        WHERE counter_name = 'Buffer cache hit ratio'

          AND object_name = 'SQLServer:Buffer Manager') a  

     JOIN

     (SELECT *, 1 x FROM sys.dm_os_performance_counters  

        WHERE counter_name = 'Buffer cache hit ratio base'

          and object_name = 'SQLServer:Buffer Manager') b