SQL Server Management Studio Standard Reports - Performance - Batch Execution Statistics

SQL Server Management Studio Standard Reports - Performance - Batch Execution Statistics

Rate This
  • Comments 3
I’m working my way through the Standard Reports you get in SQL Server Management Studio. I’ve gone through several of them already, and today we finally move out of the locking and blocking reports. The next report in the list, and we’re still only on the server-level reports, is the start of the Performance reports. This one is the Batch Execution Statistics report, and it has quite a bit of information within it.

As we move through these reports, I’ll offer links, hints and tips for the various sections. I don’t have time each day to include them all, so you’ll see them spread throughout the series.

What you’re looking at in this report is a list from the latest cached query plans. What you’ll see depends on how active your server is, and how much memory it has to store the plans. Query Plans, in case you aren’t familiar with them, are just a path to the data for a given query or set of queries. If more and more users ask for the same data, it makes sense to remember how the engine got to it. So as queries are made, the system stores the path in memory. If a lot of people ask for the same thing very often, then that plan will stay in memory longer, theoretically speeding things up for everyone. As the plan gets older without anyone using it, it moves to the back of the line, and eventually falls out of memory. If that data is requested again, it has to be re-created. You can read a lot more about that here.

You can also use hints in your queries to “pin” a plan in the memory cache, if you think it will help. You might have a very expensive query, for instance, that you know won’t be called often, but when it is called you want it to have priority. That’s when you might use this feature. Of course, there’s only so much memory to go around, and pinning a plan might not be the right thing to do.

So what’s the Batch part? Well, any time you connect to a server and run a command, that’s a batch. You can run a single command or a whole bunch, but a single group is that batch.

So you can see that the information here is pretty useful when you’re diagnosing performance. You’ll notice an emphasis on read and write operations, since these can be very expensive in a query. Let’s dive in a take a look.

You start out with five graphics showing the top Batches:

Graphic Type Description
By Average CPU Time Bar Chart Average CPU time for the queries.
By Average Logical Reads Bar Chart The average number of logical reads performed by executions of this plan since it was compiled.
By Average Logical Writes Bar Chart The number of logical writes performed by executions of this plan since it was compiled.
Total CPU Time (%) by Batches Pie Chart Average CPU time for the query batches.
Total Logical IO (%) by Batches Pie Chart The total pages read from the data cache.

These graphs are followed by a series of bands in the report, arranged by the Batch ID:

Item Description
Batch Number A number that uniquely identifies the batch.
First SQL Statement of Batch This is the first SQL statement in the cache for the batch.
Avg. CPU Time (ms.) Average CPU time for the batch, in milliseconds.
Total CPU Time (ms.)** Total CPU time for the batch, in milliseconds.
# Avg. Logical Reads The average number of logical reads performed by executions of this plan since it was compiled.
# Total Logical Reads The total number of logical reads performed by executions of this plan since it was compiled.
# Avg. Logical Writes The average number of logical writes performed by executions of this plan since it was compiled.
# Total Logical Writes The total number of logical writes performed by executions of this plan since it was compiled.

This is followed by a band showing the SQL Handle for the Batch, which is an Identifier for the in-memory plan. This identifier is transient and remains constant only while the plan remains in the cache:

Item Description
SQL Statement A portion of the SQL text for this statement.
# Executions (with last plan) Number of times that the plan has been executed since it was last compiled.
# Plans Generated Total number of plans this statement has generated.
Avg. CPU Time (ms.) The average CPU time in microseconds that this statement took.
Total CPU Time (ms.) The total CPU time in microseconds that this statement took.
Last CPU Time (ms.) CPU time, in microseconds, that this statement took the last time the plan was executed.
Min. CPU Time (ms.) The least amount of CPU time that this statement took.
Max. CPU Time (ms.) The most amount of CPU time that this statement took.
# Avg. Logical Reads The average number of logical reads this statement took.
# Total Logical Reads The total number of logical reads this statement took.
# Last Logical Reads The number of logical reads that this statement took the last time it ran.
# Min. Logical Reads The least amount of logical reads this statement took.
# Max. Logical Reads The most amount of logical reads this statement took.
# Avg. Logical Writes The average number of logical writes this statement took.
# Total Logical Writes The total number of logical writes this statement took.
# Last Logical Writes The number of logical writes that this statement took the last time it ran.
# Min. Logical Writes The least amount of logical writes this statement took.
# Max. Logical Writes The most amount of logical writes this statement took.

Leave a Comment
  • Please add 2 and 2 and type the answer here:
  • Post
  • I’m working my way through the Standard Reports you get in SQL Server Management Studio. I’ve gone through

  • We're in the performance section of the series of Standard Reports you get with SQL Server Management

  • We’re in the first of the database-level reports in this series of the Standard Reports that ship with

Page 1 of 1 (3 items)