July 21, 2010 at 9:13 pm
Attached are a couple of deadlock graphs I need help resolving. This is occuring after all triggers are disabled on all the tables.
any help is much appreciated. My developers are suggesting NOLOCK, I would like to see another way of solving this problem. This just started occuring recently and keeps occuring frequently
My next step is to check the PLE to see whether I need to throw more memory at the system
Just as a background; the DB server is running on a 8 CPU dedicated server, 16GB memory, 160GB database, basic file spit data on a seperate RAID 5 LUN and log on a RAID 10 LUN.
July 21, 2010 at 9:39 pm
Besides the deadloc trace image, you also need to supply the rest of the deadlock information. One way of doing this is by enabeling the Trace Flag 1204. This will cause SQL to record the deadlock information in the SQL Server Log. With this you will see what SQL Code was being executed in each of the treads. You can then follow this back to the application process.
You will need this to really make sence of the deadlock.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
July 22, 2010 at 2:26 pm
You can find the objects and indexes, which caused the deadlocks, based on the information (hobt_id, objid, index name) provided in the deadlock graphs. Just query catalog views (e.g., sys.partitions).
July 22, 2010 at 2:33 pm
As Leo said, you will also need to know the SQL code (statements in SP, dynamic SQL, ...) by turning on trace 1204. Once you have all the information, you can figure out how to modify the SQL code to avoid deadlocks.
July 22, 2010 at 4:51 pm
Leo,
The SQL is enclosed inside the graph. You can open the XDL file in text and you should the same and more details as the trace. Let me know if you need more
July 22, 2010 at 6:26 pm
Take the deadlock graph xml, and put it into the variable in this code below, and run. It will show you lots of information about the deadlock.
declare @deadlock xml
set @deadlock = 'put your deadlock graph here'
select
[PagelockObject] = @deadlock.value('/deadlock-list[1]/deadlock[1]/resource-list[1]/pagelock[1]/@objectname', 'varchar(200)'),
[DeadlockObject] = @deadlock.value('/deadlock-list[1]/deadlock[1]/resource-list[1]/objectlock[1]/@objectname', 'varchar(200)'),
[Victim] = case when Deadlock.Process.value('@id', 'varchar(50)') = @deadlock.value('/deadlock-list[1]/deadlock[1]/@victim', 'varchar(50)') then 1 else 0 end,
[Procedure] = Deadlock.Process.value('executionStack[1]/frame[1]/@procname[1]', 'varchar(200)'),
[LockMode] = Deadlock.Process.value('@lockMode', 'char(1)'),
[Code] = Deadlock.Process.value('executionStack[1]/frame[1]', 'varchar(1000)'),
[ClientApp] = Deadlock.Process.value('@clientapp', 'varchar(100)'),
[HostName] = Deadlock.Process.value('@hostname', 'varchar(20)'),
[LoginName] = Deadlock.Process.value('@loginname', 'varchar(20)'),
[TransactionTime] = Deadlock.Process.value('@lasttranstarted', 'datetime'),
[InputBuffer] = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)')
from @deadlock.nodes('/deadlock-list/deadlock/process-list/process') as Deadlock(Process)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 25, 2010 at 9:49 am
Thanks Wayne, interesting query. I would just modify it to extract deadlock graph from the XDL file.
I have the details but confused why even with the triggers disabled keys are leading to a deadlock. I would hear from someone who have faced KEY deadlocks. I'm thinking of moving indexes to a seperate file group and file to see if IO parallelism resolves an index deadlock
July 25, 2010 at 11:11 am
G33kKahuna (7/25/2010)
Thanks Wayne, interesting query. I would just modify it to extract deadlock graph from the XDL file.I have the details but confused why even with the triggers disabled keys are leading to a deadlock. I would hear from someone who have faced KEY deadlocks. I'm thinking of moving indexes to a seperate file group and file to see if IO parallelism resolves an index deadlock
When I started the job that I'm currently with, one of the issues I faced was a bunch of deadlocks. After finding the offending queries/procedures, and running execution plans on them, I found that most were doing table/index scans, instead of seeks. Tweaking procs and indexes to achieve seeks has virtually eliminated the deadlocks. Some of these deadlocks were occurring on indexes that the optimizer could use, but would still have to scan.
So, my initial advise is, after determining which procedures / queries are causing the deadlocks, to get the actual execution plans for them. Tweak to get everything running with seeks as fast as possible, then sit back to see how things shake out from there.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 25, 2010 at 11:19 am
G33kKahuna (7/25/2010)
Thanks Wayne, interesting query. I would just modify it to extract deadlock graph from the XDL file.
I have a version of this that will read the deadlock from a deadlock trace file, whether an active trace or not. It shouldn't be that hard to modify this yourself, but if you need what I have just shout out and I'll find it and post it.
I would not be that difficult to use the openrowset(bulk ...) to read in a file, and then work with it.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 26, 2010 at 5:18 am
Here is the Bible about deadlock troubleshooting - http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx. Note it has 2 other parts.
You might want to consider getting a professional to help you out.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 26, 2010 at 3:23 pm
Here is the Bible about deadlock troubleshooting - http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx. Note it has 2 other parts
SQLGuru, thanks for the advise. No offense to Bart, but those articles (all 3) are intended for deadlock novice and a condition that's recreatable. It's a good article but not a Bible per say. In my experience deadlocks are a case by case resolution. I'm afraid my situation needed more behavior modelling to get to a solution
I resolved 1 of the 2 deadlocks; if anyone is interested. The PK (clustered as well) was a combinational index on 3 varchar columns even though the table maintains a GUID for every row. The other non-clustered index in the deadlock was just an added flavor. The dev provided the index after running his query against the optimizer but the optimizer doesn't know the table is transient. So maintenance was the overhead as the table was growing and skrinking as the DB file size is growing. By switching PK to non-clustered and making the GUID, which is never used in any joins, as clustered (again depends on the GUID usage); the deadlock seems to be gone. Continuing test cycles in PROD still.
This link provided some insight
Moving on to the next
July 27, 2010 at 7:34 am
G33kKahuna (7/26/2010)
Here is the Bible about deadlock troubleshooting - http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx. Note it has 2 other parts
SQLGuru, thanks for the advise. No offense to Bart, but those articles (all 3) are intended for deadlock novice and a condition that's recreatable. It's a good article but not a Bible per say. In my experience deadlocks are a case by case resolution. I'm afraid my situation needed more behavior modelling to get to a solution
I resolved 1 of the 2 deadlocks; if anyone is interested. The PK (clustered as well) was a combinational index on 3 varchar columns even though the table maintains a GUID for every row. The other non-clustered index in the deadlock was just an added flavor. The dev provided the index after running his query against the optimizer but the optimizer doesn't know the table is transient. So maintenance was the overhead as the table was growing and skrinking as the DB file size is growing. By switching PK to non-clustered and making the GUID, which is never used in any joins, as clustered (again depends on the GUID usage); the deadlock seems to be gone. Continuing test cycles in PROD still.
This link provided some insight
Moving on to the next
1) the vast majority of SQL Server users are "deadlock novice"s.
2) Seems to me you did use Bart's advice and use indexing to address the deadlock scenario.
3) Unfortunatly you have now set yourself up for some massive index fragmentation problems due to having a clustered GUID.
Best of luck with your efforts.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 28, 2010 at 2:48 pm
1) the vast majority of SQL Server users are "deadlock novice"s.
Strange assumption
2) Seems to me you did use Bart's advice and use indexing to address the deadlock scenario.
Not really, i posted the link that helped. Bart's example adds a combinational index on the filter and retrieve column. My new index uses a column that is neither in a join nor retrieved. So, my implementation is exactly the opposite of his suggestion
3) Unfortunatly you have now set yourself up for some massive index fragmentation problems due to having a clustered GUID.
this is a transient table anyway, so I doubt the fragmentation will be any different. I had the same suspicision; I've been capturing fragmentation before the index and after. I don't see much of a before and after. I'm still monitoring
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply