SQLServer阻塞、死锁和最⼤并⾏度
1.阻塞
阻塞：是指当⼀个数据库会话中的事务，正在锁定其他会话事务想要读取或修改的资源，造成这些会话发出的请求进⼊等待的状态。SQL Server 默认会让被阻塞的请求⽆限期地⼀直等待，直到原来的事务释放相关的锁，或直到它超时 (根据 SET LOCK_TIMEOUT参数)、进程被杀死、服务器关闭。⼀般系统中，偶尔有短时间的阻塞是正常且合理的；但若设计不良的程序，就可能导致长时间的阻塞，这样就不必要地锁定了资源，⽽且阻塞了其他会话欲读取或更新的需求，系统表现为很卡。
项⽬中平时遇到的⼤多数是阻塞问题,⽐如系统页⾯报超时错误、SSMS中执⾏⼀条原本很快的SQL却卡住执⾏不了或需要执⾏很久、甚⾄整个数据库处于瘫痪卡死状态（严重情况）等。
阻塞的原因有很多，这⾥简单罗列下：
1.并发执⾏⼤量表扫描的SQL，由于缺失索引，造成⼤量阻塞，这种现象⾮常常见，例如⽹站⾸页中某个模块加载的SQL中缺失索引，导致⽹站⾸页访问超时。
2.隔离级别的问题，SQL Server默认隔离级别为READ COMMITTED，级别越⾼，并发性越差，造成阻塞的⼏率也越⾼。
3.长时间运⾏的SQL或者存储过程，由于执⾏效率较低或内容较多，事务期间会阻塞住其他会话执⾏
2.死锁
死锁：是指多个事务会话互相等待对⽅释放持有的锁⽽造成的环路，如果没有外部⼲预，死锁将⽆限期持续下去。Sqlserver 中默认产⽣死锁时，会根据事务开销选择终⽌并回滚其中某⼀个事务，让其他事务获取所需的锁完成事务提交。如果系统出现⼤量死锁，会导致⽤户使⽤过程中出现⼤量死锁报错页⾯，事务数据⽆法正常提交，影响系统使⽤。
死锁发⽣的现象往往是很明显的，页⾯会出现报错，如下图
3.阻塞与死锁区别
阻塞现象：系统访问超时、卡慢
死锁现象：系统访问报错
session和application的区别死锁其实也是由于阻塞造成的，会话之间先是阻塞，然后⼜因为产⽣交叉访问，造成环路，最终死锁。死锁的发⽣其实和阻塞是类似的，只不过阻塞是⼀种串⾏的等待，⽽死锁是交叉环路。
等待⽅式：默认情况下，当数据库出现阻塞时，如果没有⼈⼯⼲预，那么该会话会⼀直阻塞下去，直到阻塞该会话的事务结束，或直到LOCK_TIMEOUT设置的超时等待时间（默认为0即⽆限）; ⽽当出现死锁时，数据库会⽴即根据事务的回销，来选择终⽌（回滚）其中⼀个事务，进⽽解除死锁，所以死锁会造成操作⽆效。
4.排查维护
之前在公司平台发布栏⽬⾥发布了SQLServer 性能排查指南，可以下载参阅，下⾯简单说下操作。
4.1 阻塞处理
当系统突然发⽣了超时或卡顿等情况，可以先使⽤下⾯的脚本查看数据库阻塞情况，正常情况下没有结果或者出现少量短时间阻塞，问题发⽣时，通常会出现⼤量长时间阻塞，可以根据显⽰的BlockingSession_TSQL进⾏查看是SQL性能问题还是程序问题，必要时可以选择kill 掉阻塞的会话来解决。
SELECT  R.session_id AS WaitingSessionID ,
S.session_id AS BlockingSessionID ,
< AS WaitingSession_TSQL ,
< AS BlockingSession_TSQL ,
A.wait_duration_ms as WAIT_DURATION_MS ,
S.program_name AS BlockingSession_ApplicationName ,
S.host_name AS BlockingSession_HostName
FROM    sys.dm_exec_requests AS R
INNER JOIN sys.dm_exec_sessions AS S ON R.blocking_session_id = S.session_id
INNER JOIN sys.dm_exec_connections AS C1 ON R.session_id = C1.most_recent_session_id
INNER JOIN sys.dm_exec_connections AS C2 ON S.session_id = C2.most_recent_session_id
INNER JOIN sys.dm_os_waiting_tasks AS A ON A.session_id = C1.most_recent_session_id
CROSS APPLY sys.dm_exec_sql_st_recent_sql_handle) AS Q1
CROSS APPLY sys.dm_exec_sql_st_recent_sql_handle) AS Q2
⽰例:
4.2 死锁处理
当系统中出现死锁时，可以使⽤SQl Profile进⾏死锁监控（具体⽅法参考SQLServer 性能排查指南），捕捉到死锁后进⾏分析，看是否是由于SQL缺失索引导致。
5.最⼤并⾏度
SQL Server 通过设置最⼤并⾏度来限制并⾏计划执⾏时所⽤的处理器数，默认为0，即不限制，由SQL Server⾃⼰控制并⾏。
并⾏开销阈值：指定SQLServer创建和运⾏并⾏查询计划的阈值，仅当运⾏同⼀查询的串⾏计划的估计开销⾼于在“并⾏的开销阈值”中设置的值时，SQLServer才创建和运⾏该查询的并⾏计划。开销指的是在特定硬件配置中运⾏串⾏计划估计需要花费的时间（秒）。
我们的业务系统⼀般都是为OLTP，理想情况下事务很短，这时候其实就没必要通过并⾏来提⾼运⾏速度，所以对于这类系统来说，可以把最⼤并⾏度设为1，也就是不适⽤并⾏操作，从⽽减少不必要的资源等待。
如果是OLAP，由于事务普遍较长，所以并⾏操作往往能提⾼速度和资源利⽤率。
个⼈观点：对于最⼤并⾏度，项⽬上出现过⼏次该问题，导致系统访问超时，修改为1也就是关闭最⼤并⾏度后，问题解决。对于我们公司的系统来说，⼤部分事务都很短，不需要并⾏，⼩部分较长的事务可以改写SQL来使⽤并⾏; 另外也可以调整并⾏阈值，同时设置并⾏度为0或是固定值;
SELECT * FROM TEST  WHERE OBJECT_ID =1 OPTION (MAXDOP 8)
没有最好的⽅法，只有最适合的⽅法

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。