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] (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 :
 +
 +
 +