Table has no clustered index so what happens if i rebuild indexes?

Question

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?

Answer 1

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.

Answer 2

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.