Finding the Worst Performing TSQL Statement

Have you ever wondered how to find the worst performing TSQL queries on your instance?  If you have, you are not alone. 

SQL Server maintains query statistics as TSQL commands are run.  These query statistics can be returned by using the Dynamic Management Objects provided in SQL Server.

The Dynamic Management Objects sys_dm.exec_query_stats returns a view of the query statistics that SQL Server is currently maintaining.  The statistics that this view returned are stored in memory with the cached plan for each query.  As long a TSQL execution plan is stored in the plan cache you can return statistics for the TSQL command.   Once the command is removed from the procedure cache, then the statistics are also dropped.

Below is the code for a stored procedure that will return the TSQL query statistics that are stored in the procedure cache.  This procedure can be passed a number of different parameters.  Those parameters are @DBNAME, @COUNT, and @ORDERBY.  These parameters are used to determine which statistics are returned, or how the returned data will be sorted.   

By using this stored procedure you will be able to return the worst performing TSQL statements.

--CREATE PROC [dbo].[usp_Worst_TSQL] 
/*
Written by: Gregory A. Larsen
Copyright © 2008 Gregory A. Larsen.  All rights reserved.
 
Name: usp_Worst_TSQL
Description: This stored procedure displays the top worst performing queries based on CPU, Execution Count, 
             I/O and Elapsed_Time as identified using DMO information.  This can be display the worst 
             performing queries from an instance, or database perspective.   The number of records shown,
             the database, and the sort order are identified by passing pararmeters.
 
Parameters:  There are three different parameters that can be passed to this procedures: @DBNAME, @COUNT
             and @ORDERBY.  The @DBNAME is used to constraint the output to a specific database.  If  
             when calling this SP this parameter is set to a specific database name then only statements 
             that are associated with that database will be displayed.  If the @DBNAME parameter is not set
             then this SP will return rows associated with any database.  The @COUNT parameter allows you 
             to control the number of rows returned by this SP.  If this parameter is used then only the 
             TOP x rows, where x is equal to @COUNT will be returned, based on the @ORDERBY parameter.
             The @ORDERBY parameter identifies the sort order of the rows returned in descending order.  
             This @ORDERBY parameters supports the following type: CPU, AE, TE, EC or AIO, TIO, ALR, TLR, ALW, TLW, APR, and TPR 
             where "ACPU" represents Average CPU Usage
                   "TCPU" represents Total CPU usage 
                   "AE"   represents Average Elapsed Time
                   "TE"   represents Total Elapsed Time
                   "EC"   represents Execution Count
                   "AIO"  represents Average IOs
                   "TIO"  represents Total IOs 
                   "ALR"  represents Average Logical Reads
                   "TLR"  represents Total Logical Reads              
                   "ALW"  represents Average Logical Writes
                   "TLW"  represents Total Logical Writes
                   "APR"  represents Average Physical Reads
                   "TPR"  represents Total Physical Read
 
Typical execution calls
   
   Top 6 statements in the AdventureWorks database base on Average CPU Usage:
      EXEC usp_Worst_TSQL @DBNAME='AdventureWorks',@COUNT=6,@ORDERBY='ACPU';
   
   Top 100 statements order by Average IO 
      EXEC usp_Worst_TSQL @COUNT=100,@ORDERBY= 'AE'; 
 
   Show top all statements by Average IO 
      EXEC usp_Worst_TSQL;
 
*/
 
DECLARE 
  @DBNAME VARCHAR(128) = '<not supplied>'
,@COUNT INT = 999999999
,@ORDERBY VARCHAR(4) = 'TCPU' 
 
-- Check for valid @ORDERBY parameter
IF ((SELECT CASE WHEN 
          @ORDERBY in ('ACPU','TCPU','AE','TE','EC','AIO','TIO','ALR','TLR','ALW','TLW','APR','TPR') 
             THEN 1 ELSE 0 END) = 0)
