CODEWITHSUNDEEP
sql-serversql-server-2008
Dustin Davis
Dustin Davis

Reputation: 14585

SQL disable/reenable indexes

I am doing some index evaluations and I wanted to disable an index from being used. I unchecked the 'Use index' option in the index properties but when I checked it again to enable the index usage, it seems like it's rebuilding the index. These are large indexes and some take 3 hours to rebuild.

Is there a way to quickly disable/enable indexes in SQL 2008?

Upvotes: 2

Views: 879

Answers (2)

Martin Smith
Martin Smith

Reputation: 453887

The Database Tuning Advisor has the ability to add hypothetical indexes and hypothesise about the effect of removing indexes but I don't think that functionality is exposed in any way that we can make use of ourselves.

You could perhaps create a statistics only copy of the database if you just want to play around with various such scenarios (I haven't tried this myself)

Upvotes: 0

slkull
slkull

Reputation: 39

Once you re-enable an index the index rebuilds because any updates that occurred while disabled make the index obsolete, and ineffective.

Upvotes: 4

Related Questions