用 system_health 来查看死锁与解决死锁

system_health 扩展事件是 SQL Server 自带的系统扩展事件， 从 SQL Server 2012 起就有了， 而且是默认运行的。

这点对于我们查看死锁就非常方便了。

执行：

select substring(path,0,charindex('_',path)-3) as directory from sys.traces

可以定位到对应的目录下， 进入查看以 "system_health" 开头的 XEL 文件

将最新的文件拖放到 SSMS 的查询窗口， 然后放手， 

双击 “值” 对应的单元格， 即可看到死锁的详细信息。下图中可以清楚看到：

哪两个存储过程的两个 update 语句产生了死锁。

筛选出所有的死锁：

将 name = 的值改为 xml_deadlock_report

至于如何减少死锁， 可以从如下几个方面来考虑：

1. 减少不必要的索引;

2. 减少 update 频率;

3. 存储过程或语句按顺序执行;

4. 如果 where 中的条件用索引可以改善则加索引;

5. 避免大事务;

6. 采用低的事务隔离级别;

7. 对于同一表产生的死锁， 可以用 select * from t  with(uplock, readpast) where xxx=? 来解决。

参考：

SQL Server中解决死锁的新方法介绍

SqlServer中的更新锁(UPDLOCK)

SQL SERVER中UPDLOCK ,READPAST使用

msdn表提示( with 加锁)

————————————————————————————————————————————————————————————

下面是参考 msdn: https://technet.microsoft.com/zh-cn/library/ff877955(v=sql.120).aspx

system_health
会话是 SQL Server 默认包含的扩展事件会话。 该会话在 SQL Server 数据库引擎启动时自动启动，并且运行时不会对性能造成任何明显影响。 该会话收集的系统数据可用于帮助对数据库引擎的性能问题进行故障排除。 因此，我们建议您不要停止或删除该会话。

该会话收集的信息包括：

• 发生严重性 >=20 的错误的任何会话的 sql_text 和 session_id。

• 发生与内存有关的错误的任何会话的 sql_text 和 session_id。 这些错误包括 17803、701、802、8645、8651、8657 和 8902。

• 任何无法完成的计划程序问题的记录。 （这些问题在 SQL Server 错误日志中显示为错误 17883。）

• 检测到的任何死锁。

• 等待闩锁（或其他相关资源）的时间 > 15 秒的任何会话的 callstack、sql_text 和 session_id 。

• 等待锁的时间 > 30 秒的任何会话的 callstack、sql_text 和 session_id 。

• 已长时间等待以获得抢先等待的任何会话的 callstack、sql_text 和 session_id。 持续时间因等待类型而异。 在抢先等待中，SQL Server 等待的是外部 API 调用。

• CLR 分配失败和虚拟分配失败的调用堆栈和 session_id。

• 有关内存 Broker、计划程序监视、内存节点 OOM、安全性和连接的 ring_buffer 事件。

• sp_server_diagnostics 中的系统组件结果。

• scheduler_monitor_system_health_ring_buffer_recorded 收集的实例运行状况。

• CLR 分配失败。

• 使用 connectivity_ring_buffer_recorded 时的连接错误。

• 使用 security_error_ring_buffer_recorded 时的安全错误。

查看会话数据

会话使用环形缓冲区目标存储数据。 若要查看会话数据，请使用下面的查询：

SELECT CAST(xet.target_data as xml) FROM sys.dm_xe_session_targets xet  
JOIN sys.dm_xe_sessions xe  
ON (xe.address = xet.event_session_address)  
WHERE xe.name = 'system_health'  

若要查看事件文件中的会话数据，请使用 Management Studio 中提供的扩展事件用户界面。 有关详细信息，请参阅查看事件会话数据。

还原 system_health 会话

如果删除 system_health 会话，则可以通过在查询编辑器中执行 u_tables.sql 文件来还原该会话。 该文件位于下面的文件夹中，其中 C: 表示您安装 SQL Server 程序文件的驱动器：

C:\Program Files\Microsoft SQL Server\MSSQL12.<instanceid>\MSSQL\Install

请注意，在还原该会话后，必须使用 ALTER EVENT SESSION 语句或使用对象资源管理器中的“扩展事件”节点启动会话。 否则，该会话会在您下次重新启动 SQL Server 服务时自动启动。