SQL Server deadlock monitoring

Find the source of SQL Server deadlock and blocking-find the source of SQL Server deadlock and blocking

Copy codeThe Code is as follows:
Use master
Go
Declare @ spid int, @ bl int
DECLARE s_cur CURSOR
Select 0, blocked
From (select * from sysprocesses where blocked> 0)
Where not exists (select * from sysprocesses where blocked> 0) B
Where a. blocked = spid)
Union select spid, blocked from sysprocesses where blocked> 0
OPEN s_cur
Fetch next from s_cur INTO @ spid, @ bl
WHILE @ FETCH_STATUS = 0
Begin
If @ spid = 0
Which of the following causes a database deadlock in select:
'+ CAST (@ bl as varchar (10) +' process number. The SQL statement executed is AS follows'
Else
Select 'process No. SPID: '+ CAST (@ spid as varchar (10) + 'be' +'
Process ID: '+ CAST (@ bl as varchar (10) +' blocking. The SQL syntax executed by the current process is AS follows'
Dbcc inputbuffer (@ bl)
Fetch next from s_cur INTO @ spid, @ bl
End
CLOSE s_cur
DEALLOCATE s_cur

View the current process, or deadlock process, and automatically kill the dead process-view the current process, or deadlock process, and automatically kill the dead Process

Because it is intended for dead, if there is a deadlock process, you can only view the deadlock process. Of course, you can use Parameter Control to view only the deadlock process, whether there is a deadlock or not.

Copy codeThe Code is as follows:
Create proc p_lockinfo
@ Kill_lock_spid bit = 1, -- whether to kill the deadlock process; 1: Kill; 0: only show
@ Show_spid_if_nolock bit = 1 -- if there is no deadlock in the process, whether the normal process information is displayed, 1 is displayed, 0 is not displayed
As
Declare @ count int, @ s nvarchar (1000), @ I int
Select id = identity (int, 1, 1), flag,
Process ID = spid, thread ID = kpid, block process ID = blocked, Database ID = dbid,
Database Name = db_name (dbid), user id = uid, user name = loginame, accumulated CPU time = cpu,
Login Time = login_time, number of opened transactions = open_tran, Process status = status,
Workstation name = hostname, application name = program_name, workstation process ID = hostprocess,
Domain name = nt_domain, NIC address = net_address
Into # t from (
Select flag = 'deadlocked process ',
Spid, kpid, a. blocked, dbid, uid, loginame, cpu, login_time, open_tran,
Status, hostname, program_name, hostprocess, nt_domain, net_address,
S1 = a. spid, s2 = 0
From master .. sysprocesses a join (
Select blocked from master... sysprocesses group by blocked
) B on a. spid = B. blocked where a. blocked = 0
Union all
Select '| _ victim _> ',
Spid, kpid, blocked, dbid, uid, loginame, cpu, login_time, open_tran,
Status, hostname, program_name, hostprocess, nt_domain, net_address,
S1 = blocked, s2 = 1
From master .. sysprocesses a where blocked <> 0
) A order by s1, s2

Select @ count = @ rowcount, @ I = 1

If @ count = 0 and @ show_spid_if_nolock = 1
Begin
Insert # t
Select flag = 'normal process ',
Spid, kpid, blocked, dbid, db_name (dbid), uid, loginame, cpu, login_time,
Open_tran, status, hostname, program_name, hostprocess, nt_domain, net_address
From master .. sysprocesses
Set @ count = @ rowcount
End

If @ count> 0
Begin
Create table # t1 (id int identity (1, 1), a nvarchar (30 ),
B Int, EventInfo nvarchar (255 ))
If @ kill_lock_spid = 1
Begin
Declare @ spid varchar (10), @ sign varchar (10)
While @ I <= @ count
Begin
Select @ spid = process ID, @ sign = sign from # t where id = @ I
Insert # t1 exec ('dbcc inputbuffer ('+ @ spid + ')')
If @ sign = 'deadlocked process' exec ('Kill '+ @ spid)
Set @ I = @ I + 1
End
End
Else
While @ I <= @ count
Begin
Select @ s = 'dbcc inputbuffer ('+ cast (process ID as varchar) + ')'
From # t where id = @ I
Insert # t1 exec (@ s)
Set @ I = @ I + 1
End
Select a. *, the SQL statement of the Process = B. EventInfo
From # t a join # t1 B on a. id = B. id
End
Go

Exec p_lockinfo