Of course I can just drop and recreate or disable and rebuild it.
But for large tables it can take time, and for critical tables it can cause issues.
Is there any "more sophisticated" way to tell sql to keep index data(+ handle updates\inserts) without using it?
So if needed it can be "enabled" immediately?

  • I don't get it. You want to maintain the index (having all the costs involved) but not to use it (discard the benefits it could provide)? – Ronaldo Apr 21 at 13:21
  • Can you include a sample execution plan that shows how and why the index isn't doing any good? Often rewriting the query or rethinking the indexing strategy would be better a solution. – vonPryz Apr 21 at 13:42
  • Exactly! The benefit will be that after a while I could drop this index without any concerns of what it could break.. The issue is not some bad execution plan, but indexes, some of them almost identical, but we afraid to drop them since there were issues in the past and downtime is not an option. – Michael Cherevko Apr 21 at 14:10
  • Assuming you have a CI, can't you just use an index hint on SELECTs to use the CI? – Queue Mann Apr 21 at 16:49

Other than using query hints to force different indexes, or using Query Store to force plans that don't use the index, no. There's no way to "hide" an enabled index from the query optimizer.

  • Tnx, thought so, but thought that maybe there is some advanced feature in newer versions. Do you have any idea if something like this is even possible to develop? This could help dropping indexes in the safest way possible – Michael Cherevko Apr 21 at 14:18
  • I believe it would be possible to develop such a feature. You can suggest and vote for it here: aka.ms/sqlfeedback – David Browne - Microsoft Apr 21 at 14:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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