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.

Comments (12)

  1. GaspodeTheWonderDog says:

    When I execute the query against my SQL 2005 database I get an error:

    Msg 102, Level 15, State 1, Line 25

    Incorrect syntax near ‘.’.

    I just copied and pasted it from above. I realise it probably works for you but have you any idea why it doesn’t work for me?

  2. Lohndorf says:

    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.

  3. GaspodeTheWonderDog says:

    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.

  4. In some situations, using the SQLIndex property on a key in Microsoft Dynamics NAV can harm performance.

  5. In some situations, using the SQLIndex property on a key in Microsoft Dynamics NAV can harm performance

  6. This post is the first in a planned series to describe various "modern" methods for troubleshooting performance

  7. This post is the first in a planned series to describe various "modern" methods for troubleshooting

  8. One of the queries I use the most, is the pplan-cache query from this post: Simple query to check the

  9. One of the queries I use the most, is the pplan-cache query from this post: Simple query to check the

  10. One of the queries I use the most, is the pplan-cache query from this post: Simple query to check the

  11. pdj says:

    Hi Lars,

    I really like this query, but I have problems re-creating the troublesome statements in a Query window due to the prepare/execute method introduced in NAV5SP1. Could you please explain how to execute the SQL statements the-NAV-way? Either here, or in this thread: http://mibuso.com/forum/viewtopic.php?f=34&t=33102

    Thanks in advance

    Peter