SQL Server - Check performance with Björn's Constant

From time to time we are asked if there is an indicator or metric that tells us how well an SQL instance is performing at the moment. Unfortunately, there is not one but lots of metrics and usually you have to look at a lot of metrics to see how the instance is performing.

Our colleague Björn then wrote a simple question that shows the average response time to questions executed in the last 10 minutes:

 
SELECT AVG(1.0*last_elapsed_time)
FROM sys.dm_exec_query_stats
WHERE DATEDIFF(MINUTE, last_execution_time, GETDATE()) <= 10

It is unlikely to provide answers to all your performance problems but it is quite good at giving a quick indication of how heavily loaded the instance is at the moment, by logging metrics we can easily generate a report via e.g. Reporting Services where we can quickly get an overview of how our instances are doing.

Please feel free to send in your own suggestions for good metrics or other good performance indicators, preferably ones where the "depends on" factor is as small as possible.

Email to addprodba@addpro.se

/Fredrik