Advanced Query Inspection

Pretty much every DBA who gets a breather from their daily maintenance would like to spend some time tracking down those particularly gnarly SQL queries that are hosing their system. This post will give you a strategy for both how to identify them and then how to clean them up. First the Giant Disclaimer: it is not the DBA's job to write application level SQL. Even if you have the knowledge and skills to do so, once you cross the line from support to development you will find it difficult to allocate sufficient time to your actual DBA duties. Stick with being the operational DBA, and when you find queries that are poorly optimized (or just plain wrong) throw them over the wall for a developer to fix. As part of that process you may certainly suggest what needs to be changed and how to do so (in fact you rather have an obligation to provide this guidance) but don't check the stuff out of source control and make the changes yourself.

Once I'm done using the canned package bad-query tools mentioned in this previous post, I am ready to dig in deeper for the bad boys by running this DMV inquiry:

SELECT   TOP (20) execution_count AS [Number of Executions],      total_worker_time / execution_count AS [Average CPU Time],
     total_elapsed_time / execution_count AS [Average Elapsed Time],
         (SELECT   SUBSTRING(text, sys.dm_exec_query_stats.statement_start_offset / 2,
            (CASE WHEN statement_end_offset = - 1 THEN LEN(CONVERT(nvarchar(MAX),
            [text])) * 2 ELSE statement_end_offset END
              - sys.dm_exec_query_stats.statement_start_offset) / 2) AS Expr1
      FROM  sys.dm_exec_sql_text(sys.dm_exec_query_stats.sql_handle) AS dm_exec_sql_text_1) AS query_text,
      total_rows, total_logical_writes, total_logical_reads, total_physical_reads, qp.query_plan
FROM  sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_query_plan(sys.dm_exec_query_stats.plan_handle) qp
WHERE   (execution_count > 20) AND (last_execution_time > GETDATE() - 14)
ORDER BY [Average CPU Time] DESC

Note that this query has a couple of parameters you can play with to achieve different results. As presented above it shows the top 20 queries executed within at least the last two weeks with more than 20 executions since the last SQL server restart. Sometimes it will become evident (from some of the other tools mentioned in a earlier post) that you are really looking for a query that has run more than a hundred thousand times, so change the selection appropriately. Another one of my favorite tricks is after the development team rolls out a large upgrade, wait a couple days and then reduce the date selection to show the last 3 days, change the execution count to "< 100" and catch the newer instances of bad code. Anyway you get the idea. This is a powerful tool for keeping a close eye on what needs to be most seriously improved.