SQL SERVER性能分析--死锁检测数据库阻塞语句<转>

-- 生成测试表Ta 
if   not   object_id ( ' Ta ' )  is   null  
drop   table  Ta 
go  
create   table  Ta(ID  int   Primary   key ,Col1  int ,Col2  nvarchar ( 10 )) 
insert  Ta 
select   1 , 101 , ' A '   union   all  
select   2 , 102 , ' B '   union   all  
select   3 , 103 , ' C '  
go  

-- 测试单表 
-- ---------------------连接窗口1(update\insert\delete)------------------------------
begin   tran  
-- update 
update  ta  set  col2 = ' BB '   where  ID = 2  
-- 或insert 
-- begin tran 
--  insert Ta values(4,104,'D') 
-- 或delete 
-- begin tran 
--  delete ta where ID=1 

-- rollback tran 
-- -----------------------连接窗口2（查询表）---------------------------------------------
begin   tran  
select   *   from  ta 
-- rollback tran 
-- - --分析-------------------------------------------------- 
-- >SQL SERVER 2005查询死锁进程
select  
request_session_id  as  spid, 
resource_type, 
db_name (resource_database_id)  as  dbName, 
resource_description, 
resource_associated_entity_id, 
request_mode  as  mode, 
request_status  as  Status 
from  
sys.dm_tran_locks 
-- Result:
/*  
进程ID 资源类型 数据库 资源描述 资源关链ID 锁类型 进程状态
----------- ------------- ------ -------------------- ----------------------------- ----- ------ 
59 DATABASE Gepro 0 S GRANT
58 DATABASE Gepro 0 S GRANT
57 DATABASE Gepro 0 S GRANT
56 DATABASE Gepro 0 S GRANT
58 PAGE Gepro 1:1904 72057594039435264 IS GRANT
57 PAGE Gepro 1:1904 72057594039435264 IX GRANT
58 OBJECT              Gepro 853578079 IS GRANT
57 OBJECT Gepro 853578079 IX GRANT
57 KEY Gepro (020068e8b274) 72057594039435264     X      GRANT
58 KEY Gepro (020068e8b274) 72057594039435264 S      WAIT
(9 行受影响) 
*/  

SELECT   DISTINCT
' 进程ID '   =   STR (a.spid,  4 )
,  ' 进程ID状态 '   =   CONVERT ( CHAR ( 10 ), a.status)
,  ' 死锁进程ID '   =   STR (a.blocked,  2 )
,  ' 工作站名称 '   =   CONVERT ( CHAR ( 10 ), a.hostname)
,  ' 执行命令的用户 '   =   CONVERT ( CHAR ( 10 ),  SUSER_NAME (a.uid))
,  ' 数据库名 '   =   CONVERT ( CHAR ( 10 ),  DB_NAME (a.dbid))
,  ' 应用程序名 '   =   CONVERT ( CHAR ( 10 ), a.program_name)
,  ' 正在执行的命令 '   =   CONVERT ( CHAR ( 16 ), a.cmd)
,  ' 登录名 '   =  a.loginame
,  ' 执行语句 '   =  b. text
FROM  master..sysprocesses a  CROSS  APPLY
sys.dm_exec_sql_text(a.sql_handle) b
WHERE  a.blocked  IN  (  SELECT  blocked
FROM  master..sysprocesses )
--  and blocked <> 0
ORDER   BY   STR (spid,  4 )
-- Result
/*
进程ID  进程ID   状态  死锁进程ID  工作站名称 执行命令的用户 数据库名 应用程序名 正在执行的命令 登录名 执行语句
---- ---------- ------ ---------- ---------- ---------- ---------- ---------------- ---------------------------------------------------------------------- ------------------------- 
56 sleeping 0 DC91229126 sa Gepro Microsoft AWAITING COMMAND DC91229126FF442\Administrator SET STATISTICS XML OFF
57 sleeping 0 DC91229126 sa Gepro Microsoft AWAITING COMMAND DC91229126FF442\Administrator SET STATISTICS XML OFF
58 suspended 57 DC91229126 sa Gepro Microsoft SELECT DC91229126FF442\Administrator begin tran select * from ta 
59 runnable 0 DC91229126 sa Gepro Microsoft SELECT DC91229126FF442\Administrator SELECT DISTINCT
60 sleeping 0 DC91229126 sa Gepro Toad for S AWAITING COMMAND DC91229126FF442\Administrator SET FMTONLY OFF;
62 sleeping 0 DC91229126 sa Gepro Toad for S AWAITING COMMAND DC91229126FF442\Administrator 
*/
-- 查连接住信息(spid:57、58) 
select  connect_time,last_read,last_write,most_recent_sql_handle 
from  sys.dm_exec_connections  where  session_id  in ( 57 , 58 ) 
-- 查看会话信息 
select  login_time, host_name ,program_name,login_name,last_request_start_time,last_request_end_time 
from  sys.dm_exec_sessions  where  session_id  in ( 57 , 58 ) 
-- 查看阻塞正在执行的请求 
select  
session_id,blocking_session_id,wait_type,wait_time,wait_resource 
from  
sys.dm_exec_requests 
where  
blocking_session_id > 0 -- 正在阻塞请求的会话的 ID。如果此列是 NULL，则不会阻塞请求
/*
session_id,blocking_session_id,wait_type,wait_time,wait_resource 
58 57 LCK_M_S 2116437 KEY: 6:72057594039435264 (020068e8b274) 
*/  
-- 查看正在执行的SQL语句 
select  
a.session_id,sql. text ,a.most_recent_sql_handle 
from  
sys.dm_exec_connections a 
cross  apply 
sys.dm_exec_sql_text(a.most_recent_sql_handle)  as  SQL  -- 也可用函数fn_get_sql通过most_recent_sql_handle得到执行语句 
where  
a.Session_id  in ( 57 , 58 ) 
/*  
session_id text 
----------- ----------------------------------------------- 
57 SET STATISTICS XML OFF
58 begin tran select * from ta 
*/