Script to Identify worst Performing Queries

Leave a comment (1) Go to comments

Being a DBA, to optimize a performance, a DBA need to know

  • What are most most frequently executed queries on your system.
  • What are the Queries / Statements which which makes system real busy.
  • What are the top worst performed queries
  • How much IO is being caused by a particular query
  • What is the CPU processing time to execute a particular query
  • What is frequency of executing these worst performing queries.

We can find most of these information in DMV sys.dm_exec_query_stats, where we can rate SQL statements by their costs.

These costs can be
- AvgCPUTimeMiS = Average CPU execution time
- AvgLogicalIo  = Average logical operations
or the total values of this measures.

/*-------------------------------------------------------------------------------------------------------------------------------Description    :     This stored procedure will send out alert email if there is a blocking which lasted more than specified duration)
-- Copyright 2011 - DBATAG

-- Author        :    DBATAG
-- Created on    :    09/01/2011
-- Modified on   :    09/01/2011
-- Version       :    1.0
-- Dependencies  :
--                 Table                            Procedure                Permissions
--                 No Dependencies                No Dependencies            View Server State Permissions Required
                ----------------------------------------------------------------------------------------------------------------------------*/
-- List expensive queries 
DECLARE @MinExecutions int; 
SET @MinExecutions = 5 
 
SELECT EQS.total_worker_time AS TotalWorkerTime 
      ,EQS.total_logical_reads + EQS.total_logical_writes AS TotalLogicalIO 
      ,EQS.execution_count As ExeCnt 
      ,EQS.last_execution_time AS LastUsage 
      ,EQS.total_worker_time / EQS.execution_count as AvgCPUTimeMiS 
      ,(EQS.total_logical_reads + EQS.total_logical_writes) / EQS.execution_count  
       AS AvgLogicalIO 
      ,DB.name AS DatabaseName 
      ,SUBSTRING(EST.text 
                ,1 + EQS.statement_start_offset / 2 
                ,(CASE WHEN EQS.statement_end_offset = -1  
                       THEN LEN(convert(nvarchar(max), EST.text)) * 2  
                       ELSE EQS.statement_end_offset END  
                 - EQS.statement_start_offset) / 2 
                ) AS SqlStatement 
      -- Optional with Query plan; remove comment to show, but then the query takes !!much longer time!! 
      --,EQP.[query_plan] AS [QueryPlan] 
FROM sys.dm_exec_query_stats AS EQS 
     CROSS APPLY sys.dm_exec_sql_text(EQS.sql_handle) AS EST 
     CROSS APPLY sys.dm_exec_query_plan(EQS.plan_handle) AS EQP 
     LEFT JOIN sys.databases AS DB 
         ON EST.dbid = DB.database_id      
WHERE EQS.execution_count > @MinExecutions 
      AND EQS.last_execution_time > DATEDIFF(MONTH, -1, GETDATE()) 
ORDER BY AvgLogicalIo DESC 
        ,AvgCPUTimeMiS DESC
        

OUTPUT Screenshot


Script to Identify worst Performing Queries

Note :

  1. The above mentioned query will list down queries which at least have been executed once in the past month or the last SQL Server restart, which ever is earlier
  2. View Server State permissions are required to execute this query.
  3. The result sets is based on total average CPU and IO used per execution.
  4. This Script might take 1-5 minutes in execution , depends on data in DMV.
EOF - Script to Identify worst Performing Queries, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

1 Comments.

  1. Hello,
    Can you please explain, the computation ( formula) to determine the start and end of the substring, to get the SQl Statement.
    Thanks,
    Dudu

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.