What is Batch Requests/sec?

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:

  1. Run four SELECT queries
  2. Call the first stored procedure twice
  3. Call the first stored procedure, call the second stored procedure and run a SELECT
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.

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?

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.

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.

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.

Previous Post
Simulating Workload With ostress And Agent Jobs
Next Post
When THREADPOOL Waits Lead To RESOURCE_SEMAPHORE Waits

39 Comments. Leave new

  • Batch Requests per Sec 446
    CPU Utilization 32%.
    SQL Compilations per Sec 67
    SQL Re-Compilations per Sec 8
    Wait Time per Core per Sec 0

    Reply
  • James Youkhanis
    February 15, 2017 9:03 am

    Brent covers this topic in detail in his Performance Tuning class, this is an excellent training course that I took last month. My Batch Requests per second was 153.7

    Reply
  • medium-busy time of day (right now) 4566 batch/sec

    Reply
  • Busy days > 130000 batch/sec

    Reply
  • Busy time 10-11K

    Reply
  • Uh-oh. Third party app, probably less than 30 users: 2593392

    Reply
  • We had a server that was doing around 10K+ Batch Request per sec until a recent update.
    It is now down to under 2K+ per sec after a recent update. This update was prior to tripling (3 times more) the amount of user and server applications that connect to a single database.
    The difference in CPU utilization was like we had added another 8 cores.
    The difference in database updates to code that existed in the database was nill.
    What changed?
    The application was updates to not make a new all database call with a new SQL connection object for every database call.
    Now each page and DAL section of code executes in single batches.
    This means we see when tracing only one RPC reset and reconnect per page load instead of 100+.
    Short Story: Don’t make a new database connection and database command for every single database call.
    Gather them together in batches that populate a local dataset in the DAL.
    BTW – Using LINQ and MVC default data connectors does not reduce batches. They seemed to have bee designed to increase them in the name of better Sargasim

    Reply
  • 742 batch requests… CPU @7%

    Reply
  • 21.000 batch/sec on x3950 with 60cores. TempDB and Log files on FusionIO raid.

    Reply
  • About 75,000. The App shreds very moderately-sized import files (500 MB to 2 GB) into individual XML records then pushes them into BizTalk where a bunch of business rules run on each record.

    Reply
  • DESCRIPTION: The SQL Server performance counter ‘Batch Requests/sec’ (instance ‘N/A’) of object ‘SQL Statistics’ is now above the threshold of 2500.00 (the current value is 3058.45).
    Nordic OLTP system, Wednesday 14:29. Lot’s of small SP:s running.

    Reply
  • Laurens Bakker
    February 16, 2017 2:34 am

    ~200 requests/sec, and those are generally trivial queries.
    I get the feeling that this 32-core, 64 GB RAM machine I inherited is a bit overkill 😛

    Reply
  • 1 day window
    SQL % Processor Time min 21%/48 avg 587%/48 max 4652%/48
    Batch/s min 500 avg 74820 max 339920
    Compilation/s min 0 avg 409 max 2760
    re-compilation/s min 0 avg 0,02 max 5

    Reply
  • 5000 odd..

    Reply
  • 1,500

    Reply
  • Good stuff. It takes just one bad batch to screw all the others, though 🙂

    Reply
  • Hello,

    Is it possible to get the batch requests per second but not for the entire server but for databases and hard drives?

    Would different data usage patterns have an impact on overall performance of a database? Let’s say there are two databases A and B, both of them with similar amounts of total bytes read and written.
    But database A has performed those writes and reads in let’s say 20 batch requests per second, while
    database B has performed those writes and reads in 200 batch requests per second.

    Further more, on database A the behavior generally is: write once, read many times, while on database B for every write operation there is also a read operation.

    I would create those two databases on different hard drives, but I am not sure if letting them on the same drive would do much harm. Would there be some performance degradation either for database A or B if the lie on the same disks?
    Do you have an advice for this kind of setup?

    Thank you!

    Reply
  • my busiest instance is 2,063,669,731 batch/sec.
    least busy instance is 4,255,782 batch/sec

    Reply
  • I posted wrong number.
    Correct number should be:
    busiest instance is 5465 batch/sec.

    Reply
    • That makes more sense than the other numbers. I doubt there’s any system in the world with batch requests/sec that high on a single instance. If a system is going to be busy like that, then you’ve scaled out to multiple instances.

      Reply
  • Batch Requests/sec 48507770
    SQL Compilations/sec 551908
    SQL Re-Compilations/sec 34445

    Seems really high to me!

    Reply
    • What source is it from? I suspect you are querying it via TSQL and are looking at the wrong column which is showing you how many there have been since the server has been online and not a per-second value.

      Reply
      • Actually, not “wrong column”, but you need to do a calculation to get the per-second value. Run the query, wait 5 seconds, run it again. Do a diff between the values and divide by 5. You can use sp_BlitzFirst to look at this data too, or Performance Monitor.

        Reply
      • Thank you, you were correct! It was since the server had been online.

        Reply
  • Felipe Schneider
    October 9, 2017 1:55 pm

    Hello, i have on AVG 9,224 and a MAX of 22,289. This isn’t good right? The fun part is that in that server it works perfect. But in the PROD Environment with AVG 524 and MAX 1,010 work like garvage 😛

    Reply
    • Felipe – there’s nothing good or bad about it, it’s just how many queries you’re handling. The next step is to find out what those queries are, though.

      Reply
  • Danielle paquette-Harvey
    November 10, 2017 11:39 am

    Hey Brent,
    My server has about 10k requests/sec, but I’m beginning to see deadlocks et I/O bottlenecks. Does that mean my hardware/setup has reached it’s limits? I’m trying to figure out if it would be a good time to have a second SQL Server but I have to be sure before asking my boss. Currently we have 135 client’s databases on one server (approx 7500 users), with clients working 24/7, supporting mobile and desktop devices. My guts tells me we’ve reached our limits but I wanted to have your opinion.
    Thanks!

    Reply
    • Danielle – well, when it’s time to add additional servers, that’s usually beyond the scope of something we can recommend fast in a blog post comment. For example, you just might need some index tweaks, or maybe your backups aren’t even matching your RPO/RTO. That’s where our consulting comes in – click Consulting up at the top of the page to get an idea for what we do. Thanks!

      Reply
  • Glenn Brown
    June 20, 2018 9:43 pm

    Load testing…
    2 vCPU. 4000 batch req per sec.
    MAX Compilations per sec – 125.
    MAX Recompilations per sec – 47.
    MAX Processor queue length 25 !

    No complaints from users.
    Priceless…

    Reply
  • Mohammed Adil Ameen
    September 7, 2018 7:40 am

    I had a Microsoft support call, where the engineer requested to increase the CPU as the Batch request per sec was 2000-4000 and CPU 23% on a 32Core box 😀

    Reply
  • Is there any way to see the historical value for Batch request per second.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}