SQL基础：sql server排查死锁优化性能

1. 死锁排查

在数据库并发操作中，当两个或多个事务互相等待对方释放资源时，就会发生死锁。在 SQL Server 中，可以通过以下步骤进行死锁排查：

1.1 查看死锁日志

可以通过查看 SQL Server 错误日志或系统事件日志来查看死锁信息。其中错误日志提供了详细的死锁信息，包括死锁事务 IDs、死锁链和死锁链上的受害者等信息。

1.2 使用系统存储过程查看死锁信息

在 SQL Server 中，可以使用系统存储过程 sp_who2、sp_lock、sp_who等来查看死锁信息。

1.3 使用动态管理视图(DMV)查看死锁信息

在 SQL Server 2005 以后版本，提供了一些动态管理视图(DMV)来查看死锁信息。这些 DMV 包括 sys.dm_tran_locks、sys.dm_tran_session_transactions、sys.dm_exec_connections 等，可以用来查看当前正在运行的事务和锁定信息。

2. 死锁优化

在排查死锁之后，必须采取措施来避免死锁的发生。以下是一些常见的死锁优化措施：

2.1 减少事务并发

在数据库设计时，应当尽可能地减少并发操作的数量，以降低死锁发生的概率。 例如，将多个数据库操作合并为一个事务，可以减少死锁的发生。

2.2 在应用程序中处理死锁

在应用程序中，可以处理死锁错误，例如，当发生死锁时，可以重试当前事务，或者等待一段时间后再次尝试。

2.3 设置事务隔离级别

在 SQL Server 中，可以设置事务隔离级别，以控制事务并发执行的程度。例如，使用 READ COMMITTED 事务隔离级别可以避免脏读，同时减少死锁的发生。

2.4 优化数据库查询

优化数据库查询也可以降低死锁的发生概率。例如，可以使用索引、避免长事务、使用批量处理等技术来提高数据查询效率，减少对数据库资源的占用。

-- 查看死锁链
SELECT 
    dec.session_id AS victim_session_id, 
    der.blocking_session_id AS blocking_session_id, 
    OBJECT_NAME(s2.objectid) AS blocked_object_name, 
    ist.RESOURCE_TYPE AS blocked_resource_type, 
    ist.RESOURCE_SUBTYPE AS blocked_resource_subtype, 
    ist.resource_description AS blocked_resource_description, 
    s1.TEXT AS blocking_text, 
    s1.dbid AS blocking_db, 
    s1.ObjecTID AS blocking_object_id, 
    s1.number AS blocking_object_index, 
    der.request_mode AS blocking_resource_mode, 
    der.request_type AS sql_server_request_type, 
    der.request_status AS blocking_request_status 
FROM sys.dm_exec_cessions dec 
CROSS APPLY sys.dm_exec_requests der 
INNER JOIN sys.dm_tran_session_transactions tst 
    ON dec.session_id = tst.session_id 
INNER JOIN sys.dm_tran_locks tlt 
    ON tst.transaction_id = tlt.transaction_id 
INNER JOIN sys.dm_tran_locks as il 
INNER JOIN sys.sysprocesses as s1 
    ON il.request_session_id = s1.spid ON tlt.resource_associated_entity_id = il.resource_associated_entity_id 
INNER JOIN information_schema.TABLES it 
    ON il.resource_associated_entity_id = it.object_id 
INNER JOIN sys.dm_os_waiting_tasks as wt 
    ON der.session_id = wt.session_id 
INNER JOIN sys.dm_os_tasks as tsk 
    ON wt.exec_context_id = tsk.exec_context_id 
INNER JOIN sys.dm_os_workers as w 
    ON tsk.worker_address = w.worker_address 
INNER JOIN sys.dm_exec_sessions as s2 
    ON dec.session_id = s2.session_id 
WHERE der.blocking_session_id <> 0