none
How to Find the Deadlock in sql server 2005

    Question

  • Hi All,
    How can i know the deadlocks in SQL server is there any query is there?

    i know how to find the blocking but i want to find the Deadlocks without using the Query Profiles.

    if any body have any idea give me the reply ASAP.

    Thanks,
    Prasad.
    Wednesday, December 30, 2009 9:50 AM

Answers

  • Thankyou Tom,

    how can i know the changed "ISOLIATION LEVEL" give me the query and give me the noremal level information of that.
    i will check it, this is very helpful for me.

    Thanks,
    Prasad.
    If you capture a deadlock graph the isolation level is contained in the information for each of the contributing process nodes.  Otherwise it is also available in sys.dm_exec_requests.

    SELECT session_id, 
       isolation_level =  
       CASE transaction_isolation_level 
          WHEN 0 THEN 'Unspecified' 
          WHEN 1 THEN 'ReadUncomitted' 
          WHEN 2 THEN 'ReadCommitted' 
          WHEN 3 THEN 'Repeatable' 
          WHEN 4 THEN 'Serializable' 
          WHEN 5 THEN 'Snapshot' 
          ELSE 'UNKNOWN' 
       END 
    FROM sys.dm_exec_requests 
    WHERE session_id > 50
    




    Actual "deadlocks" in SQL Server are extremely rare, unless you have changed the "ISOLIATION LEVEL" or have very long running open transactions.  If either is the case, you should look at changing your code to avoid the deadlock.



    This is not true.  Two of the most common forms of deadlocks have nothing to do with an isolation level change or long running transaction.  The most common deadlock occurs as the result of a bookmark lookup from  a non-clustered to a clustered index.  The second most common deadlock lately has been intraquery parallelism deadlocking which occurs between worker threads for a single query waiting in the exchange iterator.  I think you have take to simplistic of a view towards deadlocks, as they can and do occur under the default isolation level of READ COMMITTED, and with short duration < 100-200ms transactions.
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Thursday, December 31, 2009 8:09 AM

