Quick and Dirty SQL Server Slow Query Log

Optimizing SQL server can be a daunting task. There is a fairly large learning curve, and knowing where to start can be a problem. Over the weekend, I read over a great article on query optimization for SQL Server and found the greatest query ever.

SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(qt.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2)+1), 
qs.execution_count, 
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY qs.total_logical_reads DESC

This query will list the top 20 most read intensive queries. What is great is it is based on total reads, so it is a quick and dirty way to find the worst queries in your system. Eliminating the top few queries on this list will give you the best bang for your buck.

Post to Twitter Post to Delicious Post to Digg Post to Facebook Post to Reddit

Time Tuesday, December 16th, 2008, 5:32 pm   |   CategoryUncategorized  |  RssRSS feed |  CommentRespond  |  TrackbackTrackback

Tags:

10 Responses to “Quick and Dirty SQL Server Slow Query Log”

    
  2. JC
    January 15, 2009 at 10:06 am

    Is there a similar query that will work on SQL server 2000?

  3. Alex
    May 10, 2009 at 6:23 am

    Hi i found this really useful,

    I actually found that looking at the full qt.text field is useful as it sometimes shows extra info and I put in a filter to stop this query actually being in the results see below.

    /*Debug query */
    SELECT TOP 100
    qt.text as QUERY,
    qs.execution_count,
    qs.total_logical_reads, qs.last_logical_reads,
    qs.min_logical_reads, qs.max_logical_reads,
    qs.total_elapsed_time, qs.last_elapsed_time,
    qs.min_elapsed_time, qs.max_elapsed_time,
    qs.last_execution_time,
    qp.query_plan
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
    WHERE qt.encrypted=0
    and qt.text not like ‘/*Debug query */%’
    ORDER BY qs.last_execution_time DESC, qs.total_logical_reads DESC

  4. Dave
    June 1, 2009 at 6:23 pm

    Note that this query requires SQL Server 2005 or newer with the database running in compatibility mode 90 or higher. Otherwise, it throws a syntax error after the CROSS APPLY.

  5. MUKESH
    July 13, 2009 at 10:28 am

    Hi,

    Is there any solution to get back the table after drop commmand.

    I did it by restoring the backup but last transaction lost.

    If you can help me for future purpost.

    I am using SQL 2000

    thanks and regards,

  6. blestab
    November 6, 2009 at 4:54 pm

    anyone with the SQL2000 version?????

  7. Craig M. Rosenblum
    February 2, 2010 at 12:05 pm

    Is there a version of this for sql2000? My database is 2005, but we’ve not yet converted to 2005 compatibility mode, so i can’t use this script, even tho i want to so badly lol..

    any suggestions?

  8. bhartsock
    February 4, 2010 at 9:37 am

    Sorry, I haven’t seen anything for SQL 2000.

  9. Craig M. Rosenblum
    February 4, 2010 at 1:47 pm

    I can run the query in ssms, but i can’t create a stored procedure that uses it, because of permissions issue.

    10. Trackbacks

    

Leave a Reply