I recently took over a project and they have a SQL job setup to run every three hours which rebuilds the indexes found within the ASP.NET Membership database tables.
This seems pretty high, to rebuild indexes 8 times a day. I get about 2000 new users every day, and a total of about 2 million registered users.
What would you recommend for a proper index rebuilding schedule?
Your deadlocks can definitely be related to the rebuilding of the indexes. There is also no doubt that those indexes don't need to be rebuilt that frequently. At a minimum though you should consider using the ONLINE option if you can to keep the index from being dropped before it's rebuilt.
Here's a guideline we use:
Index should be rebuilt when index fragmentation is greater than 40%. Index should be reorganized when index fragmentation is between 10% to 40%. Index rebuilding process uses more CPU and it locks the database resources. SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index is rebuilt. ONLINE option will keep index available during the rebuilding.
Source: http://blog.sqlauthority.com/2007/12/22/sql-server-difference-between-index-rebuild-and-index-reorganize-explained-with-t-sql-script/
A good rule of thumb is REBUILD when over 30% fragmented, REORGANIZE when between 10% and 30%.
Don't bother with either for tables less than 1000 pages, you won't notice, and even after running a REBUILD for one that is over 30% it will often be left at 30%.
You should probably be aiming to rebuild/reorganize fairly infrequently, weekly at most for an average database. If you're having to defrag the indexes more often than that then you probably need to re-look at your fill factors and padding.
An exception is after bulk data loading, where it might be common to have fragmented the indexes (sometimes its better to disable the index or to drop the indexes and rebuild or them depending upon the data being loaded).
So in summary, 8 times a day does seem excessive.
References:
http://technet.microsoft.com/en-us/library/ms189858.aspx
http://www.sqlmusings.com/2009/03/15/a-more-effective-selective-index-rebuildreorganize-strategy/
http://realworlddba.wordpress.com/2008/01/27/indexes-to-rebuild-or-reorganize/
http://realworlddba.wordpress.com/2008/01/27/indexes-to-rebuild-or-reorganize/
Capture the deadlock graph and you have an actual answer of what is deadlocking, as opposed to a guess. Given that deadlock are (or at least should be) a fairly rare occurence (under 10/second) you can pretty safely attach profiler over a long time and capture only the Locks/Deadlock Graph event.
Does the rebuilding hurt the system stability or takes too much system time?
If you answer no - don't touch it :)
