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

1. exec sp_who2  

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

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

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时间, 内存大小,运行总时间,逻辑读数等.

use [master]
GO
SELECT
DB_NAME(Blocked.database_id) AS ‘database‘,
Blocked.Session_ID AS ‘blocked SPID‘,
Blocked_SQL.TEXT AS ‘blocked SQL‘,
Waits.wait_type AS ‘wait resource‘,
Blocking.Session_ID AS ‘blocking SPID‘,
Blocking_SQL.TEXT AS ‘blocking SQL‘,
sess.status AS ‘blocking status‘,
sess.total_elapsed_time AS ‘blocking elapsed time‘,
sess.logical_reads AS ‘blocking logical reads‘,
sess.memory_usage AS ‘blocking memory usage‘,
sess.cpu_time AS ‘blocking cpu time‘,
sess.program_name AS ‘blocking program‘,
GETDATE() AS ‘timestamp‘
FROM sys.dm_exec_connections AS Blocking
INNER JOIN sys.dm_exec_requests AS Blocked ON Blocked.Blocking_Session_ID = Blocking.Session_ID
INNER JOIN sys.dm_os_waiting_tasks AS Waits ON waits.Session_ID = Blocked.Session_ID
INNER JOIN sys.dm_exec_sessions sess ON sess.session_id = Blocking.Session_ID
CROSS APPLY sys.dm_exec_sql_text(Blocking.most_recent_sql_handle) AS Blocking_SQL
CROSS APPLY sys.dm_exec_sql_text(Blocked.sql_handle) AS Blocked_SQL

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

标签：idt   source   快速   sdn   mem   logic   ase   join   tar