SELECT
TOP
10
total_worker_time/execution_count
AS
avg_cpu_cost, plan_handle,
execution_count,
(
SELECT
SUBSTRING
(text, statement_start_offset/2 + 1,
(
CASE
WHEN
statement_end_offset = -1
THEN
LEN(
CONVERT
(nvarchar(
max
), text)) * 2
ELSE
statement_end_offset
END
- statement_start_offset)/2)
FROM
sys.dm_exec_sql_text(sql_handle))
AS
query_text
FROM
sys.dm_exec_query_stats
ORDER
BY
[avg_cpu_cost]
DESC
--得到最耗时的前N条T-SQL语句
--适用于SQL SERVER 2005及其以上版本
--给N赋初值为30
declare @n int set @n=30
;with maco as
(
select top (@n)
plan_handle,
sum(total_worker_time) as total_worker_time ,
sum(execution_count) as execution_count ,
count(1) as sql_count
from sys.dm_exec_query_stats group by plan_handle
order by sum(total_worker_time) desc
)
select t.text ,
a.total_worker_time ,
a.execution_count ,
a.sql_count
from maco a
cross apply sys.dm_exec_sql_text(plan_handle) t