SQL Server Management Studio Standard Reports – Performance – Top Queries by *

I'm continuing my series on the Standard Reports in SQL Server Management Studio. Today I'm covering four reports at a time – the performance reports that are broken down by average and total times. The reason I’ll cover all four of them is that they have exactly the same information, just arranged in a different way.

Before we get started, let me tell you where these reports are. We are still in the server-level reports, which span all the activity on a system. You can find these reports by starting SQL Server Management Studio and right-clicking an instance name. From the menu that appears, click the “Standard Reports”, and then select one of these:

• Performance – Top Queries by Average CPU Time
• Performance – Top Queries by Average IO
• Performance – Top Queries by Total CPU Time
• Performance – Top Queries by Total IO

These reports are useful to identify and track down issues related to long-running queries. Normally queries create performance degradation in one of two (or both) components: the CPU and the IO system. The IO system is simply the transfer of data back and forth to the storage unit. This might mean there is pressure on the controller, the SAN, the local drive or even the path between them all. Either way, you’re looking for a spike in the graph that you can dig into for more info, all arranged by the query number shown on the graph.

As I mentioned, the reports contain the same columns but slightly different graphics, so I’ll describe them once here, and explain what each of the columns mean.

The first section contains two graphs. They show an average of the object being measured, and the other shows the total count of the object. Which one comes first is determined by the report you’re looking at.

After the graphics, the first band of the report has two columns:

Query Number	A number that represents a tie to the graph for this query statement.
Query Text	The first line of query text from the query statement.
Database Name	The name of the database that the query is working against.
Object ID	The number that uniquely identifies an object that this statement is working on.
Counter	A counter that is the same name as the report – it shows the totals or averages that show what the report measures.

Selecting the plus-sign next to the SQL text expands the data, and contains the following info:

Compilation Time	The time spent compiling the statement.
Last Execution Time	A timestamp showing the last time this statement executed.
Total CPU Time (ms)	Total CPU time spent, in microseconds, by the workers executing this statement.
Average CPU Time (ms)	Average CPU time spent, in microseconds, by the workers executing this statement.
# Logical Reads	The number of logical IO reads the statement has taken so far. More about that here.
# Logical Writes	The number of logical IO writes the statement has taken so far. More about that here.
# Total Logical IO	The combination of read and write operations for this statement.
# Executions	The number of statement executions in this query.