Understanding Batch Requests / sec

SQL Server’s Batch Requests represents the number of SQL Statements that are being executed per second. In my opinion this is the one metric that gives the best baseline on how your server is performing. It cannot be looked at by itself however. It is necessary to correlate with other metrics (especially CPU usage) in order to get an overall understanding of how your server is performing. The overall goal of any SQL Server is to be able to achieve the most batch requests/sec while utilizing the least amount of resources.

On some busy machines I’ve worked with, this counter averaged around 25,000 batch requests/sec during peak time. This peak throughput is heavily dependent on the architectural design of the system, including procedures, tables, and index design. One notable example of this was a system I worked with whose procedures were written using loops (ugh).. The average batch requests we could ever achieve was only 200. I was very surprised to initially see this, however digging deeper into the code I became less and less shocked. Ultimately I found one piece of code that affected the throughput of the entire system. It was a scalar UDF defined as a computed column in the busiest table on the system (don’t get me started). Anyway, after rewriting that one process, the system then found batch request peaks that went over 3500! The fact that the system could achieve that number now was a big achievement. It meant the overall throughput of the system was dramatically increased 10 fold and not bottle-necked in one spot.

So pay attention to this counter and realize the goal is to achieve the greatest number of batch requests while keeping the resources low (CPU, Disk, Memory).

To add this counter, open perfmon (performance monitor), click This counter can be found in performance monitor under SQLServer:SQL Statistics: Batch Requests/sec.

Batch Requests/sec Counter

8 comments
Andy 25 Jan 2016 at 5:02 pm

Hi Derek,
Thank you for the article. It is very interesting. I am just curios that when you see the Batch/sec high, what tools did you use to find out the problematic UDF? Are you using SQL Trace? Thanks!

Carsten 18 Nov 2015 at 8:10 am

Hi. I have just startet working as a DBA. I checked the instanses I’m responsible for and I have batch req/sec around 25 – 60 on all servers. Should I do anything to raise these numbers and if so, what?
Batch Requests/sec = 56
SQL Compilations/sec = 3
SQL Re-Compilations/sec = 0

Umuhoza 29 Aug 2015 at 6:12 am

YasmineHey there, Najla! Thank you for your kind words. I live in Riyadh, and I’m sorry to say that I do not sell what I make. I cook out of passion and love for food, and I share my rciepes with anyone who asks for them. I hope that one day people here won’t need to buy anything from non-professional bakers/cooks through learning how to cook.

Chalapathi Pagidakula 15 Nov 2013 at 6:31 pm

Its very good for understanding everything about batch requests

Alex T 08 May 2013 at 6:13 am

Indeed, a lot depends on how application is written. My 16-core SQL Server, when coupled with my C++ Monte Carlo simulation engine running on another 16-core server, processes in excess of 250,000 “batch requests” per second; CPU use while doing that is 54%. Each of these “batch requests” happens to be a parametric single-line table insert; the total data-to-disk rate is about 25 MB/sec.

Derek Dieter 09 Sep 2011 at 10:22 am

Lol.. Somehow I forgot you knew about that name…

Kevin 29 Aug 2015 at 8:22 am

Hello YAMMOSA. I am a fan of your food meddling. so if you don’t mind ainrewnsg some of my simple food questions from time to time. I promise i’ll cook and learn from your delicious looking recipes someday. 1-what are some of your favorite restaurants in Riyadh? as well as your favorite dishes in those restaurants.2-what are your most hated restaurants in Riyadh?

Mike Duncan 09 Sep 2011 at 9:09 am

Canada,

Good article!

Mike

Featured Articles

 Site Author

  • Thanks for visiting!
css.php