sqlserver 死锁相关排查及优化

 

sqlserver 无论是排查死锁还是排查性能问题都离不开SQL Server Profiler ，死锁主要勾选哪里：

 

 

 

--查询死锁总数,如果死锁较多建议重启sqlserver服务
SELECT cntr_value AS NumOfDeadLocks
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Locks'
AND counter_name = 'Number of Deadlocks/sec'
AND instance_name = '_Total'

 

--查询死锁的spid
select
request_session_id spid,
OBJECT_NAME(resource_associated_entity_id) tableName
from
sys.dm_tran_locks
where
resource_type='OBJECT'

--删除死锁
kill 70

注意：死锁较多时，安全的做法是重启sqlserver服务

 

 

--以下为查询具体死锁的sql
CREATE FUNCTION [dbo].[DigLock]
(
@spid int,
@orginSpid int
)
RETURNS bit
AS
BEGIN
declare @blockedSpid int=null;

select @blockedSpid=spid from master.dbo.sysprocesses where blocked<>0 and blocked=@spid

if @blockedSpid=@orginSpid
return 1;--检测到了死锁

if @blockedSpid is not null
begin
return dbo.DigLock(@blockedSpid,@orginSpid);
end

return 0;--未检测到死锁
END

 

CREATE VIEW [dbo].[V_DeadLock_Process]
AS
SELECT spid, kpid, blocked, waittype, waittime, lastwaittype, waitresource, dbid, uid, cpu, physical_io, memusage, login_time,
last_batch, ecid, open_tran, status, sid, hostname, program_name, hostprocess, cmd, nt_domain, nt_username,
net_address, net_library, loginame, context_info, sql_handle, stmt_start, stmt_end, request_id
FROM sys.sysprocesses AS sp1
WHERE (blocked <> 0) AND (dbo.DigLock(spid, spid) = 1)

Select * from [dbo].[V_DeadLock_Process]
DBCC INPUTBUFFER (53)--输入会话spid，可查询该会话正在执行的Sql语句，从而知道发生死锁的会话执行了什么Sql语句
--优化方式：1.创建非聚集索引 2.查询使用with (nolock)方式