Developing Matt

My Technical Journal

Worst performing t-sql statements

with one comment

I found an article in sqlmag by Gregory A. Larsen that assists in finding the stored procedures that wreak the most havoc on your server.  The following stored procedure shows the top worst performers in your databases based upon cpu, execution count, io, and elapsed time. Of course, just because it’s the highest cpu-hogging stored procedure doesn’t mean that it’s not warranted. 

An aside: for sql server 2005 you need sp2 for the utilization of OBJECT_SCHEMA_NAME.

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 DMV 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 Logical Read
      EXEC usp_Worst_TSQL @COUNT=100,@ORDERBY=’ALR’;

   Show top 100 statements by Average IO
      EXEC usp_Worst_TSQL;

(@DBNAME VARCHAR(128) = ‘<not supplied>’
,@COUNT INT = 999999999
— Check for valid @ORDERBY parameter
          @ORDERBY in (‘ACPU’,’TCPU’,’AE’,’TE’,’EC’,’AIO’,’TIO’,’ALR’,’TLR’,’ALW’,’TLW’,’APR’,’TPR’)
             THEN 1 ELSE 0 END) = 0)
   — 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)
                  DB_NAME(CAST(pa.value AS INT))+’*’,
                 ‘Resource’) AS [Database Name] 
         — find the offset of the actual statement being executed
                   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]
         ,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 / execution_count [Avg Elapsed Time]
         ,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) + ‘*’) 
                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

disclaimer: I did attempt to contact to owner of this script ( but was unable to reach anyone (I did reach Greg Larson and he recommended I contact them).  I tried a few times.  I also read through their copyright information and did not find any information indicating that I could not use it for the purposes of this blog.  Please use at your own discretion.


Written by matt

November 23, 2008 at 8:39 am

Posted in T-Sql

One Response

Subscribe to comments with RSS.

  1. Awesome, I didn’t know about this topic up to now. Thanks.


    December 11, 2009 at 3:01 pm

Leave a Reply

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

You are commenting using your 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: