Simple query to check the recent performance history

The query described in this blog is a variant of a query described in KB 935395 on Partnersource (login required). While this KB article described issues specific to SQL Servers plan-cache, the query has proved to be very useful in general performance troubleshooting of SQL installations.

The query is using Dynamic Management Views (DMVs), which were introduced in SQL Server 2005. So it will not work for SQL 2000.

It gives you an immediate view of the top 30 plans currently in cache, ordered by number of reads (or writes with a small change). So it gives you a view of the queries that are most likely to cause the most performance problems. In this way, it does what you would have otherwise had to use SQL Profiler for, but without the overhead of SQL Profiler, or the need to spend many hours browsing through 1000s of lines of details in Profiler traces.

So, here is the query:

SELECT TOP 30
st.text,
    SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
    ((CASE statement_end_offset
        WHEN -1 THEN DATALENGTH(st.text)
        ELSE qs.statement_end_offset END
            - qs.statement_start_offset)/2) + 1) as statement_text,
    execution_count,
   case 
  when execution_count = 0 then null
  else total_logical_reads/execution_count
   end as avg_logical_reads,
      last_logical_reads,
      min_logical_reads,
      max_logical_reads,
      case 
  when execution_count = 0 then null
  else total_logical_writes/execution_count
   end as avg_logical_writes,
      last_logical_writes,
      min_logical_writes,
      max_logical_writes,
      max_elapsed_time 
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
ORDER BY max_logical_reads DESC --change here to sort by max_logical_writes

The query is read-only. It does not cause any locks or any noticeable overhead to SQL Server. So I would recommend anyone with a SQL 2005 installation to try to run it. This is what it shows:

Every time a query is sent to SQL Server, SQL Server makes a query-plan for that query. It then caches this plan to re-use it for identical queries. This plan-cache also collects statistics about how efficiently each query-plan was run. This query looks into the plan-cache and retrieves the plans, with the one causing the most reads at the top. It returns (among other things) the following information:

text and statement_text:
This shows you the query that this plan is being used for. Remember, the same plan can be used again and again for identical queries.

Execution_count:
Shows you how many times the plan was used. If this shows 1, the plan may have been for a one-off query, and it may not be relevant to investigate it further. If it shows a high count, then the plan is for a common query, and you may want to investigate further where this query came from.

After this, there are a number of self-describing columns, showing you statistics about number of reads and writes for each plan.

The query can easily be changed, to order by writes instead of reads - just change the ORDER BY clause in the last line.

Advantages:
The query is a very simple tool to get some very useful information out of SQL Server. In some cases, it can identify the same problems as SQL Profiler, but in a much simpler and quicker way.
The query is completely risk-free, and can often show some very useful information.

Disadvantages:
As mentioned, the query will not work for SQL Server 2000 (or earlier versions).
SQL Server's plan-cache is very dynamic, and it changes many times every hour. So the results of the query can easily differ from one hour to the next. So it will only give you a snapshot of current cache - not full statistics since SQL Server was first started.

Lars Lohndorf-Larsen

Escalation Engineer

These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.