Can shrink database improve performance

All we need is an easy explanation of the problem, so here it is.

Yesterday I’ve got a mail from one of our suppliers mentioning that there were performance issues with their application.

I don’t know yet what the issues are but the solution they propose is to shrink the database (because there is a lot of free space). By my knowledge this can not result in a performance improvement but apparenty they had success with this method when applying at other clients.

The way I think the shrinking happens is by moving pages from the back to the front of the file and like so compacting the database and causing massive fragmentation.

Is it possible that range scans take advantage of compacting the database?
Or are there some other edge cases that can benefit from shrinking a database?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

The strong general advice is to never shrink of course.

Still, the question here is: Can shrinking ever improve performance?

Well, maybe. Consolidating all the used pages at the start of each file might have benefits for ramp-up reads, and have favourable cache effects at multiple levels, including at the storage layer. In addition, shrink will remove any empty pages from heap tables rather than move them, so that might help heap table scan performance directly.

Shrink also checks if a b-tree or heap page should be recompressed, which might be relevant if data compression is in use. Shrink also never generates forwarding pointers, so shrinking heap pages can result in "unforwarding" and stub removal.

It would still be better to take more effective action to address whatever the specific cause of poor performance is. That requires understanding the issue and knowing how to fix it, rather than (blindly) trying things that appeared to work before.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply