Quick and Dirty SQL Server Slow Query Log

by bhartsock on Dec.16, 2008, under Uncategorized

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.

:SQL Server

9 Comments for this entry

drewski
December 18th, 2008 on 10:57 am
Fantastic!
JC
January 15th, 2009 on 10:06 am
Is there a similar query that will work on SQL server 2000?
Alex
May 10th, 2009 on 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
Dave
June 1st, 2009 on 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.
MUKESH
July 13th, 2009 on 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,
blestab
November 6th, 2009 on 4:54 pm
anyone with the SQL2000 version?????
Craig M. Rosenblum
February 2nd, 2010 on 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?
bhartsock
February 4th, 2010 on 9:37 am
Sorry, I haven’t seen anything for SQL 2000.
Craig M. Rosenblum
February 4th, 2010 on 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.