LinkedIn | FaceBook | Twitter
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:
These graphs are followed by a series of bands in the report, arranged by the Batch ID:
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:
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