通过DMV查找CPU时间最长的语句和查询计划

SELECT TOP 50
execution_count, total_worker_time [CPU time],
total_logical_reads / execution_count AS [Avg Logical Reads],
total_elapsed_time / execution_count AS [Avg Elapsed Time],
total_worker_time / execution_count as [Avg CPU time],
db_name(st.dbid) as [database name],
object_name(st.dbid) as [object name],
object_name(st.objectid) as [father object name],
SUBSTRING
(
st.text, (qs.statement_start_offset / 2) + 1,
(
(
CASE statement_end_offset
WHEN - 1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END
- qs.statement_start_offset
) / 2
) + 1
)
AS statement_text,
qp.query_plan, qs.creation_time [PlanCompileTime]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
WHERE total_worker_time > 300
and db_name(st.dbid) is not null and db_name(st.dbid) not in ('distribution')
ORDER BY [CPU time] DESC, [Avg CPU time] DESC[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/66009/viewspace-1040590/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/66009/viewspace-1040590/