SQL Server DMV查询SELECT所消耗的时间


SELECT TOP 20 
CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28, 2))
AS [Total Elapsed Duration (s)],
qs.execution_count,
SUBSTRING(qt.text, (qs.statement_start_offset/2) + 1, ((CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1) AS [Individua Query],
qt.text AS [Parent Query],
DB_NAME(qt.dbid) AS DatabaseName,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

ORDER BY total_elapsed_time DESC

执行结果:
< DMV还可以做很多事情,作为分析SQL Server的好助手~ 参考:


发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

验证码 * Time limit is exhausted. Please reload CAPTCHA.