查询数据库表

场景：服务器数据打开缓慢，系统登录读取数据库超时

查询数据库耗时缓慢的语句

SELECT TOP 30
total_worker_time/1000 AS [CPU总耗时(ms)], 
execution_count [运行次数], 
qs.total_worker_time/qs.execution_count/1000 AS [CPU平均耗时(ms)], 
last_execution_time AS [最后执行时间], 
max_worker_time/1000 AS [最大执行耗时(ms)], 
SUBSTRING(qt.text,qs.statement_start_offset/2+1, 
 (CASE WHEN qs.statement_end_offset = -1 
 THEN DATALENGTH(qt.text) 
 ELSE qs.statement_end_offset 
 END -qs.statement_start_offset)/2 + 1) AS [SQL语句]
FROM sys.dm_exec_query_stats qs WITH(nolock)
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count > 1
ORDER BY max_worker_time DESC

 

原因：后面发现是数据库生成文件导致系统IO卡住（快年底有人在执行大范围的数据生成）

 

感谢：https://www.cnblogs.com/OpenCoder/p/18294996