【SQLServer】查询SQLServer执行过的SQL记录

直接贴代码：

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