Poor Performance Querys

/*-------------------------------------------------------------------------------------------------------------------------------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 ================================================================================= Note : 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 View Server State permissions are required to execute this query. The result sets is based on total average CPU and IO used per execution. This Script might take 1-5 minutes in execution , depends on data in DMV.