Differences

This shows you the differences between two versions of the page.

Link to this comparison view

control_sql_-_sql_server_expensive_queries [2019/01/27 11:29]
 control_sql_-_sql_server_expensive_queries [2019/01/27 11:29] (current)
Line 1: Line 1:
 +======  ======
 +
 +
 +
 +===== Explanation of SQL =====
 +
 +
 +
 +This queries will show you the most expensive queries that were recently executed.
 +
 +
 +
 +There are four of options to change the output:
 +  - @ResultCount -- Number of Records to be Retrieved in Int 
 +  - @Database_Name -- Database Name for which the Expense queries needs to be Retrieved in Varchar 
 +  - @ExecutedSince -- Show only queries executed on or after the specified date in DateTime
 +  - @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 
 +
 +
 +
 +===== Risk of Data Corruption if Run Improperly =====
 +
 +**None**.  This is a selection query and no data is modified in the running of it.
 +
 +
 +
 +===== SQL =====
 +
 +
 +
 +<code sql>
 +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 
 +</code>
 +
 +
 +
 +===== Version Information =====
 +  * Entered : __/__/2009
 +  * Version :
 +
 +
 +