(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.)
Myth #9: Data file shrink does not affect performance.
<wipes tears from eyes, attempts to focus on laptop screen, cleans drool from keyboard>
The only time a data file shrink won’t affect performance is if you use the WITH TRUNCATEONLY option and there’s free space at the end of file being shrunk.
Shrink affects performance while it’s running. It’s moving tons of data around, generating I/Os, fully logging everything it does, and burning CPU.
Shrink affects performance after it’s run. All that log has to be backed up, log shipped, database mirrored, scanned by transactional replication, and so on. And if the data file grows again, the new space has to be zeroed out again (unless you have instant file initialization enabled).
Worst of all, shrink causes massive index fragmentation – which sucks down performance of range scans.
Unfortunately there was never time for me to rewrite the shrink code (I didn’t write it like that in the first place) so we’re probably stuck with its massive suckiness (that’s a technical term) forever.
Check out this blog post where I go into more details and explain an alternative to using shrink: Why you should not shrink your data files and this one for how to manage your data files correctly: Importance of data file size management. And this one just because it’s cool (safe for work): TGIF Time Warp.
- Data file shrink is evil
- Shrinkdatabase is evil-er
- Auto-shrink is evil-est
Shrink – just say no. With proper education and effective counselling, we can rid the world of this terrible affliction once and for all.