Solved

Detect SQL Statement that causing Deadlock

Posted on 2010-09-02
5
689 Views
Last Modified: 2012-05-10
Hello All,

I have a big data application, so many insert/udate/select statement execute at a single time. we are have so many sp's, function's, view's and sometime other people execute SQL commands from Management studio.

Generally we takecare of SQL hints, but We know some old SP's/function's/view's/statemtns/jobs may not using SQL hints.

Now problem is:
We want to detect which SQL statement is causing deadlock. we know sp_who2 and other commands that tell us how to see which spid is causing deadlock, but specifically we want to know Statement that causing problem, also name of SP/View/Job/Function that is related.

For SQL Statement, executed manually, can be determined easily using information available in sp_who2.

Thanks beforehand.
0
Comment
Question by:Mohit Vijay
  • 2
  • 2
5 Comments
 
LVL 4

Accepted Solution

by:
MSSystems earned 300 total points
ID: 33589587
I got this from a friend and should work for you.
USE [master]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO



ALTER PROCEDURE [dbo].[sp_rk_blocker_blockee]

AS



set transaction isolation level read uncommitted

set nocount on



declare @blocker_spid int, @blockee_spid int, @blockee_blocker int

declare @blockee_waitime int



IF EXISTS

(SELECT * FROM master.dbo.sysprocesses

WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses))

BEGIN





DECLARE blocker_cursor CURSOR FOR

     SELECT spid FROM master.dbo.sysprocesses WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)

AND blocked=0 



DECLARE blockee_cursor CURSOR FOR

     SELECT spid, blocked, waittime FROM master.dbo.sysprocesses WHERE blocked > 0





OPEN blocker_cursor 





FETCH NEXT FROM blocker_cursor INTO @blocker_spid

     WHILE (@@FETCH_STATUS =0 )

          BEGIN

               Select 'Blocker: ',@blocker_spid 

               exec sp_executesql N'dbcc inputbuffer(@Param)',N'@Param int', @blocker_spid 

               OPEN blockee_cursor

                         FETCH NEXT FROM blockee_cursor INTO @blockee_spid, @blockee_blocker, @blockee_waitime

                    

                         WHILE (@@fetch_status = 0)

                              BEGIN

                              IF (@blockee_blocker=@blocker_spid)

                                   BEGIN

                                        SELECT 'Blockee: Waittime:', @blockee_spid, @blockee_waitime

                                        exec sp_executesql N'dbcc inputbuffer(@Param)',N'@Param int', @blockee_spid

                                   END

                              FETCH NEXT FROM blockee_cursor INTO @blockee_spid, @blockee_blocker, @blockee_waitime                               

                              END

               CLOSE blockee_cursor

          FETCH NEXT FROM blocker_cursor INTO @blocker_spid

          END

CLOSE blocker_cursor

DEALLOCATE blockee_cursor 

DEALLOCATE blocker_cursor



END

ELSE

SELECT 'No blocking processes found!'

Open in new window

0
 
LVL 8

Author Comment

by:Mohit Vijay
ID: 33589656
Thanks for quick response. I will check it on detail. But What these statements do? I dont think they will show me the SQL Statement/Stored Procedure/View/Function those causing deadlock.
0
 
LVL 4

Expert Comment

by:MSSystems
ID: 33590347
The stored procedure will return the following, if anything was found. I hope this helps.

         
--------- -----------
Blocker:  56

EventType      Parameters EventInfo
-------------- ---------- ----------------------------
Language Event 0          exec [sp_rk_blocker_blockee]

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
                               
------------------ ----------- -----------
Blockee: Waittime: 58          46380

EventType      Parameters EventInfo
-------------- ---------- ------------------------------------------------------------------------------------------------------------------
Language Event 0          -- 3) Run in second connection
BEGIN TRAN
UPDATE tempdb.dbo.bar SET col1 = 1
UPDATE tempdb.dbo.foo SET col1 = 1

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
0
 
LVL 5

Assisted Solution

by:ThakurVinay
ThakurVinay earned 200 total points
ID: 33594463
0
 
LVL 8

Author Comment

by:Mohit Vijay
ID: 33612819
I am still reviewing facts..
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now