How to find the worst performing queries in SQL Server 2008?

Question

How to find the worst performing queries in SQL Server 2008?

I found the following example but it does not seem to work:

SELECT TOP 5 obj.name, max_logical_reads, max_elapsed_time
FROM sys.dm_exec_query_stats a
CROSS APPLY sys.dm_exec_sql_text(sql_handle) hnd
INNER JOIN sys.sysobjects obj on hnd.objectid = obj.id
ORDER BY max_logical_reads DESC

Taken from:

http://www.sqlservercurry.com/2010/03/top-5-costly-stored-procedures-in-sql.html

Answer 1

Finding slow performing queries with SQL Profiler

• Start SQL Profiler (preferrably on the live database).
• File -> New Trace
• Choose SQL server
• Tab filter
• Optionally set a filter on the database name
• Start the profiler (RUN)
• Save the result in a table, for example: _Mytrace, preferrably on a database server that hasn't got much to do already
• Filter the select queries
• order them by duration
• Check exectution plan for this queries

Answer 2

top 10 worst queries based on...:

SELECT TOP 10
    total_worker_time/execution_count AS Avg_CPU_Time
        ,execution_count
        ,total_elapsed_time/execution_count as AVG_Run_Time
        ,(SELECT
              SUBSTRING(text,statement_start_offset/2,(CASE
                                                           WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2 
                                                           ELSE statement_end_offset 
                                                       END -statement_start_offset)/2
                       ) FROM sys.dm_exec_sql_text(sql_handle)
         ) AS query_text 
FROM sys.dm_exec_query_stats 

--pick your criteria

ORDER BY Avg_CPU_Time DESC
--ORDER BY AVG_Run_Time DESC
--ORDER BY execution_count DESC

Answer 3

If you want to find the worst performing queries by time taken, I'd use this:

SELECT * FROM sys.dm_exec_query_stats a CROSS APPLY sys.dm_exec_sql_text(sql_handle) hnd ORDER BY total_elapsed_time/execution_count DESC

However, finding the "worst" queries often requires a bit more probing into the exec_query_stats DMV. There are lots of things to consider:

1. Worst individual queries by time taken which the above query will yield.
2. Worst cpu hogs (if you are running high on CPU) which would order by total_worker_time/execution_count
3. Queries doing the most reads which are often queries that take the longest.

Now these queries will highlight queries that have poor performance but often you might have queries with "fair" performance but get called very frequently which drives down the overall performance of your app. To find these, order the above query by total_elapsed time (or total_[whatever metric you are interested in]) and do not divide by execution_count.

Answer 4

This MSDN Magazine article provides excellent info on this topic.