September 20, 2011 at 10:15 pm
Hi all,
I have issue by deadloack for MSSQL 2008
How to resolve deadlock for MSSQL 2008 ?
thanks,
phuongnh
September 21, 2011 at 1:22 am
Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.
DBCC TRACEON(1222,-1)
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
September 21, 2011 at 1:39 am
It still deadlock occur.
can resolve finish ?
September 21, 2011 at 2:08 am
Sorry, don't understand
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
September 21, 2011 at 2:12 am
I ran the script "DBCC TRACEON(1222,-1)" but deadlock still appear
Can resolve finish ?
September 21, 2011 at 2:51 am
nhphuong76 (9/21/2011)
I ran the script "DBCC TRACEON(1222,-1)" but deadlock still appearCan resolve finish ?
This trace flag will not resolve the deadlock prob. It'll write a graph in your error log. check it and post here.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
September 21, 2011 at 4:04 am
YOu need to analyze the deadlock graph which will be in the error log. Find out the victim if possible attach the graph here, we will help you out
"More Green More Oxygen !! Plant a tree today"
September 21, 2011 at 6:45 am
nhphuong76 (9/21/2011)
I ran the script "DBCC TRACEON(1222,-1)" but deadlock still appearCan resolve finish ?
I never said the traceflag would fix the deadlocks, please read my post. I said
That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.
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
September 21, 2011 at 7:45 am
i have one exprience in my office!
Daily five or six time deadlock accurse on production server. We optimize program coding also still getting deadlock. so resarch morethen a week Finally we found the reason memory usage is high. So increased memory 4GbB to 8GB , last five month server was running fine. We didn’t got any deadlock.
September 21, 2011 at 7:50 am
senfriends2008 (9/21/2011)
i have one exprience in my office!Daily five or six time deadlock accurse on production server. We optimize program coding also still getting deadlock. so resarch morethen a week Finally we found the reason memory usage is high. So increased memory 4GbB to 8GB , last five month server was running fine. We didn’t got any deadlock.
That's more likely luck than anything else.
None of us can debug this without a lot more info.
September 21, 2011 at 7:58 am
Ninja's_RGR'us (9/21/2011)
senfriends2008 (9/21/2011)
i have one exprience in my office!Daily five or six time deadlock accurse on production server. We optimize program coding also still getting deadlock. so resarch morethen a week Finally we found the reason memory usage is high. So increased memory 4GbB to 8GB , last five month server was running fine. We didn’t got any deadlock.
That's more likely luck than anything else.
Agreed. What likely happened there was that the additional memory reduced the IO waits, so the queries are faster and less likely to deadlock. Give it a few months, queries get slower due to data volumes, deadlocks come right back.
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
September 21, 2011 at 8:03 am
GilaMonster (9/21/2011)
Ninja's_RGR'us (9/21/2011)
senfriends2008 (9/21/2011)
i have one exprience in my office!Daily five or six time deadlock accurse on production server. We optimize program coding also still getting deadlock. so resarch morethen a week Finally we found the reason memory usage is high. So increased memory 4GbB to 8GB , last five month server was running fine. We didn’t got any deadlock.
That's more likely luck than anything else.
Agreed. What likely happened there was that the additional memory reduced the IO waits, so the queries are faster and less likely to deadlock. Give it a few months, queries get slower due to data volumes, deadlocks come right back.
Yup, or 4-5 other similar "non-fixes".
Deadlock is mostly caused by bad code, that just doesn't fix itself with more ram.
September 21, 2011 at 8:09 am
Ninja's_RGR'us (9/21/2011)
Deadlock is mostly caused by bad code, that just doesn't fix itself with more ram.
Poor indexing's the other main 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 recoverability
September 21, 2011 at 8:40 am
GilaMonster (9/21/2011)
Ninja's_RGR'us (9/21/2011)
Deadlock is mostly caused by bad code, that just doesn't fix itself with more ram.Poor indexing's the other main cause.
So true, tho weirdly it's almost a synonym in my mind :-D.
September 21, 2011 at 2:50 pm
A common cause that I have seen mentioned is bookmark lookups.
Process A is reading data from a non-clustered index, but needs to get to the base table to get the rest of the data (bookmark lookup).
At the same time, process B is updating the table and in the process needs to update the non-clustered index.
Process A cannot acquire a shared lock (S) on the base table because process B has already acquired an exclusive (X) lock on it.
Process B cannot acquire an X lock on the nonclustered index because Process A has already acquired an S lock on it.
Deadlock!
This may be rectified by defining the index with INCLUDE columns to prevent the lookup.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply