alen teplitsky wrote:
Has anyone done this on servers with numerous applications accessing them? How do you handle applications slowing down due to the loss of indexes? And how does Always On react to rebuilding several large indexes at once? Long ago I used to use maintenance plans and quit because rebuilding every index of a large table created a storm of AlwaysOn replication that also screwed up replication.
I know it's an older post but here goes.
Yes. I've done this with multiple applications that access common tables.
We have the luxury of a "quiet time". Disabling and then rebuilding indexes makes the underlying tables unavailable during the rebuild unless the ONLINE option is used. Dropping them and recreating them doesn't appear to have the same problem. Note that's ONLY for non-clustered indexes! Disabling a clustered index will make the table unavailable until it's rebuilt. Dropping a clustered index will definitely put you in a world of hurt in more ways than one not to mention causing a rebuild of all non-clustered indexes as a result of the drop and then again we you rebuild the clustered index. This is because either the CI Key or the RID (for Heaps) is an integral part of ALL non-clustered indexes. If you don't have a "quiet time", then don't do the rebuilds. I also strongly recommend that you NOT use REORGANIZE except to compress LOBs because REORGANIZE compresses an index and is not capable of creating any free space above the Fill Factor. It can and usually does cause an index to do massive page splits on the morning after just as if you had rebuilt it with a 100% Fill Factor if the reason for the Fill Factor was to help alleviate the pages splits... which is almost always the only reason to have other than the default Fill Factor of "0", which is identical to a 100% Fill Factor in all cases unless one was foolish enough to change the default Fill Factor for the system.
If you don't have such a quiet time to rebuild large indexes, then you're going to have to figure something else out.
Even with the mods they made, you shouldn't use a built in maintenance plan for index maintenance especially but not limited to if you still think using REORGANIZE is generally a good idea. It's not and here's a link to a 'tube that proves it.
If you have "Always On" or anything else that requires data from the transaction log file, that seriously changes the game in a whole lot of ways but I have to tell you that it's STILL NOT A REASONABLE JUSTIFICATION FOR USING REORGANIZE because it perpetuates page splits and fragmentation and takes a whole lot more log file usage than what has been advertised. I did a whole lot of testing. One of the tests was to restore a database and then either rebuild or reorganize a 147GB Clustered Index that was only 12% fragmented. The rebuild did as expected and used a little more than 147GB of log file. Reorganize caused the log file to explode to more than a QUARTER TERA-BYTE!
Going back to what has already been stated, there are a whole lot of great ideas in the article but, like any other article, "IT DEPENDS" comes into play and it's almost impossible to cover everywhere it depends in a single article. Instead, you must use the ancient DBA art of "Must Look Eye" and figure out the dependencies.