Home > Uncategorized > Find Worst Offending SQL from Cache

Find Worst Offending SQL from Cache

I was performing an performance tuning analysis on a SQL Server for a client with very minimal permissions but high expectations (undesirable I know) so I was looking for a way to target what needed to be looked at for tuning opportunities and I decided to go look into the procedure cache to get the previously executed SQL statements in a way I could determine what the worst offenders where.  I wanted to share the script I came up with to do this

--Get Worst Offenders
select top 1000
      DB_NAME(txt.dbid) as DBName,
      cp.cacheobjtype, 
      cp.objtype,
      OBJECT_NAME(txt.objectid, txt.dbid) as SPName,
      SUBSTRING (txt.text,(r.statement_start_offset / 2) + 1,((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(txt.text) ELSE r.statement_end_offset END - r.statement_start_offset) / 2) + 1) as QueryText,
      r.total_worker_time / r.execution_count as AverageWorkerTime,
      r.total_elapsed_time / r.execution_count as AverageDuration,
      r.total_logical_reads / r.execution_count as AverageLogicalReads,
      r.total_physical_reads / r.execution_count as AveragePhysicalReads,
      r.total_logical_writes / r.execution_count as AverageLogicalWrites,
      r.execution_count as ExecutionCount,
      r.total_worker_time as TotalWorkerTime,
      r.total_elapsed_time as TotalDuration,
      r.total_logical_reads as TotalLogicalReads,
      r.total_physical_reads as TotalPhysicalReads,
      r.total_logical_writes as TotalLogicalWrites,
      r.sql_handle as sql_handle,
      r.plan_handle as plan_handle
from sys.dm_exec_query_stats r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) as txt
INNER JOIN sys.dm_exec_cached_plans cp ON cp.plan_handle = r.plan_handle

--Only if it is a compiled object or it will be NULL
--WHERE DB_NAME(txt.dbid) = '<DatabaseName>' --Only if it is a compiled object (Proc or Trigger) or it will be NULL
--WHERE objtype = 'Prepared' --Use to find cetain Object Types (AdHoc, Prepared, Proc, etc)


--ORDER BY AverageDuration desc
Advertisements
Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: