Post reply

Facing deadlock issue more offen

  • June 13, 2012 at 11:00 pm

    #275763

    Dear All,

    I'm facing lot of deadlocks more often in our production server and even we aren't able to login to the SSMS.

    When I'm trying to login to SSMS, it's throwing " Timeout Errors" after sometime when I looked into the SQLServer Log, there are more deadlocks.

    I think, deadlock should blocked only queries with was in deadlock list, but we have situation when we cannot connect to SQLServer even locally from management studio.

    Please advise how I can proceed further on this.

    Thanks and Regards,

    Ravi.

  • June 14, 2012 at 2:50 am

    #1500835

    Timeouts are probably from normal blocking, not the deadlocks themselves. You probably need to spend some time tuning queries.

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 14, 2012 at 5:27 am

    #1500913

    setup the profiler and trace the queries which are causing time out. Column filter Error code : 2

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • June 14, 2012 at 6:35 am

    #1500943

    Timeouts might be from blocking during the rollback of transactions that were selected as a victim of deadlocks, but I'm with Gail, it's likely just standard blocking. Deadlocks are both a structural query design issue AND a performance issue. If you're getting more and more deadlocks as well as timeouts, it sounds like performance is getting worse on your system. I'd suggest monitoring the system to identify where the slow points and bottlenecks are and then addressing them. If you have problems with this, my book can help. Also, take a look at the book, Troubleshooting for the Accidental DBA[/url]. It's a free download.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply