进程死锁运行状态、进程阻塞、死锁监控

--死锁检测DMV

sys.dm_exec_requests

sys.dm_tran_locks

sys.dm_os_waiting_tasks

sys.dm_tran_database_transactions

sp_who

　　　　--DBCC INPUTBUFFER (spid) 查看spid中的t-sql

　　　　--是 sp_who 系统存储过程的输出中所显示的用户连接系统进程 ID (SPID)。




sp_lock

 --死锁监控

SELECT t1.resource_type AS [信息锁定类型] ,
        DB_NAME(resource_database_id) AS [数据库名] ,
        t1.resource_associated_entity_id AS [锁定的ID] ,
        OBJECT_NAME(resource_associated_entity_id) AS [锁定的对象] ,
        t1.request_mode AS [等待者需求的锁定类型] ,
        t1.request_session_id AS [等待者sid] ,
        t2.wait_duration_ms AS [等待时间] ,
        GETDATE() AS [StartTimeed] ,
        ( SELECT    text
          FROM      sys.dm_exec_requests AS r
                    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)
          WHERE     r.session_id = t1.request_session_id
        ) AS [等待者要运行的批处理] ,
        ( SELECT    SUBSTRING(qt.text, r.statement_start_offset / 2 + 1,
                              ( CASE WHEN r.statement_end_offset = -1
                                     THEN DATALENGTH(qt.text)
                                     ELSE r.statement_end_offset
                                END - r.statement_start_offset ) / 2 + 1)
          FROM      sys.dm_exec_requests AS r
                    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS qt
          WHERE     r.session_id = t1.request_session_id
        ) AS [等待者正要运行的语法] ,
        t2.blocking_session_id AS [锁定者sid] ,
        ( SELECT    text
          FROM      sys.sysprocesses AS p
                    CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)
          WHERE     p.spid = t2.blocking_session_id
        ) AS [锁定者的语法]
 FROM   sys.dm_tran_locks AS t1 ,
        sys.dm_os_waiting_tasks AS t2
 WHERE  t1.lock_owner_address = t2.resource_address 








IF EXISTS ( SELECT  *
            FROM    master.sys.sysprocesses
            WHERE   spid IN ( SELECT    blocked
                              FROM      master.sys.sysprocesses ) )  
--确定有进程被其他的进程锁住
    SELECT  spid AS 进程 ,
            status AS 状态 ,
            登入帐号 = SUBSTRING(SUSER_SNAME(sid), 1, 30) ,
            使用者机器名称 = SUBSTRING(hostname, 1, 12) ,
            是否被锁住 = CONVERT(CHAR(3), blocked) ,
            数据库名称 = SUBSTRING(DB_NAME(dbid), 1, 20) ,
            cmd AS 命令 ,
            waittype AS 等待型态
    FROM    master.sys.sysprocesses
--列出锁住别人(在别的进程中blocked字段出现的值)，但自己未被锁住(blocked=0) 
    WHERE   spid IN ( SELECT    blocked
                      FROM      master.sys.sysprocesses )
            AND blocked = 0 
ELSE 
    SELECT  '没有进程被锁住'

--把死锁信息记录到错误日志
dbcc traceon(1222,1204,3605,-1)

--看查调用语句与父语句以及来源情况

SELECT [Spid] = session_id ,

start_time,

[Database] = DB_NAME(sp.dbid) ,

command,

[User] = nt_username ,

[Status] = er.status ,

[Wait] = wait_type ,

[Individual Query] = SUBSTRING(qt.text,

er.statement_start_offset / 2,

( CASE WHEN er.statement_end_offset = -1

THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))

* 2

ELSE er.statement_end_offset

END - er.statement_start_offset )

/ 2) ,

[Parent Query] = qt.text ,

Program = program_name ,

hostname ,

nt_domain ,

FROM sys.dm_exec_requests er

INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid

CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt

WHERE session_id > 50 -- Ignore system spids.

AND session_id NOT IN ( @@SPID ) -- Ignore this current statement.

ORDER BY 1

--查看表锁

SELECT request_session_id spid,OBJECT_NAME(resource_associated_entity_id)tableName
FROM  sys.dm_tran_locks
WHERE resource_type='OBJECT '

--查看进程状态

select start_time,command,percent_complete,wait_type,text,
session_id,blocking_session_id
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle) s

WITH    temp ( BSID, SID, sql_handle )
          AS ( SELECT   blocking_session_id ,
                        session_id ,
                        sql_handle
               FROM     sys.dm_exec_requests
               WHERE    blocking_session_id <> 0
               UNION ALL
               SELECT   A.blocking_session_id ,
                        A.session_id ,
                        A.sql_handle
               FROM     sys.dm_exec_requests A
                        JOIN temp B ON A.SESSION_ID = B.BSID
             )
    SELECT  C.BSID ,
            C.SID ,
            S.login_name ,
            S.host_name ,
            S.status ,
            S.cpu_time ,
            S.memory_usage ,
            S.last_request_start_time ,
            S.last_request_end_time ,
            S.logical_reads ,
            S.row_count ,
            q.text
    FROM    temp C 
            JOIN sys.dm_exec_sessions S ON C.sid = s.session_id
            CROSS APPLY sys.dm_exec_sql_text(C.sql_handle) Q
    ORDER BY sid

--找到死锁与阻塞的原因的方法:
if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[sp_who_lock]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_who_lock]
GO
--说明 : 查看数据库里阻塞和死锁情况
use master
go
create procedure sp_who_lock
as
begin
declare @spid int,@bl int,
@intTransactionCountOnEntry  int,
@intRowcount   int,
@intCountProperties   int,
@intCounter      int
create table #tmp_lock_who (
id int identity(1,1),
spid smallint,
bl smallint)
IF @@ERROR&lt;&gt;0 RETURN @@ERROR
insert into #tmp_lock_who(spid,bl) select  0 ,blocked
from (select * from sysprocesses where  blocked&gt;0 ) a
where not exists(select * from (select * from sysprocesses
where  blocked&gt;0 ) b
where a.blocked=spid)
union select spid,blocked from sysprocesses where  blocked&gt;0
IF @@ERROR&lt;&gt;0 RETURN @@ERROR
-- 找到临时表的记录数
select     @intCountProperties = Count(*),@intCounter = 1
from #tmp_lock_who
IF @@ERROR&lt;&gt;0 RETURN @@ERROR
if    @intCountProperties=0
select '现在没有阻塞和死锁信息' as message
-- 循环开始
while @intCounter &lt;= @intCountProperties
begin
-- 取第一条记录
select     @spid = spid,@bl = bl
from #tmp_lock_who where Id = @intCounter
begin
if @spid =0
select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10))
+ '进程号,其执行的SQL语法如下'
else
select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被'
+ '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'
DBCC INPUTBUFFER (@bl )
end
-- 循环指针下移
set @intCounter = @intCounter + 1
end
drop table #tmp_lock_who
return 0
end

--需要的时候直接调用,就可以查出引起死锁的进程和SQL语句.

exec sp_who_lock  
发现问题后：就进行语句的优化，避免来再次出现上面现象。Profiler跟踪时主要是看：TextData，Applicationname，Username，Loginname，CpU，Read and write
Duration(这个很重要),spid