I have read Perform Index Operations Online provided by MSFT but I couldn't find any detail information for my questions.

My questions are:

  • What is the impact when I update (create/drop) non-clustered index in the live database?
  • If the database cannot be shut down, is there any specific way to reduce the risk and impact?

closed as too broad by hot2use, sp_BlitzErik, mustaccio, LowlyDBA, Mr.Brownstone Dec 7 at 22:06

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Welcome to DBA.SE and thanks for participating. Your question isn't really clear, as update is not the same as create/drop. A REBUILD could be viewed as a drop/create and a REORGANIZE would be just shuffling the index leaf pages around on disk. Could you clarify your question a bit please? – hot2use Dec 6 at 10:57
  • I have added the mentioned page link to your post. I hope it is the correct link? Otherwise please change it. – hot2use Dec 6 at 11:02
up vote 3 down vote accepted

I mean But during index creation/updating period, will they cause data corruption because old index cannot be found

None of the documented T-SQL commands can cause data corruption.

The difference between online and offline index operations is in the fact of index's availability: while you do them online, indexes are still available, and when offline, they are not.

This means that your queries just cannot use nonclustered index for a period of its rebuild offline, and in case of clustered index rebuild the whole table is not available when doing it offline.

Online index operations come at a cost: they are slower, they are fully logged (while offline rebuild can be minimally logged if your db's recovery model is simple/bulk logged), and online index rebuild can result in long time blocking under some conditions described here: A SQL Server DBA myth a day: (8/30) unicorns, rainbows, and online index operations by Paul randal

Why are you dropping and recreating the indexes? You should be reorganizing or rebuilding them. Online if you have Enterprise and want to.

You can disable the index during ETL operations if you need to as well, just rebuild afterwards.

Impact of your approach: - Schema locks while index is being built. - Potential blocking while index is being built.
- Larger than necessary I/O - Any queries that use index hints will break while the index is
dropped.

  • But after indexes creation and deletion complete, all queries should run with new reference right? – NewPy Dec 6 at 6:00
  • Yes, after it's created, the engine should start using the index if it's appropriate for the query. – Jonathan Fite Dec 6 at 6:01
  • But during trigger creation/updating period, will they cause data corruption because old index cannot be found or basically just introduce delay which is not a very serve issue. – NewPy Dec 6 at 6:20
  • triggers are a different animal. they require an X lock on the table (during creation) so have to wait for writes to quell. And depending on what the trigger is doing, then you may have some data oddities. with indexes, usually the worst you get is bad performance on reads if they aren't there. – Jonathan Fite Dec 6 at 6:24
  • Sorry for the typo. I mean But during tindex creation/updating period, will they cause data corruption because old index cannot be found or basically just introduce delay which is not a very serve issue. – NewPy Dec 6 at 6:28

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