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

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.