Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

We have a vendor app that has a SQL Server 2005 database. During data entry, app writes data to a table that has no clustered indexes on it. It has a number of indexes on the table, though.

I used the Maintenance Plan wizard to rebuild the indexes on that table.

During performance testing, we started running into deadlock issues. The deadlock graph shows that this particular table without a clustered index is the one being contended for.

I then used the Maintenance Plan wizard to rebuild the indexes with fillfactor 80. No deadlocks now. Any ideas why this fillfactor would make a difference?

share|improve this question

2 Answers 2

up vote 1 down vote accepted

There are several possibilites that I can think of.
@Chris has beaten me to it for part one of what I was going to say, so I won't repeat it.

The second, and IMO less likely, possibility is that index rebuild also updated the statistics, and SQL server is now using different execution plans that avoid the deadlock.

The only way to really tell is to carefully examine the deadlock graph and the before and after QEP's. Which may not be possible now if the issue has been "fixed".

Lastly, I doubt that this is going to be a permanent fix.

share|improve this answer

You don't need a clustered index, although it's generally a good idea. Unless, of course, the primary key is a GUID or the records in the table go through a high percentage of deletes / inserts. I'm thinking of purely temp storage tables.

Most likely the indexes that were being used by the queries that were deadlocking were highly fragmented, which is why the index rebuild caused the problem to go away.

You might profile those queries to see if other indexes would be of help.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.