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
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
Version Information
- Entered : //2009
- Version :
You could leave a comment if you were logged in.