Can shrink database improve performance

Question

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?

Answer 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.