Issue in deadlock in our production server
-
We are facing dead lock issue on prod database, and checked sp_who2 lot of blocking is there, more than 25 and connection was 450.. insert and update operation is going on ? database is default isoloation level..
i killed some SPID....
How can i resolve the problem? Any steps.. Anything do for DBA side...
Please help me..
Thanks
Jerry
-
find out the spid by sp_who2 and use dbcc inputbuffer(spid) and pull out the queries which are creating problem.
is it a sudden deadlock occured or lingering from some days ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-) -
It is happening 3 days, because PLP review for employee, so that more than 600 users connected insert and updating data at same time...
Sp_who
two procedure we found , one is insert and another update with same table,...
database is deafult osolation level, blocking happend pages...
-
solomon.jernas (1/30/2013)
two procedure we found , one is insert and another update with same table,...check whether your indexes are supportiing those queries or not ? , and have you upodated the statistics or not ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-) -
Deadlocks or blocking?
If you had to kill a session to resolve, it's not a deadlock, just long duration blocking.
Take a read through chapters 6 and 7 of http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/, it covers blocks and deadlocks respectively.
Solution - find the procedures involved and tune queries and indexes. Post the procedures here if you need help with that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
It is blocking.. for insert and update operation..
-
Ok, so take a read through chapter 6 of the book I recommended, identify the procedures involved in the blocking, see if you can tune them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Use "With (nolock)" hint with table name in query,
for example
select a.*
from <<tbl_aa>> a with (nolock)
Join <<tbl_bb>> b with (nolock) on a.aa_id = b.bb_id
--Note: You need to replace <<tbl_aa>> and <<tbl_bb>> with your table names.
While updating use rowlock
while using "with (nolock)", you may see some dirty read and several perf. issues in 2005 based on the IO operations and traffic on your server.
-
asmita.patel (2/1/2013)
Use "With (nolock)" hint with table name in query,for example
select a.*
from <<tbl_aa>> a with (nolock)
Join <<tbl_bb>> b with (nolock) on a.aa_id = b.bb_id
--Note: You need to replace <<tbl_aa>> and <<tbl_bb>> with your table names.
While updating use rowlock
while using "with (nolock)", you may see some dirty read and several perf. issues in 2005 based on the IO operations and traffic on your server.
No, no, no, no, no!!!!!
Nolock is not something you slam around on all your queries. It's a lot more than just some dirty reads, it can result in rows duplicated, rows missed. Nolock's fine for when the data doesn't have to be accurate, when within 10% is good enough, but if users expect that their data is right (which most do), using nolock is very irresponsible.
See - http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
Locking hints are for rare cases, not just put down without understanding. For example, using rowlock on queries can actually increase blocking, not reduce it (larger chance of escalation to table lock).
If you want to remove locks, then one of the snapshot isolation levels is a far better idea than nolock everywhere and the resultant hard to reproduce data errors it tends to cause.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (2/1/2013)
asmita.patel (2/1/2013)
Use "With (nolock)" hint with table name in query,for example
select a.*
from <<tbl_aa>> a with (nolock)
Join <<tbl_bb>> b with (nolock) on a.aa_id = b.bb_id
--Note: You need to replace <<tbl_aa>> and <<tbl_bb>> with your table names.
While updating use rowlock
while using "with (nolock)", you may see some dirty read and several perf. issues in 2005 based on the IO operations and traffic on your server.
No, no, no, no, no!!!!!
+1
NOLOCK is a hint only to be used when you know EXACTLY what it is doing.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply