WhiteLotus (8/3/2014)
Jeff Moden (8/1/2014)
You could disable them which drops all of the data from the index but keeps the meta data and see if peformance suffers. Of course, you need to do a test on whatever is using the indexes before you do that. If performance does suffer, rebuild the index and it will reform, data and all.Of course, there are the normal warnings about doing this on high usage or large tables during business hours and that such action could cause some substantial logfile growth like any index reorg (no matter the recovery model) or index rebuild (in the FULL recovery model) can cause.
Thanks for your response ..It is a good idea .. I could disable 1 index that has zero index seek but I highly hesitate to disable the others that has high number of index seek ...
I still don't know what to do with the indexes that have high number of index seek....
Apologies... I was relying on the flow of this thread to avoid having to include specific context...
Of course you wouldn't disable indexes with a large number of index seeks. You would only do such a thing on indexes that had little or no seeks compared to updates. Scans on indexes would definitely be a judgement call after some further investigation to see if the index could/should (not all should necessarily be) be modified to use seeks.
You would also avoid disabling UNIQUE indexes whether they have seeks or scans against them or not.
--Jeff Moden
Change is inevitable... Change for the better is not.