Solved

Detect SQL Statement that causing Deadlock

Posted on 2010-09-02
5
730 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

738 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