一，如何监控和解决SQL Server的阻塞(1) (当前阻塞)

如何监控和解决SQL Server的阻塞(1) (当前阻塞)

1. 什么是"阻塞"?

阻塞是SQL数据库应用"锁"机制的一个副作用。当一个应用请求针对某个数据库对象(例如全表，某行数据, 或者是某个数据页)加锁后，那么这个锁会阻塞其它的应用请求。这就好像你把家里的大门上了锁，你的老婆没有钥匙，只能等待你回家。她进入房间的请求被你阻塞了，不得不等待，直到你解锁开门。对于数据库来说短暂的阻塞是可以被接受的。而且短暂的阻塞也是数据库的常态。只有当阻塞的时间超过了人们的容忍时间，这种阻塞现象需要DBA的关注。

 

2. 如何发现"当前"的"阻塞"?

 

通过下列工具可以发现当前正在发生的阻塞现象

• 活动监视器 Activity Monitor
• sp_who2
• 动态性能视图(DMV)

 

2.1 活动监视器 Activity Monitor

微软自SQL 2008以后提供了一个图形化的活动监视器来帮助DBA观察"当前的"阻塞现象. 详情请参考 (点击打开链接). DBA需要关注如下的信息列去

• 会话 ID：　是建立连接时分配给每个用户连接的唯一整数 (int)。
• 等待时间(毫秒):此任务等待资源所用的时间（毫秒）。如果任务没有等待，则等待时间为 0。
• 等待类型：最近或当前等待类型的名称。
• 等待资源：所需资源的名称。
• 阻塞者：如果有阻塞会话，则为正阻塞任务的会话的 ID。
• 头阻塞程序：如果有阻塞会话，则标识导致第一个阻塞条件的会话。值为 1 表示其他会话的头阻塞程序。

 

 

2.2 sp_who2

在master数据库下，运行如下语句：　

[sql]  view plain  copy

 

1. exec sp_who2  

 

你会看到下面的信息，它类似活动监视器，显示当前用户请求的阻塞信息．只是表格化了，便于我们过滤一些不相关的内容．

 

通过下面的语句可以将sp_who2的显示信息导入到一张临时表里．当然你也可以导入到一张永久表里．

 

[sql]  view plain  copy

 

1. CREATETABLE #sp_who2 (SPID INT,Status VARCHAR(255),  
2.       Login  VARCHAR(255),HostName  VARCHAR(255),  
3.       BlkBy  VARCHAR(255),DBName  VARCHAR(255),  
4.       Command VARCHAR(255),CPUTime INT,  
5.       DiskIO INT,LastBatch VARCHAR(255),  
6.       ProgramName VARCHAR(255),SPID2 INT,  
7.       REQUESTID INT)  
8.   
9. INSERTINTO #sp_who2 EXECsp_who2  
10. SELECT  *   
11. FROM  #sp_who2  
12. WHERE       DBName <> 'master'  
13. ORDER BY    DBName ASC  
14.    
15. DROP TABLE #sp_who2  

  

2.3 使用动态性能视图 (推荐)

动态性能视图展示了更多更丰富的信息,帮助DBA快速诊断"当前"的阻塞现象. 它还能捕获诸如SQL语句和执行当前SQL语句已经使用的CPU时间, 内存大小,运行总时间,逻辑读数等.

 

运行如下语句:

 

[sql]  view plain  copy

 

1. use [master]  
2. GO  
3. SELECT   
4.     DB_NAME(Blocked.database_id)                    AS 'database',  
5.     Blocked.Session_ID                              AS 'blocked SPID',  
6.     Blocked_SQL.TEXT                                AS 'blocked SQL',  
7.     Waits.wait_type                 AS 'wait resource',  
8.     Blocking.Session_ID                             AS 'blocking SPID',  
9.     Blocking_SQL.TEXT                               AS 'blocking SQL',  
10.     sess.status                 AS 'blocking status',  
11.     sess.total_elapsed_time             AS 'blocking elapsed time',  
12.     sess.logical_reads              AS 'blocking logical reads',  
13.     sess.memory_usage               AS 'blocking memory usage',  
14.     sess.cpu_time                   AS 'blocking cpu time',  
15.     sess.program_name               AS 'blocking program',  
16.     GETDATE()                                       AS 'timestamp'  
17. FROM sys.dm_exec_connections AS Blocking   
18.     INNER JOIN sys.dm_exec_requests AS Blocked ON Blocked.Blocking_Session_ID = Blocking.Session_ID  
19.         INNER JOIN sys.dm_os_waiting_tasks AS Waits ON waits.Session_ID = Blocked.Session_ID  
20.         INNER JOIN sys.dm_exec_sessions sess ON sess.session_id = Blocking.Session_ID  
21.         CROSS APPLY sys.dm_exec_sql_text(Blocking.most_recent_sql_handle) AS Blocking_SQL  
22.         CROSS APPLY sys.dm_exec_sql_text(Blocked.sql_handle) AS Blocked_SQL  
23. GO