Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

deadlock Expand / Collapse
Author
Message
Posted Tuesday, August 16, 2011 1:12 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 6, 2013 5:15 AM
Points: 345, Visits: 567
Hello Folks,

am in similar kind of situation, on one of my Production server there are so many lockings are there it is causing performance problem.

1)I run a query sp_who4 active it results in same query with same login but different SPID's are showing in command text column.

2) SPID is blocking by same SPID is more than once.
i.e., ex: SPID:98 is BLKBy 98.

3) I run a SQL profiler and please guide me what to do next and how to fix this issue ASAP.
Post #1160381
Posted Tuesday, August 16, 2011 3:21 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 25, 2014 11:56 PM
Points: 225, Visits: 466
srinath.vanama (8/16/2011)
Hello Folks,

am in similar kind of situation, on one of my Production server there are so many lockings are there it is causing performance problem.

1)I run a query sp_who4 active it results in same query with same login but different SPID's are showing in command text column.

2) SPID is blocking by same SPID is more than once.
i.e., ex: SPID:98 is BLKBy 98.

3) I run a SQL profiler and please guide me what to do next and how to fix this issue ASAP.


Srinath... This is a year old thread...

See when you're seeing a spid blocking itself then this is not a blocking issue this is a common scenario in multiple core systems....

Can you confirm you SQL Server edition and sp level.
Post #1160424
Posted Tuesday, August 16, 2011 3:32 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 6, 2013 5:15 AM
Points: 345, Visits: 567
Sql Server 2005 Enterprise edition and SP2.

If this is not a Blocking issue, what might be the problem and how to fix this issue.?

Kindly suggest me to fix this problem.
Post #1160429
Posted Tuesday, August 16, 2011 3:34 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, August 28, 2014 4:00 AM
Points: 21,397, Visits: 9,612
Medhatithi Guha (8/15/2011)
Is this sql server 2005? Then run a profiler and capture the deadlock graph. Deadlock can occur because of a lot of reasons and the deadlock graph will give you a good idea where to start.

Also, just an observation. Most of the times while resolving deadlocks,we tend to look for X locks only. But please do examine the S locks also and let us just remember that a deadlock occurs on one (or more) resource. That resource can be a table itself, a row, a page, an index anything.
Even a simple select and then and update statement may lead to a deadlock.
So best idea would be to get hold of the deadlock graph first and then only can this problem be solved


Please start a new thread.
Post #1160431
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse