最近遇到一个sqlserver项目，月底会出现多个财务相关功能出现不定期操作无响应问题
- 通过查询SQL阻塞信息，定位到阻塞源头spid、该会话的状态、等待事件及执行的SQL脚本
- 根据spid查询该会话已经持有的锁资源、数据对象
- 根据SQL脚本如果能够找到对应的程序上下文，便可以快速定位到问题源头
- 根据源头会话确定远程连接的HostName、HostProcess，抓取进程dump文件，分析线程堆栈匹配该SQL脚本
- 使用sqlserver的Profiler，开启SQL的RPC、Batch及transaction的跟踪，结合阻塞SQL时间点，分析跟踪文件
查询阻塞源头，根据hostname、hostprocess，远程该应用服务器，打开任务管理器选中该进程，创建转储文件、dump。
-- 查询并确认阻塞源头
select t.spid, t.kpid, t.blocked, t.status, t.waittype, t.lastwaittype, t.waitresource
, t.hostname, t.hostprocess, t.loginame, t.program_name, t.waittime
, DB_NAME(t.dbid) DbName, t.login_time, t.last_batch
, t.cmd, t.stmt_start, t.stmt_end, t.request_id, dc.text
from master.sys.sysprocesses t
outer apply master.sys.dm_exec_sql_text(t.sql_handle) dc
where t.blocked = 0
and t.spid in (select blocked from master.sys.sysprocesses where blocked != 0)
再次到数据库上查询并导出所有session的状态、SQL、等待事件及持有的锁资源，以备后期分析。
-- 查看所有会话的状态、等待类型及当前正在执行SQL脚本
select t.spid, t.kpid, t.blocked, t.status, t.waittype, t.lastwaittype, t.waitresource, t.waittime
, DB_NAME(t.dbid) DbName, t.login_time, t.last_batch, t.loginame, t.program_name, t.hostname, t.hostprocess
, t.cmd, t.stmt_start, t.stmt_end, t.request_id, dc.text
from master.sys.sysprocesses t outer apply master.sys.dm_exec_sql_text(t.sql_handle) dc
where t.spid >= 50 -- 查看所有会话当前持有和申请的锁资源
select l.request_session_id,
l.resource_type,
l.resource_subtype,
l.request_status,
l.request_mode,
l.resource_description,
case l.resource_type
when 'database' then DB_NAME(l.resource_database_id)
when 'object' then object_name(l.resource_associated_entity_id)
else OBJECT_NAME(p.object_id)
end as obj_name,
p.index_id
from sys.dm_tran_locks l
left join sys.partitions p on l.resource_associated_entity_id = p.hobt_id
order by l.request_session_id, l.resource_type
另外，建议同时开启sqlserver的trace跟踪。问题重现后确认发生的时间点，反馈前后30分钟内的trc文件。
注意：
- 先选中显示所有列，然后重新选中所有需要的跟踪项目，确保复选框是“黑色”全选状态，而不是灰色的半选；
- SQL trace会产生大量的数据，请定时监控存储文件所在磁盘的可用空间，可根据实际情况酌情删除几个小时之前产生的trace文件。