CA Tuesday Tip: More on MS SQL Server Management Studio Reports

Discussion created by Shawn_Moore Employee on Feb 15, 2011
CA Clarity Tuesday Tip by Shawn Moore, Sr. Principal Support Engineer for 2/15/2011

SQL Server 2005 Reports

In the introduction a few weeks back, I mentioned the following reports. Today we'll look at these a little closer.

Performance - Top Queries by Total CPU Time
Performance - Top Queries by Average CPU Time
Performance - Top Queries by Total IO
Performance - Top Queries by Average IO

The "Total" based reports are good for looking at queries that may add up to high amounts of IO or CPU time, but in themselves don't appear to be a problem. Queries that run a lot, but are fast and don't appear to create a lot of reads, can still have a major impact on the system. The total based reports are good at exposing these situations. Look for queries that consume millions to billions of reads over the life of the query in the plan cache(see below for more details on the plan cache). The Performance - Top Queries by Total IO is a good way to identify these.

The "Average" based reports are good for looking for expensive single run(instance) queries. For these reports 1st look at the number of executions, they typically will be a few or less. This will give you an idea of the impact of such queries throughout the day. The average logical I/O can be a problem if it is in the hundreds of thousands and much worse in the millions. CPU can also be an indicator of a problem -- watch out for queries that consume a high % of the CPU. Sometimes queries are fast, but result in a large number of reads. More powerful hardware can mask out such a problem, but the best solution is to address the query.

A key difference between the SQL Server reports and the Oracle AWR/Statspacks is that MS SQL Server will show queries based on what is available within the plan cache. Most reports will list the following disclaimer at the top of the page

"This report identifies the queries currently residing in the plan cache that have consumed the most CPU on average each time they execute. This data is aggregated over the lifetime of the plan in the cache and is available only for plans currently in the cache."

In addition to the above reports, another useful one to know is the "Activity - All Blocking Transactions" report. This report will show situations where data base processes (spid's) are blocking others. It typically will show the set of queries that are being blocked and the one doing the blocking. (If the blocker is in a transaction, it may not show the actual query.)

Explore the different reports, you might find one that you like. You can also customize reports to display specific information you might be interested in.