1、阻塞的解决

1.1、SQL命令法确认阻塞情况

SQL1

SELECT dtl.request_session_id as waitingsessionid,
       der.blocking_session_id as blockingsessionid,
       dowt.resource_description,
       der.wait_type,
       dowt.wait_duration_ms,
       DB_NAME(dtl.resource_database_id) as databasename,
       dtl.resource_associated_entity_id as waitingassociatedentity,
       dtl.resource_type as waitingresourcetype,
       dtl.request_type as waitingrequesttype,
       dest.[text] as waitingTSql,
       dtlbl.request_type  blockingrequesttype,
       destbl.[text] as blockingTsql
  from sys.dm_tran_locks as dtl
       join sys.dm_os_waiting_tasks as dowt on dtl.lock_owner_address =dowt.resource_address 
       join sys.dm_exec_requests as der on der.session_id =dtl.request_session_id 
       cross apply sys.dm_exec_sql_text(der.sql_handle) as dest
       left join sys.dm_exec_requests derbl on derbl.session_id =dowt.blocking_session_id 
       outer apply sys.dm_exec_sql_text(derbl.sql_handle) as destbl
       left join sys.dm_tran_locks as dtlbl on derbl.session_id =dtlbl.request_session_id;

SQL2

---查询阻塞
select 0 spid ,'受阻于' 受阻于,blocked from (select * from sysprocesses where blocked>0 ) a    
where not exists(select * from (select * from sysprocesses where blocked>0 ) b   
 where a.blocked=spid) 
 union select spid,'受阻于',blocked 
 from sysprocesses 
 where blocked>0

1.2、可视化监控工具确认阻塞情况

1.3、解决阻塞

--Lock SQL，先把引起阻塞的SQL查询出来，以便确认是查询，还是增删改操作
select spid,text from sys.sysprocesses a with(nolock) 
cross apply sys.dm_exec_sql_text(sql_handle)
where spid=680

1.4 通过终止会话解决阻塞

kill 680
如果是SELECT语句，为了快速解决阻塞，可以用kill杀死引起阻塞的进程。
如果是增删改则建议等待，除非很清楚是从那里发起的操作。

1.5、通过分析优化查询语句解决阻塞或减少阻塞

通常阻塞是因为某个慢查询引起，慢的原因可能是：请求查询的数据范围太多，缺失索引，具体要根据具体的情况进行分析。

2、死锁的解决

2.1、捕获死锁信息

开启跟踪标记法

即执行如下命令：

DBCC TRACEON(1222,-1)
GO

这样如果有死锁，会在ERRORLOG日志中有记录，大概内容如下：

使用Profiler捕获死锁信息

从SSMS的【工具】——>【SQL Server Profiler】启动，模板选择默认即可，如下图：

再【事件选择】中勾选【显示所有事件】展示所有事件，避免跟踪过多事件导致系统压力，可以仅选择死锁图，如图：

然后【运行】，进入监控界面如图：

如果有死锁，单击对应的事件行，比如如果有死锁，Deadlock graph事件捕获效果如下图：

2.2、确认死锁产生的对象

select object_name(i.object_id),
       i.name
from   sys.partitions as p
inner join sys.indexes as i on i.object_id=p.object_id
       and i.index_id=p.index_id
where p.partition_id=72057594048151552

从上面几种死锁捕获信息的方法中，都能捕获到重要的对象ID，把ID带入这个语句，可以找到是哪个对象在死锁发生是作为了牺牲者。再加上日志里面的SQL语句，resource-list等就可以找到死锁产生的具体对象和语句。针对具体的业务对具体的事务进行调整，以解决死锁的问题。

3、解决阻塞和死锁的建议

（1）98%的情况下，建议保留SQL server的默认行为，包括：隔离级别的选择，默认并发模式的选择，锁的管理等
（2）应用程序开发人员多了解一些SQL server管理事务的知识
（3）理解等待和阻塞的区别
（4）认识死锁和阻塞之间的区别

其他有用的SQL

查看阻塞持续时间超过5000ms的会话

SELECT W.session_id AS waiting_session_id,
       W.waiting_task_address,
       W.wait_duration_ms,
       W.wait_type,
       W.blocking_session_id,
       W.resource_description
FROM   sys.dm_os_waiting_tasks AS W
WHERE W.wait_duration_ms >5000
  AND W.blocking_session_id  IS NOT NULL;

查看库内所有在wait状态的锁

SELECT L1.resource_type ,
       DB_NAME(L1.resource_database_id) AS DatabaseName,
       CASE L1.resource_type 
         WHEN 'OBJECT'
         THEN OBJECT_NAME(L1.resource_associated_entity_id,
                          L1.resource_database_id)
         WHEN 'DATABASE' THEN 'DATABASE'
         ELSE CASE WHEN L1.resource_database_id =DB_ID()
                   THEN (SELECT OBJECT_NAME(object_id,
                                L1.resource_database_id)
                         FROM   sys.partitions
                         WHERE  hobt_id =L1.resource_associated_entity_id)
                   ELSE NULL
              END
         END AS ObjectName,
        L1.resource_description ,
        L1.request_session_id ,
        L1.request_mode ,
        L1.request_status 
FROM   sys.dm_tran_locks AS L1
JOIN   sys.dm_tran_locks AS L2 ON L1.resource_associated_entity_id =L2.resource_associated_entity_id 
WHERE  L1.request_status <>L2.request_status 
  AND (L1.resource_description=L2.resource_description
       OR (L1.resource_description IS NULL 
           AND L2.resource_description IS NULL
           )
       )
ORDER BY L1.resource_database_id ,
         L1.resource_associated_entity_id ,
         L1.request_status ASC;