直接贴代码:
SELECT TOP 1000
--创建时间
QS.creation_time,
--查询语句
SUBSTRING(ST.text,(QS.statement_start_offset/2)+1,
((CASE QS.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
ELSE QS.statement_end_offset END - QS.statement_start_offset)/2) + 1
) AS statement_text,
--执行文本
ST.text
FROM
sys.dm_exec_query_stats QS
--关键字
CROSS APPLY
sys.dm_exec_sql_text(QS.sql_handle) ST
where
ST.text LIKE '%select%'
ORDER BY
QS.creation_time DESC
下面是对各属性进行别名优化后的SQL:
SELECT TOP
1000 QS.creation_time AS '执行时间',
SUBSTRING (
ST.text,
( QS.statement_start_offset/ 2 ) + 1,
(
( CASE QS.statement_end_offset WHEN - 1 THEN DATALENGTH( st.text ) ELSE QS.statement_end_offset END - QS.statement_start_offset ) / 2
) + 1
) AS '执行的SQL语句',
QS.execution_count AS '执行次数',
ST.text AS 'SQL语句',
QS.total_elapsed_time AS '耗时',
QS.total_logical_reads AS '逻辑读取次数',
QS.total_logical_writes AS '逻辑写入次数',
QS.total_physical_reads AS '物理读取次数'
FROM
sys.dm_exec_query_stats QS CROSS APPLY sys.dm_exec_sql_text ( QS.sql_handle ) ST
WHERE
ST.text LIKE '%select%'
ORDER BY
QS.creation_time DESC