How to Find Blocking and Deadlock in SQL Server

・ SQL Server

#Performance
  • What is deadlock in SQL Server?
  • What is Blocking in SQL Server?
  • What is difference between deadlock and blocking in SQL Server?
  • How to create deliberate deadlock for learning purposes?
  • What are the deadlock traces in SQL Server?
  • How to turn on deadlock traces on and off in SQL Server?
  • How and where to check deadlock information in SQL Server?
--How to check blocking in SQL Server
Exec sp_who;
GO

--How to turn on and off traces and check Traces on SQL Server
DBCC TRACESTATUS();

--How to check If Deadlock traces are enabled or disabled
DBCC TRACESTATUS(1204,1222, -1)

--How to Turn Deadlock Traces on SQL Server
DBCC TRACEON(1204,1222, -1)

--How to Turn deadlock Traces Off
DBCC TRACEOFF(1204,1222, -1)

--create deliberate deadlock
-- Tran1
CREATE TABLE DemoDeadLock1 (SessionNumber INT)
INSERT DemoDeadLock1 SELECT 1

CREATE TABLE DemoDeadLock2 (SessionNumber INT)
INSERT DemoDeadLock2 SELECT 1

--Tran2 in new session
BEGIN TRAN
UPDATE  DemoDeadLock1 SET  SessionNumber= 1

--Tran3
BEGIN TRAN
UPDATE  DemoDeadLock2 SET SessionNumber = 1
UPDATE  DemoDeadLock1 SET SessionNumber = 1

--update Demodeadlock2 table in our Tran2 session
UPDATE DemoDeadLock2 SET SessionNumber = 1