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.
And, sorry... I have to say that routine shrinking of files or databases is a total waste of time.
Doing it for reasons of recovering space after a "rampant growth accident" might also the wrong thing
to do if you don't do it correctly and, it looks like you might be doing it incorrectly. If you pack everything down and then rebuild your indexes, you will have wasted space equal to about 120% of your largest pagecount index and that last shrink with TRUNCATE ONLY won't take it (unwanted free space) out because your index rebuilds build a new copy of the index at the logical end of the file (after a shrink) because there's no room at the beginning of the shrunk file. If the goal really is to recover disk space, then you need to create a new temporary file group, rebuild your largest index(es) to that (moving the index using the DROP EXISTING option), do your first shrink, do all your other rebuilds not once but twice to avoid the end-of-file problem, and then rebuild the largest index back to that original file group and drop the temporary file group. Again, that's just a suggestion at this point... I'm not done experimenting with all this and it could be several months (if at all) before I have a demonstrable example.
is pronounced ree-bar and is a Modenism for R
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair
When you put the right degree of spin on it, the number 318
is also a glyph that describes the nature of a DBAs job. Helpful Links:
How to post code problemsHow to post performance problemsForum FAQs