Finding the Most Expensive, Longest Running Queries in SQL Server

One of the things we often want to know is what are our worst performing statements in our application so we can fix those statements and improve our overall performance.  We can easily do this by querying the dynamic management views in SQL Server.

One of the great advantages of using the DMV's in this way is that we immediately get a global view of what our application is doing inside of SQL Server and where we might need to concentrate our performance tuning efforts.  The statement below gives us a good idea of what statements are being run from our application, how often, how long they take and how many resources they use on average.  That is a lot of information for the low cost of running a single query.

To run this query, you will need to VIEW SERVER STATE permission in SQL Server.  If you don't have this permission, ask your DBA to run this query for you and give you the results.

OK, lets look at the query

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

SELECT TOP 20 DatabaseName = DB_NAME(CONVERT(int, epa.value)), [Execution count] = qs.execution_count, [CpuPerExecution] = total_worker_time / qs.execution_count , [TotalCPU] = total_worker_time, [IOPerExecution] = (total_logical_reads + total_logical_writes) / qs.execution_count , [TotalIO] = (total_logical_reads + total_logical_writes) , [AverageElapsedTime] = total_elapsed_time / qs.execution_count, [AverageTimeBlocked] = (total_elapsed_time - total_worker_time) / qs.execution_count, [AverageRowsReturned] = total_rows / qs.execution_count, [Query Text] = SUBSTRING(qt.text,qs.statement_start_offset/2 +1, (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2 ELSE qs.statement_end_offset end - qs.statement_start_offset) /2), [Parent Query] = qt.text, [Execution Plan] = p.query_plan, [Creation Time] = qs.creation_time, [Last Execution Time] = qs.last_execution_time FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) p OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa WHERE epa.attribute = 'dbid' AND epa.value = db_id() ORDER BY [AverageElapsedTime] DESC; --Other column aliases can be used

This query selects the 20 statements in the current database that have the longest average elapsed time, that is, the 20 statements that on average took the longest to run.  

• If you want more statements, then you can modify the "TOP 20" statement in line 1.  You could also just remove the "TOP 20" criteria to get all of the statements running in this database.
• You can sort the list by different criteria.  CpuPerExecution and IOPerExecution are also good choices because these tell you what statements take the most resources to run.
• If you have a batch process, you may get a few statements that were only run once or twice at the top of this list, because statements run in a batch process tend to be expensive by nature.  Just add a criteria in the WHERE clause to only show statements with an execution count > 10, 20 or whatever if you want to exclude these.

What units is the data in?

• The elapsed time and CPU time numbers are in microseconds, but the MSDN page claims these values are only accurate to the millisecond
• The number of IO operations is in number of pages

What should I look for?

• Statements that take a long time to run on average.  For line of business applications, I want to understand anything taking over 500 ms.  But if a statement takes 400 ms and every other statement takes only 50 ms, then I want to look at the 400 ms statement and see what is going on.
• Any statement that has a high amount of average CPU or IO usage relative to its peers.
• Statements with high numbers of executions.  Is this legit?  Is the app firing off this statement more than necessary?  Is there a caching opportunity here?

The query above will return the execution plan for each statement in this list, so you can quickly click into the plan to see what is happening and if you want to take further action

What if I want to see a different database on my SQL Server?

• This query is set up to look at the current database the user is in (line 25 - epa_value = db_id()).  So just switch to the other database or comment out this line to see data for all statements across SQL Server

How current is the data returned by this query?

• The data for this query comes from SQL Server's plan cache, which generally holds onto execution plans for statements that have been executed in the last few hours.  Its a good idea to run this query a few different times throughout the day to capture any different loads that might be put on the database.  Most of the data will probably agree, but a few different runs will give you a more complete overall picture.

In my opinion, this is probably the single most useful query of the DMV's because it immediately tells me if there are statements that aren't performing well and which ones they are.  If you have a performance issue in your database, this query will take you right to the heart of what is going on.