Does "index disabling" blocks a table? Do we have any constraints regarding it?
No... disabling the index doesn't substantially block the table except for the time it takes to disable the index, which is quite short for most places where I've seen it done.
Yes, there are some possibly serious constraints when it comes to either dropping or disabling an index.
First, you should never drop a Clustered Index. It will cause a table to revert to a heap and any Non-Clustered Indexes will automatically be rebuilt whether you want them to be or not. Rebuilding the Clustered Index from a heap will cause the same issue. The Clustered Index is frequently the target of any FKs, as well.
Speaking of FKs, you probably shouldn't drop or disable any Non-Clustered index that is unique unless you've checked to see if any FKs are pointing to it and have made arrangements to handle that interaction. It may mean rebuilding FKs if you go that route.
When you REBUILD any index, it will paralyze the underlying table during the REBUILD unless you use the WITH (ONLINE = ON) option, which has it's own set of caveats including but not limited to some seriously massive log file entries and not doing the rebuild quite as tightly as doing an OFFLINE rebuild, which is also quite a bit faster (~350ms for a half-Gig index on my laptop for OFFLINE, almost 9 seconds for ONLINE). It may be worth it, though. "It Depends". One good tradeoff is to do OFFLINE rebuilds for such a small index, wait a couple of seconds, and then do the next one if in a 24/7 environment but you do need to check the effect to make sure it's not going to kill your online customers especially if the table has a high hit ratio.
For me, REORGANIZE simply isn't an option because, although it follows the Fill Factor, it's not capable of creating new pages to clear the area above the Fill Factor, which can be essential to preventing page-splits that cause blocking and the fragmentation caused by the page splits.
A vivid example for the problems associated with REORGANIZE can be found in Random GUID keyed indexes. Almost everyone regards them as a major fragmentation problem when, in reality, they're they the epitome of how most people envision how an index should actually behave. The use of REORGANIZE on such indexes is the actual cause of the fragmentation because it packs partially full pages to the Fill Factor and does nothing to clear the area above the Fill Factor. If you only use REBUILDs (whether online of offline) you can go literally for weeks and even months with <1% fragmentation because page-splits are seriously minimized.
Of course, all bets are off for fragmented indexes that you rebuild that have the default Fill Factor of "0" or indexes that have a super high Fill Factor because they will also not have any place (or very little of it) to absorb mid-index inserts and "ExpAnsive Updates". It's actually better to not bother doing index maintenance on such indexes than it is to do index maintenance wrong. Of course, that can be the wrong thing to do, as well.
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)