sp_PerformanceCounters - Get a Health Check of SQL Server's Performance

During your career as a DBA, you'll run across articles by SQL experts or other DBAs that just give you an idea.  A few years ago I found an article, or a poster to be more precise, that explains SQL Server performance counters.  It was written by Kevin Kline, Brent Ozar, Christian Bolton, Bob Ward, Rod Colledge, and Raoul Illyaos.

http://www.quest.com/backstage/images/promotions/SQLServer-Perfmonance-Poster.pdf

I was thinking this would be a nice poster for any DBA to hang up in their cubical.  But as I read down near the bottom, I saw they also mentioned getting performance counters from within SQL Server via sys.dm_os_performance_counters.  Ah ha!  My light bulb just turned on!

In an earlier post, I had discussed how to get performance counters from within SQL Server, so for this post I want to share a stored procedure that pulls the counters as described in the poster as well a few more.

The procedure is sp_PerformanceCounters, and it will aggregate overall performance data that is available in the sys.dm_os_performance_counters DMV.  The metrics are gathered for various counters and objects and displayed in 7 columns.

1. PerformanceObject - The counter category.
2. CounterName - Name of the counter.
3. InstanceName - The specific instance of the counter; often the database name.
4. TimeFrame - The timeframe of the counter.
5. ActualValue - The value of the counter.
6. IdealValue - A generalized optimal value for the counter.
7. Description - A brief description of the counter.

The most important thing to understand from the output is to understand the timeframe of the metrics, and how that impacts the actual value.  There are three possible TimeFrames: Current, Total since SQL startup, and Avg since SQL startup.

For the TimeFrames that are "Current", those are counter type 65792 and are the absolute values that do not need any special definition.  For example, you can select the number of database pages in the buffer pool, and the value returned is the current value.

SELECT * FROM sys.dm_os_performance_counters

WHERE object_name = 'MSSQL$TEST1:Buffer Manager'

AND counter_name = 'Database Pages';

The second TimeFrame "Total since SQL startup" is also easy to interpret.  This counter is also of type 65792, and it's just the accumulated total of a counter since SQL was started up.  For example, you can select the number of log growths from the databases counter which would tell you the total number of log growths for all databases since SQL Server was last started.

SELECT * FROM sys.dm_os_performance_counters

WHERE object_name = 'MSSQL$TEST1:Databases'

AND counter_name = 'Log Growths'

ORDER BY instance_name;

The last and most common TimeFrame is "Avg since SQL startup", which is for counters of type 272696576.  The value of these counters get incremented every time that event is fired and are mostly related to "per second" counters.  To figure out the per second value we have to divide by the total uptime (in seconds) of SQL Server.  This will give us the output displayed in the ActualValue column.  This is also one reason why the SQL Server startup time is the first row displayed in the output.  The SQL Server startup time is taken from the sys.dm_os_sys_info DMV.

SELECT * FROM sys.dm_os_performance_counters

WHERE object_name = 'MSSQL$TEST1:SQL Statistics'

AND counter_name = 'Batch Requests/sec';

Most counter names will match exactly what you see in sys.dm_os_performance_counters; however, there will be a few that are calculated as a ratio. One example is the Page Lookup / Batch Request.  This counter will show you the average number page lookups that occurred per batch request.  Both of these individual counters are of type 272696576 which means they are "per second counters".  However, for this ratio counter we don't care about the per second value, we just want total of Page Lookups divided by the total of Batch Requests.

DECLARE @TempValue1 DECIMAL(25,5), @TempValue2 DECIMAL(25,5)

SELECT @TempValue1 = cntr_value

FROM sys.dm_os_performance_counters

WHERE object_name = 'MSSQL$TEST1:Buffer Manager'

AND counter_name = 'Page lookups/sec';

SELECT @TempValue2 = cntr_value

FROM sys.dm_os_performance_counters

WHERE object_name = 'MSSQL$TEST1:SQL Statistics'

AND counter_name = 'Batch Requests/sec';

-- This is to avoid divide by zero.

IF @TempValue2 <> 0

    SELECT @TempValue1/@TempValue2 AS 'Page lookups/Batch Requests';

ELSE

    SELECT 0;

All aggregated data is stored in a temporary table #PerformanceCounters and then displayed at the end, and is best viewed using the "Results to Grid" output in SSMS.

EXEC master.dbo.sp_PerformanceCounters;

GO

Most of the ideal values and descriptions were taken from the poster mentioned above.  My disclaimer with that is the real ideal value will always depend on your specific application and setup.  This stored procedure was created to be used an overall health check for a server.  It's especially useful if another DBA just handed you a new SQL Server to support, and you need a quick way to see a brief history of its performance.

The stored procedure was written for SQL Server 2005 and 2008.  It will work on SQL Server 2012, but there were a lot of changes with the counters so I will have an updated officially supported version for 2012 soon.

Click here to download the full script.