All replies

  • If you are looking for a Deadlock Graph, then you can enable -T1222 by adding that to the startup parameters for the SQL instance or executing DBCC TRACEON(1222,-1) from a query window. In case a deadlock is encountered by the SQL Server instance, then the deadlock graph would be sent to the SQL Errorlogs.

    Here is a blog post for deciphering deadlock graphs:
    http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
    This posting is provided "AS IS" with no warranties, and confers no rights. My Blog: Troubleshooting SQL
    Wednesday, December 30, 2009 12:11 PM
  • Check Jonathan's article in Deadlocks. usually, proper indexing can solve your problem. 

    Troubleshoot Deadlocking in SQL Server.
    Troubleshoot Deadlocking in SQL Server 2005 using Profiler.
    Understanding the Anatomy of a Deadlock.
    Using the SQL Server Profiler.


    Madhu
    MCITP, MCTS, MCDBA,MCP-- Blog : http://experiencing-sql-server-2008.blogspot.com/
    Wednesday, December 30, 2009 12:27 PM
  • Thankyou Amit,
    if i am excuting the above query is there any problme for performence please explain me.


    Thanks,
    Prasad.
    Wednesday, December 30, 2009 12:59 PM
  • Thankyou Amit,
    if i am excuting the above query is there any problme for performence please explain me.


    Thanks,
    Prasad.
    There shouldn't be.  In 6 years I have never had a performance problem associated with having a deadlock trace flag turned on, even on systems where it stayed on for a month or more under significant production workloads.  Another way to collect them is with Event Notifications which fire asynchronously inside SQL using Service Broker.  The added benefit here is that if you have Database Mail configured, you can get an instantaneous email notification with the deadlock graph in it as well:

    
    
    -- ============================================================
    -- Create Event Notifications for Deadlock Graph Capture
    -- ============================================================
    CREATE DATABASE SystemEvents
    GO
    
    ALTER DATABASE SystemEvents SET ENABLE_BROKER
    GO
    
    USE SystemEvents
    GO
    
    CREATE TABLE CapturedEvents
    (message_sequence_number int not null ,
    message_body xml not null );
    
    GO
    
    -- Create a certificate to sign stored procedures with
    CREATE CERTIFICATE [SystemEventsCertificate]
    ENCRYPTION BY PASSWORD = '$tr0ngp@$$w0rd'
    WITH SUBJECT = 'Certificate for signing SystemEvents Stored Procedures' ;
    GO
    
    -- Backup certificate so it can be create in master database
    BACKUP CERTIFICATE [SystemEventsCertificate]
    TO FILE = 'd:\Srvapps\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\SystemEventsCertificate.CER' ;
    GO
    
    -- Add Certificate to Master Database
    USE [master]
    GO
    CREATE CERTIFICATE [SystemEventsCertificate]
    FROM FILE = 'd:\Srvapps\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\SystemEventsCertificate.CER' ;
    GO
    
    -- Create a login from the certificate
    CREATE LOGIN [SystemEventsLogin]
    FROM CERTIFICATE [SystemEventsCertificate];
    GO
    
    GRANT AUTHENTICATE SERVER TO [SystemEventsLogin]
    -- Create a MSDB User for the Login
    USE [msdb]
    GO
    CREATE USER SystemEventsLogin FROM LOGIN SystemEventsLogin
    GO
    
    -- Add msdb login/user to the DatabaseMailUserRole
    EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole' , @membername = 'SystemEventsLogin' ;
    GO
    
    -- Switch back to SystemEvents database
    USE [SystemEvents]
    GO
    
    -- Create the Activation Stored Procedure to Process the Queue
    IF EXISTS (SELECT * FROM dbo.sysobjects
    WHERE id = OBJECT_ID (N'[dbo].[ProcessSystemEvents]' ) AND OBJECTPROPERTY (id, N'IsProcedure' ) = 1)
    DROP PROCEDURE [dbo].[ProcessSystemEvents]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE PROCEDURE [dbo].[ProcessSystemEvents]
    WITH EXECUTE AS OWNER
    AS
    
    DECLARE @message_body nvarchar (MAX )
    DECLARE @message_sequence_number int
    DECLARE @dialog uniqueidentifier
    DECLARE @email_message nvarchar (MAX )
    
    WHILE (1 = 1)
    BEGIN
    BEGIN TRANSACTION
    
    -- Receive the next available message FROM the queue
    
    WAITFOR (
    RECEIVE TOP (1) -- just handle one message at a time
    @message_body=message_body,
    @message_sequence_number=message_sequence_number
    FROM dbo.SystemEventQueue
    ), TIMEOUT 1000 -- if the queue is empty for one second, give UPDATE AND GO away
    
    -- If we didn't get anything, bail out
    IF (@@ROWCOUNT = 0)
    BEGIN
    RAISERROR (N'Error WHILE receiving Service Broker message FROM queue DeadLockNotificationsQueue.' , 16, 1) WITH LOG ;
    ROLLBACK TRANSACTION
    BREAK
    END
    
    INSERT INTO CapturedEvents (message_sequence_number, message_body)
    VALUES (@message_sequence_number, @message_body)
    
    SELECT @email_message =
    CAST ('EventType:' AS CHAR (25)) + message_data.value('(/EVENT_INSTANCE/EventType)[1]' , 'varchar(128)' ) +
    CHAR (10) + CHAR (13) +
    CAST ('ServerName:' AS CHAR (25)) + message_data.value('(/EVENT_INSTANCE/ServerName)[1]' , 'varchar(128)' ) +
    CHAR (10) + CHAR (13) +
    CAST ('PostTime:' AS CHAR (25)) + message_data.value('(/EVENT_INSTANCE/PostTime)[1]' , 'varchar(128)' ) +
    CHAR (10) + CHAR (13) +
    CAST ('TextData:' AS CHAR (25)) +
    CHAR (10) + CHAR (13) +
    CHAR (10) + CHAR (13) +
    CAST (message_data.query('/EVENT_INSTANCE/TextData/*' ) AS varchar (max ))
    FROM
    (SELECT cast ( @message_body as xml ) as message_data ) as sub
    
    -- Uncomment to use database mail for notifications
    /*
    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'SQL Monitor', -- your defined email profile
    @recipients = 'notification@domain.com', -- your email
    @subject = 'System Event Notification',
    @body = @email_message;
    */
    
    -- Commit the transaction. At any point before this, we could roll
    -- back - the received message would be back on the queue AND the response
    -- wouldn't be sent.
    COMMIT TRANSACTION
    END
    GO
    
    -- Sign the procedure with the certificate's private key
    ADD SIGNATURE TO OBJECT::[ProcessSystemEvents]
    BY CERTIFICATE [SystemEventsCertificate]
    WITH PASSWORD = '$tr0ngp@$$w0rd' ;
    GO
    
    -- Create a service broker queue to hold the events
    CREATE QUEUE SystemEventQueue
    WITH STATUS = ON ,
    ACTIVATION
    (PROCEDURE_NAME = [ProcessSystemEvents] ,
    MAX_QUEUE_READERS = 1,
    EXECUTE AS OWNER)
    GO
    
    -- Create a service broker service receive the events
    CREATE SERVICE SystemEventService
    ON QUEUE SystemEventQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
    GO
    
    -- Create a new route for the service on the Local Address
    CREATE ROUTE SystemEventRoute
    WITH SERVICE_NAME = 'SystemEventService' ,
    ADDRESS = 'LOCAL' ;
    GO
    
    -- Create the event notification for deadlock graphs on the service
    CREATE EVENT NOTIFICATION CaptureDeadlocks
    ON SERVER
    WITH FAN_IN
    FOR DEADLOCK_GRAPH
    TO SERVICE 'SystemEventService' , 'current database' ;
    GO
    
    -- Query the catalog to see the notification
    SELECT *
    FROM sys.server_event_notifications
    
    
    -- Use this script to clean up the above example
    /*
    
    USE [SystemEvents]
    DROP EVENT NOTIFICATION CaptureDeadlocks ON SERVER
    DROP SERVICE SystemEventService
    DROP ROUTE SystemEventRoute
    DROP QUEUE SystemEventQueue
    DROP TABLE dbo.CapturedEvents
    
    USE [master]
    DROP DATABASE SystemEvents
    GO
    
    USE [msdb]
    DROP USER SystemEventsLogin
    GO
    USE [master]
    DROP LOGIN SystemEventsLogin
    DROP CERTIFICATE [SystemEventsCertificate]
    
    */ 


    If you are on SQL 2008, you can get them without turning anything on by querying the default system_health extended events session:

    Retrieving Deadlock Graphs with SQL Server 2008 Extended Events



    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Wednesday, December 30, 2009 1:29 PM
  • Actual "deadlocks" in SQL Server are extremely rare, unless you have changed the "ISOLIATION LEVEL" or have very long running open transactions.  If either is the case, you should look at changing your code to avoid the deadlock.


    Wednesday, December 30, 2009 2:50 PM
  • Thankyou Tom,

    how can i know the changed "ISOLIATION LEVEL" give me the query and give me the noremal level information of that.
    i will check it, this is very helpful for me.

    Thanks,
    Prasad.
    Thursday, December 31, 2009 7:21 AM
  • Detecting and Ending Deadlocks:

    http://msdn.microsoft.com/en-us/library/ms178104.aspx

    How to resolve a deadlock:

    http://support.microsoft.com/default.aspx/kb/832524

    Analyzing Deadlocks with SQL Server Profiler:

    http://msdn.microsoft.com/en-us/library/ms188246.aspx


    Regards,
    Jai
    Thursday, December 31, 2009 7:52 AM
  • Thankyou Tom,

    how can i know the changed "ISOLIATION LEVEL" give me the query and give me the noremal level information of that.
    i will check it, this is very helpful for me.

    Thanks,
    Prasad.
    If you capture a deadlock graph the isolation level is contained in the information for each of the contributing process nodes.  Otherwise it is also available in sys.dm_exec_requests.

    SELECT session_id, 
       isolation_level =  
       CASE transaction_isolation_level 
          WHEN 0 THEN 'Unspecified' 
          WHEN 1 THEN 'ReadUncomitted' 
          WHEN 2 THEN 'ReadCommitted' 
          WHEN 3 THEN 'Repeatable' 
          WHEN 4 THEN 'Serializable' 
          WHEN 5 THEN 'Snapshot' 
          ELSE 'UNKNOWN' 
       END 
    FROM sys.dm_exec_requests 
    WHERE session_id > 50
    




    Actual "deadlocks" in SQL Server are extremely rare, unless you have changed the "ISOLIATION LEVEL" or have very long running open transactions.  If either is the case, you should look at changing your code to avoid the deadlock.



    This is not true.  Two of the most common forms of deadlocks have nothing to do with an isolation level change or long running transaction.  The most common deadlock occurs as the result of a bookmark lookup from  a non-clustered to a clustered index.  The second most common deadlock lately has been intraquery parallelism deadlocking which occurs between worker threads for a single query waiting in the exchange iterator.  I think you have take to simplistic of a view towards deadlocks, as they can and do occur under the default isolation level of READ COMMITTED, and with short duration < 100-200ms transactions.
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Thursday, December 31, 2009 8:09 AM
  • Jonathan,

    You are correct, my statement was a simplistic view.  I should have clarified my statement.    Since the poster did not give us an actual deadlock graph or any other information about his environment and seemed to be asking a general question and reading the OP other posts about "row locking", I assumed the poster has changed the ISOLATION LEVEL.  Which is the most likely cause of his current issue.

    In an OLTP environment, the most common cause of deadlocks is programmers code trying to lock things manually by using ISOLOATION level settings.  Yes, in specific cases, configuration or code or internal optimization or programming methodology can cause deadlocks.  However, compared to the conservatively thousands of SQL Servers, databases and millions of queries per second in the world, deadlocks are extremely rare. 

    Without a deadlock graph and the code causing the deadlock, none of us can guess as to the cause of the deadlock.
    Thursday, December 31, 2009 2:05 PM