SELECT CAST( (select SUBSTRING(text,eq1.statement_start_offset/2 +1 , ((CASE eq1.statement_end_offset WHEN -1 THEN DATALENGTH(text) ELSE eq1.statement_end_offset END -eq1.statement_start_offset)/2) + 1) AS Query from sys.dm_exec_sql_text (eq1.sql_handle)) AS nvarchar(max)) AS [Query_StatementText], CAST( (select text AS Query from sys.dm_exec_sql_text (eq1.sql_handle)) AS nvarchar(max)) AS [Query_BatchText], CAST(eq1.sql_handle AS varbinary(max)) AS [Query_Handle], CAST(eq1.plan_handle AS varbinary(max)) AS [Query_PlanHandle], eq1.statement_start_offset AS [Query_StatementStartOffset], eq1.statement_end_offset AS [Query_StatementEndOffset], eq1.execution_count AS [Query_ExecutionCount], eq1.creation_time AS [Query_CreateDate], eq1.last_execution_time AS [Query_LastExecutionTime], eq1.plan_generation_num AS [Query_PlanGenerationNumber], CAST(eq1.query_hash AS varbinary(max)) AS [Query_Hash], eq1.total_worker_time AS [Query_TotalWorkerTime], eq1.last_worker_time AS [Query_LastWorkerTime], eq1.min_worker_time AS [Query_MinWorkerTime], eq1.max_worker_time AS [Query_MaxWorkerTime],
case when ( (DATEDIFF(s,eq1.creation_time, GETDATE()) ) != 0) then (eq1.total_worker_time) / (DATEDIFF(s,eq1.creation_time, GETDATE()) ) else 0 end AS [Query_BurnRateWorkerTime], eq1.total_elapsed_time AS [Query_TotalElapsedTime], eq1.last_elapsed_time AS [Query_LastElapsedTime], eq1.min_elapsed_time AS [Query_MinElapsedTime], eq1.max_elapsed_time AS [Query_MaxElapsedTime],
case when ( (DATEDIFF(s,eq1.creation_time, GETDATE()) ) != 0) then (eq1.total_elapsed_time) / (DATEDIFF(s,eq1.creation_time, GETDATE()) ) else 0 end AS [Query_BurnRateElapsedTime], eq1.total_physical_reads AS [Query_TotalPhysicalReads], eq1.max_physical_reads AS [Query_LastPhysicalReads], eq1.last_physical_reads AS [Query_MinPhysicalReads], eq1.min_physical_reads AS [Query_MaxPhysicalReads],
case when ( (DATEDIFF(s,eq1.creation_time, GETDATE()) ) != 0) then (eq1.total_physical_reads) / (DATEDIFF(s,eq1.creation_time, GETDATE()) ) else 0 end AS [Query_BurnRatePhysicalReads], eq1.total_logical_reads AS [Query_TotalLogicalReads], eq1.last_logical_reads AS [Query_LastLogicalReads], eq1.min_logical_reads AS [Query_MinLogicalReads], eq1.max_logical_reads AS [Query_MaxLogicalReads],
case when ( (DATEDIFF(s,eq1.creation_time,GETDATE()) ) != 0) then (eq1.total_logical_reads) / (DATEDIFF(s,eq1.creation_time, GETDATE()) ) else 0 end AS [Query_BurnRateLogicalReads], eq1.total_logical_writes AS [Query_TotalLogicalWrites], eq1.last_logical_writes AS [Query_LastLogicalWrites], eq1.min_logical_writes AS [Query_MinLogicalWrites], eq1.max_logical_writes AS [Query_MaxLogicalWrites],
case when ( (DATEDIFF(s,eq1.creation_time,GETDATE()) ) != 0) then (eq1.total_logical_writes) / (DATEDIFF(s,eq1.creation_time, GETDATE()) ) else 0 end AS [Query_BurnRateLogicalWrites], CAST(eq1.plan_handle AS varbinary(max)) AS [Query_$Identity] FROM sys.dm_exec_query_stats AS eq1 ORDER BY [Query_BurnRatePhysicalReads] DESC, [Query_$Identity] ASC, [Query_StatementStartOffset] ASC, [Query_StatementEndOffset] ASC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY
|