Solved

Detect SQL Statement that causing Deadlock

Posted on 2010-09-02
5
738 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Viewers will learn how the fundamental information of how to create a table.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

628 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