【工作笔记0032】sqlserver慢sql查询

慢sql查询：

SELECT TOP 100 * FROM (
	SELECT
		(total_elapsed_time / execution_count)/1000 平均时间ms,
		total_elapsed_time/1000 总花费时间ms,
		total_worker_time/1000 所用的CPU总时间ms,
		total_physical_reads 物理读取总次数,
		total_logical_reads/execution_count 每次逻辑读次数,
		total_logical_reads 逻辑读取总次数,
		total_logical_writes 逻辑写入总次数,
		execution_count 执行次数,
		SUBSTRING(dest.text, (deqs.statement_start_offset/2) + 1,
				((
						CASE
								statement_end_offset 
								WHEN - 1 THEN
								DATALENGTH( dest.text ) ELSE deqs.statement_end_offset 
							END - deqs.statement_start_offset 
				) / 2) + 1 
		)  执行语句,
		creation_time 语句编译时间,
		last_execution_time 上次执行时间
	FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) dest
) tt
WHERE 执行语句 not like '%fetch%'
AND 上次执行时间 > '2021-4-7 18:19:03' AND 上次执行时间 < '2021-4-8 18:19:14'
ORDER BY 平均时间ms DESC;

指定时间内的sql执行情况查询：

select * from (
	SELECT TOP 100 		
		deqs.creation_time, --创建时间 		
		SUBSTRING(dest.text,(deqs.statement_start_offset/2)+1, 
		((CASE deqs.statement_end_offset WHEN -1 THEN DATALENGTH(dest.text) 
		ELSE deqs.statement_end_offset END - deqs.statement_start_offset)/2) + 1 
		) AS statement_text, --sql语句 		
		dest.text, 
		--执行时间
		deqs.total_worker_time, 
		deqs.last_worker_time, 
		deqs.max_worker_time, 
		deqs.min_worker_time 
	FROM sys.dm_exec_query_stats deqs 	
	CROSS APPLY 
		sys.dm_exec_sql_text(deqs.sql_handle) dest 
	WHERE 
		deqs.creation_time BETWEEN '2021-4-8 16:56:36' AND '2021-4-8 17:56:48'  
		--AND dest.text LIKE '%%' 
	ORDER BY 
		deqs.creation_time DESC
) t