Partager via

Simple query to check the recent performance history

  • Article

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.

Comments

  • Anonymous
    January 04, 2008
    PingBack from http://geeklectures.info/2008/01/04/simple-query-to-check-the-recent-performance-history/

  • Anonymous
    February 27, 2008
    The comment has been removed

  • Anonymous
    February 27, 2008
    Hi GaspodeTheWonderDog, Can it be that the compatibility level for either the Nav database, or for Master, is set to SQL2000? Im not sure how to check this, but you can use sp_dbcmptlevel to change it. Will be interesting if you can come back, and let us know if this is the problem.

  • Anonymous
    February 28, 2008
    I read your comment and knew you were right even before I opened the database to look! I have tested it now and yes this is the cause. I upgraded the database from an old SQL2000 database using a database restore and didn't change the compatability level. Thanks for your help. This has to be the best blog on NAV there is. Cheers, Dave.

  • Anonymous
    April 21, 2008
    In some situations, using the SQLIndex property on a key in Microsoft Dynamics NAV can harm performance.

  • Anonymous
    April 22, 2008
    In some situations, using the SQLIndex property on a key in Microsoft Dynamics NAV can harm performance

  • Anonymous
    May 12, 2008
    This post is the first in a planned series to describe various "modern" methods for troubleshooting performance

  • Anonymous
    May 13, 2008
    This post is the first in a planned series to describe various "modern" methods for troubleshooting

  • Anonymous
    October 13, 2008
    One of the queries I use the most, is the pplan-cache query from this post: Simple query to check the

  • Anonymous
    October 15, 2008
    One of the queries I use the most, is the pplan-cache query from this post: Simple query to check the

  • Anonymous
    October 28, 2008
    One of the queries I use the most, is the pplan-cache query from this post: Simple query to check the

  • Anonymous
    March 31, 2009
    The comment has been removed