很多时候,我们在用SQL语句查询数据时,难免会漏掉对SQL语句性能的考虑,所以有时就会造成SqlServer服务占用过高的问题,为了大致排查是哪些SQL语句造成的问题,我们可以通过如下SQL查询出最近所有耗时最大的SQL语句,具体查询SQL语句如下所示:
SELECT s2.dbid,
s1.sql_handle,
(
SELECT TOP 1
SUBSTRING( s2.text,
statement_start_offset / 2 + 1,
((CASE
WHEN statement_end_offset = -1 THEN
(LEN(CONVERT(NVARCHAR(MAX), s2.text)) * 2)
ELSE
statement_end_offset
END
) - statement_start_offset
) / 2 + 1
)
) AS 执行SQL,
last_worker_time '最后执行总耗时(毫秒)',
last_execution_time '最后执行时间',
total_worker_time '所有执行总耗时(毫秒)',
min_worker_time '执行最小耗时(毫秒)',
max_worker_time '执行最大耗时(毫秒)',
execution_count,
plan_generation_num,
total_physical_reads,
last_physical_reads,
min_physical_reads,
max_physical_reads,
total_logical_writes,
last_logical_writes,
min_logical_writes,
max_logical_writes
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
WHERE s2.objectid IS NULL
ORDER BY last_worker_time DESC,
s1.sql_handle,
s1.statement_start_offset,
s1.statement_end_offset;
执行效果:
标签:语句,last,Sql,offset,SqlServer,time,statement,SQL From: https://www.cnblogs.com/qubernet/p/17994410