SQL Server misunderstanding 30th about the 9th Day database file shrinkage does not affect performance _mssql

Myth #9: Database file shrinkage does not affect performance

Error!

Shrinking a database file does not affect performance only if there is space left at the end of the file, and the TRUNCATEONLY option is specified in the shrink file.

The process of shrinking a file is very impacting performance, which involves moving a large amount of data to create a lot of Io, which is logged to the log, causing the log to skyrocket and, correspondingly, a large amount of CPU resources.

Not only does it affect performance in the process of contraction, and the same effect should be done after the file is shrunk, the large number of logs generated by the contraction are transferred by transaction log, mirrored, and duplicated. When the space is not enough, the file also needs to fill in 0 initialization to affect performance (unless you open the option of not filling out the 0 initialization).

That's not the worst, but the worst result is that file shrinkage causes a lot of index fragmentation, which affects performance for scan operations.

Unfortunately, the code to shrink the database was not written by me (if I had written it, I would not have allowed it in the first case) so the only thing we could do was to accept the operation.

If you want to find a way to shrink your database files, see this blog post: Why you should not shrink your data files, or from the outset you plan your files: Importance of data File size Managemen T. And this article: TGIF time Warp.

Children, remember this:

• Data file contraction is evil.
• Shrinking the database is more sinister
• Automatic contraction that's a heinous thing.

Simply saying no to the contraction will keep us away from the troubles we have caused.