RE: Performance improvement following a disable indexes, shrink, enable indexes - but why?

Ten Centuries

Points: 1289

February 11, 2018 at 9:46 pm

#1979379

Jeff Moden - Sunday, February 11, 2018 9:25 PM
Performance of what "improves significantly"?
It could be from things like the fact that disabling an index also disables any FK that may be based on those indexes. Of course, that won't affect SELECTs.... just INSERTs, DELETEs, and some UPDATEs. Any FKs of that nature must be rebuilt using ALTER TABLE CHECK CONSTRAINT to become active again.
The other thing is, how are you doing your normal index maintenance? If it's a mature database and it normally does most REORGANIZE instead of REBUILD, that could be it. I'm working on a project that may prove that but I'm not done yet so I can only say that as a suggestion rather than a demonstrable fact.

Jeff
A reasonable ask. It's a Data Warehouse database. We have traced a series of queries (select only) that we execute in sequence. Before we 'compact' routines take 40+minutes, after that take 18 minutes. We flush all the caches etc and perform cold and warm tests. There are no RI constraints (not that there are any inserts or updates).

The reorganize only removes leaf level fragmentation, leaving non leaf level fragmentation, where as the rebuild removes both. That could be the answer. Is there a way of determining fragmentation at either a) non leaf levels or b) a specific level? Time to brush up on sys.dm_db_index_physical_stats by the looks of things.

pcd