SELECT top 10
QS.creation_time '编译计划的时间'
, QS.last_execution_time '上次执行计划的时间'
, QS.execution_count '执行的次数'
, QS.total_elapsed_time / 1000 '占用的总时间（秒）'
, QS.total_physical_reads '物理读取总次数'
, QS.total_logical_reads/execution_count N'每次逻辑读取次数'
, QS.total_worker_time / 1000 'CPU 时间总量（秒）'
, QS.total_logical_writes '逻辑写入总次数'
, QS.total_logical_reads N'逻辑读取总次数'
, QS.total_elapsed_time / 1000 N'总花费时间（秒）'
, 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 '执行语句'
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 QS.last_execution_time>'2017-09-20'
ORDER BY QS.total_logical_reads/execution_count DESC
--Blocked SQL:
;with cte as (select replace(hostname,' ','') as hostname ,''''+replace(program_name,' ','')+'''' as program_name
, loginame, db_name(a.dbid) AS DBname,spid,blocked,waittime/1000 as waittime,a.status,a.lastwaittype,a.cmd
,Replace(substring(b.text,1,340),'''','''') as sqlmessage,cpu
from sys.sysprocesses as a with(nolock)
cross apply sys.dm_exec_sql_text(sql_handle) as b
where a.blocked>0 and sql_handle<>0x0000000000000000000000000000000000000000
and waittime>2000 )
select replace(hostname,' ','') as hostname ,''''+replace(program_name,' ','')+'''' as program_name
, loginame, db_name(a.dbid) AS DBname,spid,blocked,waittime/1000 as waittime,a.status,a.lastwaittype,a.cmd
,Replace(substring(b.text,1,340),'''','''') as sqlmessage,cpu
from sys.sysprocesses as a with(nolock)
cross apply sys.dm_exec_sql_text(sql_handle) as b
where exists(select blocked from cte where cte.blocked=a.spid)
and not exists (select spid from cte where cte.spid=a.spid)
union all
select * from cte
