Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 755
  • Last Modified:

Detect SQL Statement that causing Deadlock

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
Mohit Vijay
Asked:
Mohit Vijay
  • 2
  • 2
2 Solutions
 
MSSystemsCommented:
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
 
Mohit VijayAuthor Commented:
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
 
MSSystemsCommented:
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
 
ThakurVinayCommented:
0
 
Mohit VijayAuthor Commented:
I am still reviewing facts..
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now