December 19, 2008 at 4:03 am
Hi,
On our prodution system we encounter a few deadlocks each day (apporox 5). There is no real pattern to when but it is always the same two stored procedures involved.
One of the other DBA's set the traceflag to log all dealocks to the error log as the Deadlock Graphs generated via the alert we have setup are not that usefull as all they identify is the two stored procedures involved.
Each stored procedure is very long and complex so needed to narrow it down.
The info we get back from the log is that both the stored procedures use a temporary table called #tmp_sorted.
I have not been a DBA for long so was wondering if this could be a concurrency issue as both stored procedures create \ select from a temporary table called #tmp_sorted.
Could this be the problem, if we changed one procedure to create \ select from a temporary table of the same structre but with a different name would this help?
I thought temporary tables were session specific so it wouldn't matter but I am not 100% sure.
I cant post the spr's due to our security policies so release that any advice can only be general but any help would be appreciated.
Sorry if this is in the wrong section, wasn't entirely sure where would be appropriate.
Thanks in advance.
December 19, 2008 at 6:07 am
Do you have traceflag 1222 and/or 1204 enabled? That will output the deadlock graph to the error log when the deadlocks occur. It will specify exactly where the deadlock is occurring.
You're correct that #temp tables are isolated by connection, but you can get deadlocks within a single connection, so you need to examine the deadlock graph carefully to see if that's what is happening.
It's going to be difficult to help beyond general advice since you're unable to post the code.
"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
December 19, 2008 at 7:00 am
Russell.Taylor (12/19/2008)
One of the other DBA's set the traceflag to log all dealocks to the error log as the Deadlock Graphs generated via the alert we have setup are not that usefull as all they identify is the two stored procedures involved.
Post the graph? There's a lot of info in those things, if you know where to look. If you're on 2005, make sure you're using traceflag 1222, not 1204. 1222 is very detailed.
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply