SELECT 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 ‘执行语句’ ,
DB_Name(DB.dbid) ‘数据库名’
, OBJECT_ID(db.objectid) ‘对象’
, QS.creation_time ‘编译计划的时间’
, QS.last_execution_time ‘上次执行计划的时间’
, QS.execution_count ‘执行的次数’
, QS.total_elapsed_time / 1000 ‘占用的总时间（秒）’
, QS.total_physical_reads ‘物理读取总次数’
, QS.total_worker_time / 1000 ‘CPU 时间总量（秒）’
, QS.total_logical_writes ‘逻辑写入总次数’
, QS.total_logical_reads N’逻辑读取总次数’
, QS.total_elapsed_time / 1000 N’总花费时间（秒）’
FROM sys.dm_exec_query_stats AS QS CROSS APPLY
sys.dm_exec_sql_text(QS.sql_handle) AS ST INNER JOIN
( SELECT *
FROM sys.dm_exec_cached_plans cp CROSS APPLY
sys.dm_exec_query_plan(cp.plan_handle)
) DB
ON QS.plan_handle = DB.plan_handle
where 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) not like ‘%fetch%’
and isnull(DB_Name(DB.dbid),”) != ‘AME1.0’
ORDER BY QS.total_elapsed_time / 1000 DESC
–by 别人
