Facing deadlock issue more offen

Question

Facing deadlock issue more offen

ravisamigo

SSCertifiable

Points: 7440
More actions
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.

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

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;-)

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 3

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning