First published on MSDN on Oct 11, 2016
In this blog post I am going to discuss a very little-known performance metric introduced in SQL Server 2012 that you can use to instantly get a baseline of the query performance for your system. The counters I'm referring to are under the Performance Monitor (perfmon) group SQLServer:Batch Resp Statistics (this name may be slightly different if you have a named SQL Server instance). What this set of counters represent is the count and duration of batches that have completed since SQL Server was last restarted. This allows you to keep track of what your workload looks like on your system for a given snapshot and even for trending over time. Let's look at how we can make use of these useful counters.
First, open perfmon on machine that is hosting the SQL Server service.
Navigate to the SQLServer:Batch Resp Statistics counter group. Notice that there are quite a few different counters under this group. Each counter represents a range, in milliseconds, for batch duration. The ranges are pre-determined – you can't modify them. But, that shouldn't matter too much as they are quite granular. These are metrics for batches that have successfully completed and the values they represent are aggregated over time.
Each counter has 4 different metrics (Instances in Perfmon terms) associated with it (which I borrowed from MSDN). What I am particularly interested in this case are the last two – Elapsed Time: Requests and Elapsed Time: Total (ms). I can use these two metrics for finding the average duration of batches for each counter – which will give me a pretty handy baseline for my system.
For my test, I've restarted my SQL Server and ran a few small queries so some data would show up in perfmon. I've also written an expensive query that I will run a few times to illustrate how the counters will increase. The query I am using the show the jump takes, on average, around 22 seconds to execute on my test VM. Here is the query:
SELECT ROW_NUMBER() OVER ( ORDER BY NEWID() ) FROM master..spt_values a ,master..spt_values b WHERE a.type = 'P' AND b.type = 'P' AND a.number < 1500 AND b.number < 1500I had my perfmon capture running when I executed the statement above (which happened to be the 2 nd time I had executed it). Notice the spike in ElapsedTime:Total(ms) for the Batches >= 20s and Batches <50s counter (I converted milliseconds to seconds because I'm lazy ).
IF OBJECT_ID('tempdb..#BatchResponses') IS NOT NULL DROP TABLE #BatchResponses GO SELECT * INTO #BatchResponses FROM sys.dm_os_performance_counters WHERE object_name LIKE '%Batch Resp Statistics%' AND instance_name IN ( 'Elapsed Time:Requests' ,'Elapsed Time:Total(ms)' ) GO SELECT AvgRunTimeMS = CASE WHEN bcount.cntr_value = 0 THEN 0 ELSE btime.cntr_value / bcount.cntr_value END ,StatementCount = CAST(bcount.cntr_value AS BIGINT) ,bcount.counter_name ,TotalElapsedTimeMS = btime.cntr_value ,ExecutionTimePercent = CAST((100.0 * btime.cntr_value / SUM(btime.cntr_value) OVER ()) AS DECIMAL(5, 2)) ,ExecutionCountPercent = CAST((100.0 * bcount.cntr_value / SUM(bcount.cntr_value) OVER ()) AS DECIMAL(5, 2)) FROM ( SELECT * FROM #BatchResponses WHERE instance_name = 'Elapsed Time:Requests' ) bcount JOIN ( SELECT * FROM #BatchResponses WHERE instance_name = 'Elapsed Time:Total(ms)' ) btime ON bcount.counter_name = btime.counter_name ORDER BY bcount.counter_name ASCAfter running the above query, I see the following output. I can see that 82.64% of the batches executed on my system are between 0 and 1 ms. Again, this is since my SQL Server has been last restarted. I can also see that just over 56% of the time spent executing queries on my system has been for queries that have taken between 20 and 50 seconds to execute. I'm also able to calculate that the average for these executions is right at 22.75 seconds (the AvgRunTimeMS column). So, this may be a situation, if this was a production-level OLTP system, for me to look at the queries on my system that are matching up to that range and see if I can tune them so they aren't so expensive. But – that would completely depend on my workload and what my system needs to be doing.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.