When I first look at a server, I want to know how busy it is, where its bottlenecks are, what is SQL Server waiting on and many other things. Batch Requests/sec is one of the data points that is used to measure how busy a server is.
WHAT IS BATCH REQUESTS/SEC?
Batch Requests/sec is a performance counter that tells us the number of T-SQL command batches received by the server per second. It is in the SQLServer:SQL Statistics performance object for a default instance or MSSQL$InstanceName:SQL Statistics for a named instance.
WHAT COMPRISES A BATCH?
If I execute a stored procedure that has multiple queries and calls to other stored procedures, what will Batch Requests/sec show? Let’s test it to find out.
I created three stored procedures:
- Run four SELECT queries
- Call the first stored procedure twice
- Call the first stored procedure, call the second stored procedure and run a SELECT
CREATE PROC BatchRequestsTest1 AS SET NOCOUNT ON; SELECT DisplayName, CreationDate FROM Users WHERE Id = 1; SELECT PostId, CreationDate FROM dbo.Votes u WHERE Id = 44; SELECT * FROM PostTypes; SELECT TOP 100 * FROM Comments ORDER BY Id DESC; GO CREATE PROC BatchRequestsTest2 AS SET NOCOUNT ON; EXEC BatchRequestsTest1; EXEC BatchRequestsTest1; GO CREATE PROC BatchRequestsTest3 AS SET NOCOUNT ON; EXEC BatchRequestsTest1; EXEC BatchRequestsTest2; SELECT Text, Score, CreationDate FROM Comments WHERE CreationDate > '03/08/2015'; GO
I have no other load on my instance. Batch Requests/sec is 0 (see next section for where to find it).
When I execute BatchRequestsTest1, Batch Requests/sec turns to 1 even though BatchRequestsTest1 contains four SELECT queries. It then goes back to 0.
Batch Requests/sec is 1 when I execute BatchRequestsTest2 and then goes back to 0.
You can probably already guess what Batch Requests/sec will be when I execute BatchRequestsTest3.
Highlight all three executions and then click execute. Batch Requests/sec is 1 as all three executions are in one batch.
EXEC BatchRequestsTest1; EXEC BatchRequestsTest2; EXEC BatchRequestsTest3;
No matter how many queries are inside a batch, it will add 1 to Batch Requests/sec. For systems that have very long, complex stored procedures, Batch Requests/sec may not be a good metric to determine how busy the server is. You have to combine the metric with everything else you look at.
WHAT IF MY SCRIPT HAS GO IN IT?
Let’s look at one more example. This script has two GOs. GO signals the end of a batch of T-SQL statements in some utilities, such as Management Studio. If I execute the below script all at once, what will Batch Requests/sec be?
BEGIN TRAN; UPDATE Users SET Reputation = Reputation + 1 WHERE Id = 1; UPDATE Users SET Reputation = Reputation + 1 WHERE Id = 1; UPDATE Users SET Reputation = Reputation + 1 WHERE Id = 1; UPDATE Users SET Reputation = Reputation + 1 WHERE Id = 1; GO UPDATE Users SET Reputation = Reputation + 1 WHERE Id = 1; GO ROLLBACK TRAN;
Why is it 3? BEGIN TRAN to the first GO is one batch. The UPDATE after the first GO and to the second GO is another batch. And the ROLLBACK TRAN is the third batch.
WHERE CAN I FIND BATCH REQUESTS PER SECOND?
I use Performance Monitor, a T-SQL query or sp_BlitzFirst to check Batch Requests/sec. You could also use PowerShell, C# and many other programming languages to query performance counters.
In Performance Monitor, click the plus sign to add a counter and then navigate to the SQLServer:SQL Statistics or MSSQL$InstanceName:SQL Statistics performance object. Expand the object, select Batch Requests/sec, click Add and then click OK.
To query Batch Requests/sec via T-SQL, you have to calculate the difference of two samples over a time interval as the value is stored as a cumulative value since the SQL Server has been up.
DECLARE @v1 BIGINT, @delay SMALLINT = 2, @time DATETIME; SELECT @time = DATEADD(SECOND, @delay, '00:00:00'); SELECT @v1 = cntr_value FROM master.sys.dm_os_performance_counters WHERE counter_name = 'Batch Requests/sec'; WAITFOR DELAY @time; SELECT (cntr_value - @v1)/@delay FROM master.sys.dm_os_performance_counters WHERE counter_name='Batch Requests/sec';
Let’s look at Batch Requests/sec via sp_BlitzFirst.
If you use @ExpertMode = 1, then you’ll see it in the first result set like above and also in the fourth result set with all of the other SQL Server performance counters.
EXEC sp_BlitzFirst @ExpertMode = 1;
HOW BUSY IS YOUR SERVER?
I’ve supported servers that had Batch Requests/sec over 10,000. I know there are much busier systems out there too. Some of our clients have low Batch Requests/sec, under 1,000. Drop a comment with the Batch Requests/sec value on your busiest server during the busiest time of the busiest day.
Brent says: if I know a server well, this is the first place I look when someone says queries are slow. Maybe queries are slow because we’re dealing with 10x more queries than we usually get – maybe the web site is on fire because somebody made a pricing mistake, for example. When this metric goes up, it affects every other performance metric.