This queries will show you the most expensive queries that were recently executed.

There are four of options to change the output:

  1. @ResultCount – Number of Records to be Retrieved in Int
  2. @Database_Name – Database Name for which the Expense queries needs to be Retrieved in Varchar
  3. @ExecutedSince – Show only queries executed on or after the specified date in DateTime
  4. @Expense_Counter – Criteria on which the Query Expense needs to judged:
    • DurTimeAvgMin: 0
    • CPUTimeAvgMin: 1
    • TotalCPUTime: 2
    • TotalDurTime: 3
    • NoPhysicalReads: 4
    • AvgNoPhysicalReads: 5
    • NoLogicalReads: 6
    • AvgNoLogicalReads: 7

None. This is a selection query and no data is modified in the running of it.

USE master
DECLARE @ResultCount AS INT;
DECLARE @Database_Name AS VARCHAR(255);
DECLARE @ExecutedSince DATETIME;
DECLARE @Expense_Counter INT ;
SET @ResultCount = 20;
SET @Database_Name = 'StoreData';
SET @ExecutedSince = '2013-01-01 00:00 AM';
SET @Expense_Counter = 5;
/*Variables Used 
1. @@ResultCount -- Number of Records to be Retrieved in Int 
2. @Database_Name -- Database Name for which the Expense queries needs to be Retrieved in Varchar 
2. @ExecutedSince -- Show only queries executed on or after the specified date in DateTime
4. @Expense_Counter -- Criteria on Which the Query Expense needs to judged 
Refer Below for Expense Counter 
End Variables Used*/ 
/*Expense Counter 
DurTimeAvgMin 0 
CPUTimeAvgMin 1 
TotalCPUTime 2 
TotalDurTime 3 
NoPhysicalReads 4 
AvgNoPhysicalReads 5 
NoLogicalReads 6 
AvgNoLogicalReads 7 
*/ 
SELECT DatabaseName, QueryText, ProcessText, LastExecutionTime, ExecutionPlan,
       PlanGenerationNumber,ExecutionCount,DurTimeAvgMin, CPUTimeAvgMin,TotalCPUTime,
	   TotalDurTime,NoPhysicalReads,AvgNoPhyscialReads,NoLogicalReads,AvgNoLogicalReads
FROM 
( 
SELECT TOP (@ResultCount) DB_NAME(CONVERT (INT, epa.value)) AS [DatabaseName], 
SUBSTRING(est.text, (eqs.statement_start_offset/2)+1, 
        ((CASE eqs.statement_end_offset 
         WHEN -1 THEN DATALENGTH(est.text) 
         ELSE eqs.statement_end_offset 
         END - eqs.statement_start_offset)/2) + 1) AS QueryText, 
est.text AS ProcessText,         
eqs.plan_generation_num AS PlanGenerationNumber, 
eqs.execution_count AS ExecutionCount, 
(eqs.total_worker_time/1000) AS TotalCPUTime, 
(((eqs.total_worker_time/1000)/eqs.execution_count)/3600) AS CPUTimeAvgMin, 
(eqs.total_elapsed_time/1000) AS TotalDurTime, 
(((eqs.total_elapsed_time/1000)/eqs.execution_count)/3600) AS DurTimeAvgMin, 
eqs.total_physical_reads AS NoPhysicalReads, 
(eqs.total_physical_reads/eqs.execution_count) AS AvgNoPhyscialReads, 
eqs.total_logical_reads AS NoLogicalReads, 
(eqs.total_logical_reads/eqs.execution_count) AS AvgNoLogicalReads, 
eqs.last_execution_time AS LastExecutionTime,
eqp.query_plan ExecutionPlan
FROM SYS.DM_EXEC_QUERY_STATS eqs 
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(sql_handle) est 
CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(plan_handle) eqp 
CROSS APPLY SYS.DM_EXEC_PLAN_ATTRIBUTES(eqs.plan_handle) epa 
WHERE attribute = 'dbid'  
AND DB_NAME(CONVERT (INT, epa.value)) = @Database_Name
AND eqs.last_execution_time >= @ExecutedSince
) x 
--If we want to get queries executed greater than some time 
--and (((qs.total_elapsed_time/1000)/qs.execution_count)/3600) >= 2 
ORDER BY 
--Seems to be Problem with Order By working on the same 
--Order By Fixed 
CASE 
WHEN @Expense_Counter = 0 THEN DurTimeAvgMin 
WHEN @Expense_Counter = 1 THEN CPUTimeAvgMin 
WHEN @Expense_Counter = 2 THEN TotalCPUTime 
WHEN @Expense_Counter = 3 THEN TotalDurTime 
WHEN @Expense_Counter = 4 THEN NoPhysicalReads 
WHEN @Expense_Counter = 5 THEN AvgNoPhyscialReads 
WHEN @Expense_Counter = 6 THEN NoLogicalReads 
WHEN @Expense_Counter = 7 THEN AvgNoLogicalReads 
END DESC 
  • Entered : //2009
  • Version :
You could leave a comment if you were logged in.