查看快速分析&应急工具箱的源代码

==5分钟==
首先排查永健：内存、存储和CPU使用率.
[PerfMon]
Memory/Available MBytes
SQL Server:Memory Manager/Target Server Memory(KB)
SQL Server:Memory Manager/Total Server Memory(KB)
Buffer Manager:Page Life Expectancy

LogicalDisk:Avg.Disk sec/Transfer
sys.dm_io_virtual_file_stats

Processor:% Processor Time
Processor:% Privileged Time
Processor:% User Time
Process:% Processor Time:sqlserver
使用PAL做长时间数据收集、分析。

[检查SQL Server等待]
1.sys.dm_os_waiting_tasks
SELECT * 
FROM sys.dm_os_waiting_tasks
WHERE session_id > 50;

SELECT wt.*,
  st.text,
  qp.query_plan
FROM sys.dm_os_waiting_tasks wt
 LEFT JOIN sys.dm_exec_requests er
   ON wt.waiting_task_address = er.task_address
 CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st
 CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) qp
WHERE wt.wait_type = 'CXPACKET' ORDER BY wt.session_id;

2.sys.dm_os_wait_stats 自SQL服务启动以来所有查询的等待时间
--重置计数器
DBCC SQLPERF ('sys.dm_os_wait_stats', clear);