March 20, 2012 at 2:16 pm
I've just deployed a number of new stored procedures to production. There were no deadlocks in testing. Now, we're seeing deadlocks.
I have information about when the deadlock occured, which stored procedure was being run by the victim process, and the SPID. However, without knowing the resources that were being contested and which procedure was being run by the winner process, I can't draw any conclusions.
The procedures that are deadlock victims (two different ones) are updating two heavily updated tables, in the same order. There are other procedures updating these and other tables. Users cannot contend for the same rows because the user id is part of the primary key and users only update their own rows.
The production database is being hit very heavily right now since our users are eager to get the new functionality we just deployed. The heavy usage will continue for at least a week.
I am not the production DBA. A vendor runs our database. I don't have direct access to production. The vendor doesn't appear to have a DBA, so I will need to send them detailed instructions.
I suppose the first step is to enable deadlock tracing. According to Microsoft, I need to do this by restarting the production database (not possible) or turning on the trace when the production database has no active users (not possible).
http://msdn.microsoft.com/en-us/library/ms187329.aspx
Next steps??
March 20, 2012 at 2:58 pm
I would start by looking at the winner of the deadlock and the losers. When both are run a the same time, what would be causing the deadlock? Can you eliminate that? I have found, in my company, that the culprit is usually an isolation level that they throw in there because they don't understand SQL and what it really does.
Jared
CE - Microsoft
March 20, 2012 at 3:09 pm
You don't need to run profiler traces or to restart the SQL Server at all. The traceflag that you referenced is very, very light weight and will cause no hassle if you decide to use it, but it's not necessary on SQL 2008.
On SQL 2008, look in the system health extended events session for the deadlock graphs. The system health event session runs by default and catches deadlock graphs among other things.
Trying to debug a deadlock without knowing all the processes involved and all the resources is a waste of time, as you found. First step, get the deadlock graphs out of the extended events session. That will give you all the processes involved and all the resources.
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
March 20, 2012 at 4:44 pm
Thanks for information on extended events. I don't have permission to view them, but I've sent an appropriate query to those who do.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply