This article is a continuation of "Coping with SQL Server Deadlocks Part-1" If you have not yet read that article and downloaded my "Deadlock Script Kit" please do so now, I will wait. Ready? Ok. Let's go.
Finding the Object that is the root of it
Sometimes even when you have an idea of what query or SP is causing the locking, it may not be apparent what object in it is really involved in the locking. I recently have started using the "Dead Lock Object.txt" script:
object_name(P.object_id) as TableName,
join sys.partitions P on L.resource_associated_entity_id = p.hobt_id
The query above uses the Function index_name, so make sure you create that first. When you run this on the database with issues, it will tell you which resources a currently locked. If you currently have a full "Mexican Stand Off Deadlock" going on you are going to see a truck load of objects listed. This listing really won't be helpful. What I try to do is watch the Fusion Reactor for the page that is causing the issues then run this script on the SQL server BEFORE another thread starts competing with it and causing an escalation. What this script will then tell you is the table or the index that truly has long running locks. Typically you can run the script several times in a row and the SAME table or index will be listed each t ime. CONGRATULATIONS! You just found a bad guy. If it is a table, check its indexes and primary keys. Do they exists? Are they fragged? If it is an index that is the bad guy. Do you really need it? Delete it: do things get better? Recreate it: better or worse? The resource type column can return many different things.. some of which you may have never heard of this page has a refefence for them: http://msdn.microsoft.com/en-us/library/ms190345(v=sql.90).aspx
Overall Index Health
Missing Indexes. The script MissingIndex.txt will tell you a list of the Top missing indexes on your server, in descending order of severity. SQL Server knows about these because it actually creates these in the background when it runs the Query Plan on it, but it doesn't make it a permanent index. Making it a permanent index can speed things up considerably. The script only lists the top 25. You could list EVERY one if you wish. I don't recommend creating them all. Indexes have a cost. It is complete voodoo finding the balance between too few indexes and too many. But if you have query that is used very often and it is not indexed properly: take the servers suggestion and create it.
Duplicate Indexes. The script DuplicateIndex.txt will, not surprisingly, find duplicate indexes. Having two indexes on the same fields is stupid and nasty, but stuff happens, so just remove one of them.
Unused Indexes. Indexes that never or rarely get used are worthless at the best or harmful at the worst. You may have at one time needed those indexes, but have since refactored your code and now the index is as helpful as nipples on a boar. Run the Unusedindex.txt script on your database to find such flotsam and jettison them out the airlock. Your INSERT statements will thank you.
SQL Profiler comes with SQL Server, it can be configured to track Deadlocks on your running code. It also has a Database Tuning Wizard. Here is the down side: while it is running it is a HOG. If you try running it against your production server during a "crisis" time it will just make the crisis look worse. Profiler is much better as a development tool on your developement server under a modest load. Sometimes when I am bashing my head against the wall on a evil production server, I will turn it on for 5 minutes, ignore calls from the customer who want to tell me, "it is running even slower now (thanks, I know)" After I stop the Profiler, I examine the output and run the Tuning wizard against it. But generally this is a last resort, since the techniques above tend to find the issue and do not aggravate the already ailing server.
Well, that is voodoo that I do. How bout you? Any techniques or tips you would care to share with me?