快速分析&应急工具箱

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);