2.死锁跟踪

7.死锁跟踪

• 产生死锁原因
  1、session连接持有锁时间过长
  2、锁数目过多
  3、锁粒度过大

7.1利用服务器端跟踪

创建脚本

-- 针对那个库进行
use hantest
go

    --创建跟踪文件返回值
    declare @rc int
    --创建一个跟踪句柄
    declare @TraceID int
    --创建跟踪文件路径
    declare @TraceFilePath nvarchar(500)
    set @TraceFilePath=N'D:\DBA_TOOLS\db_deadLock_log'
    --跟踪文件的大小
    declare @maxfilesize bigint
    set @maxfilesize=200
    --设置停止的时间
    declare @EndTime datetime
    set @EndTime=null
    --设置系统默认的操作
    declare @options int
    set @options=2
    --设置默认滚动文件的数目
    declare @filecount int
    set @filecount=5

    exec @rc=sp_trace_Create
    @TraceID output,
    @options,
    @TraceFilePath,
    @maxfilesize,
    @EndTime,
    @filecount
    if(@rc=0)


    declare @on bit  
    set @on = 1  
    --下述语句中的148指的是locks:deadlock graph事件(参见sys.trace_events),12指的是spid列(参见sys.trace_columns)  
    exec sp_trace_setevent @TraceID, 148, 12, @on    
    exec sp_trace_setevent @TraceID, 148, 11, @on  
    exec sp_trace_setevent @TraceID, 148, 4, @on  
    exec sp_trace_setevent @TraceID, 148, 14, @on  
    exec sp_trace_setevent @TraceID, 148, 26, @on  
    exec sp_trace_setevent @TraceID, 148, 64, @on  
    exec sp_trace_setevent @TraceID, 148, 1, @on  
    -- 启动跟踪  
    exec sp_trace_setstatus @TraceID, 1  
    -- 记录下跟踪ID，后面使用  
    select TraceID = @TraceID  
    goto finish  
    error:   
    select ErrorCode=@rc  
    finish:   
go  

运行上述语句后，每当SQL Server中发生死锁事件,都会自动往文件D:\DBA_TOOLS\db_deadLock_log\deadlockdetect.trc中插入一条记录

开机启动

--默认重启实例后，跟踪会取消，所以写一个SP做实例启动执行
use master

go
create proc StartBlackBoxTrace
as
begin
                --默认开启追踪所有的SQL 执行语句，文件文件路径为默认
               --创建跟踪文件返回值
            declare @rc int
            --创建一个跟踪句柄
            declare @TraceID int
            --创建跟踪文件路径
            declare @TraceFilePath nvarchar(500)
            set @TraceFilePath=N'D:\DBA_TOOLS\db_deadLock_log'
            --跟踪文件的大小
            declare @maxfilesize bigint
            set @maxfilesize=200
            --设置停止的时间
            declare @EndTime datetime
            set @EndTime=null
            --设置系统默认的操作
            declare @options int
            set @options=2
            --设置默认滚动文件的数目
            declare @filecount int
            set @filecount=5

            exec @rc=sp_trace_Create
            @TraceID output,
            @options,
            @TraceFilePath,
            @maxfilesize,
            @EndTime,
            @filecount
            if(@rc=0)
            select  @TraceID


            declare @on bit  
            set @on = 1  
            --下述语句中的148指的是locks:deadlock graph事件(参见sys.trace_events),12指的是spid列(参见sys.trace_columns)  
            exec sp_trace_setevent @TraceID, 148, 12, @on    
            exec sp_trace_setevent @TraceID, 148, 11, @on  
            exec sp_trace_setevent @TraceID, 148, 4, @on  
            exec sp_trace_setevent @TraceID, 148, 14, @on  
            exec sp_trace_setevent @TraceID, 148, 26, @on  
            exec sp_trace_setevent @TraceID, 148, 64, @on  
            exec sp_trace_setevent @TraceID, 148, 1, @on  
            -- 启动跟踪  
            exec sp_trace_setstatus @TraceID, 1  
END
GO
--将该存储过程设置为SQL Server服务启动时自动启动
EXEC sp_procoption
'StartBlackBoxTrace','STARTUP','ON'
print 'ok'
GO

查看traces

--查看
select * from sys.traces

暂停和停止及删除服务器端跟踪

--删除
exec sp_trace_setstatus 2, 0
exec sp_trace_setstatus 2, 2

如果要启动上面的服务端跟踪，可运行下面的语句：

exec sp_trace_setstatus 2, 1 --第一个参数2表示 TraceID，可以通过select * from sys.traces查看跟踪ID。第二个参数表示将状态改为1，即启动

如果要停止上面的服务器端跟踪，可运行下面的语句：

exec sp_trace_setstatus 1, 0 --第一个参数表示TraceID,即步骤1中的输出参数。第二个参数表示将状态改为0，即暂停

如果要移除上面的服务器端跟踪，可运行下面的语句：

exec sp_trace_setstatus 1, 2 --第一个参数表示TraceID,即步骤1中的输出参数。第二个参数表示将状态改为2，即停止

查看跟踪文件内容

对于上面生成的跟踪文件(D:/DBA_TOOLS/db_deadLock_lo/deadlockdetect.trc),可通过两种方法查看：

1).执行t-sql命令

select * from fn_trace_gettable('D:/DBA_TOOLS/db_deadLock_lo/deadlockdetect.trc',1)  

结果中的Extricate列即以XML的形式返回死锁的详细信息。

2).在SQL Server Profiler中打开。

依次 进入Profiler -> 打开跟踪文件 ->选择D:/DBA_TOOLS/db_deadLock_lo/deadlockdetect.trc，就可以看到以图形形式展现的死锁信息