|
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
|