BEGIN 
   -- abort if invalid @ORDERBY parameter entered
   RAISERROR('@ORDERBY parameter not APCU, TCPU, AE, TE, EC, AIO, TIO, ALR, TLR, ALW, TLW, APR or TPR',11,1)
   RETURN
END
SELECT TOP (@COUNT) 
         COALESCE(DB_NAME(st.dbid), 
                  DB_NAME(CAST(pa.value AS INT))+'*', 
                 'Resource') AS [Database Name]  
         -- find the offset of the actual statement being executed
         ,SUBSTRING(text, 
                   CASE WHEN statement_start_offset = 0 
                          OR statement_start_offset IS NULL  
                           THEN 1  
                           ELSE statement_start_offset/2 + 1 END, 
                   CASE WHEN statement_end_offset = 0 
                          OR statement_end_offset = -1  
                          OR statement_end_offset IS NULL  
                           THEN LEN(text)  
                           ELSE statement_end_offset/2 END - 
                     CASE WHEN statement_start_offset = 0 
                            OR statement_start_offset IS NULL 
                             THEN 1  
                             ELSE statement_start_offset/2  END + 1 
                  )  AS [Statement]  
         ,OBJECT_SCHEMA_NAME(st.objectid,dbid) [Schema Name] 
         ,OBJECT_NAME(st.objectid,dbid) [Object Name]   
         ,objtype [Cached Plan objtype] 
               ,total_elapsed_time / execution_count [Avg Elapsed Time]
         ,execution_count [Execution Count]  
         ,(total_logical_reads + total_logical_writes + total_physical_reads )/execution_count [Average IOs] 
         ,total_logical_reads + total_logical_writes + total_physical_reads [Total IOs]  
         ,total_logical_reads/execution_count [Avg Logical Reads] 
         ,total_logical_reads [Total Logical Reads]  
         ,total_logical_writes/execution_count [Avg Logical Writes]  
         ,total_logical_writes [Total Logical Writes]  
         ,total_physical_reads/execution_count [Avg Physical Reads] 
         ,total_physical_reads [Total Physical Reads]   
         ,total_worker_time / execution_count [Avg CPU] 
         ,total_worker_time [Total CPU]  
         ,total_elapsed_time  [Total Elasped Time] 
         ,last_execution_time [Last Execution Time]  
    FROM sys.dm_exec_query_stats qs  
    JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle 
    CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st 
    OUTER APPLY sys.dm_exec_plan_attributes(qs.plan_handle) pa 
    WHERE attribute = 'dbid' AND   
     CASE when @DBNAME = '<not supplied>' THEN '<not supplied>'
                               ELSE COALESCE(DB_NAME(st.dbid), 
                                          DB_NAME(CAST(pa.value AS INT)) + '*', 
                                          'Resource') END
                                    IN (RTRIM(@DBNAME),RTRIM(@DBNAME) + '*')  
      
    ORDER BY CASE 
                WHEN @ORDERBY = 'ACPU' THEN total_worker_time / execution_count 
                WHEN @ORDERBY = 'TCPU'  THEN total_worker_time
                WHEN @ORDERBY = 'AE'   THEN total_elapsed_time / execution_count
                WHEN @ORDERBY = 'TE'   THEN total_elapsed_time  
                WHEN @ORDERBY = 'EC'   THEN execution_count
                WHEN @ORDERBY = 'AIO'  THEN (total_logical_reads + total_logical_writes + total_physical_reads) / execution_count  
                WHEN @ORDERBY = 'TIO'  THEN total_logical_reads + total_logical_writes + total_physical_reads
                WHEN @ORDERBY = 'ALR'  THEN total_logical_reads  / execution_count
                WHEN @ORDERBY = 'TLR'  THEN total_logical_reads 
                WHEN @ORDERBY = 'ALW'  THEN total_logical_writes / execution_count
                WHEN @ORDERBY = 'TLW'  THEN total_logical_writes  
                WHEN @ORDERBY = 'APR'  THEN total_physical_reads / execution_count 
                WHEN @ORDERBY = 'TPR'  THEN total_physical_reads
           END DESC
 

See all articles by Greg Larsen