3

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?

2
  • If I understand correctly, your vendor is recommending DBCC SHRINKDATABASE as the solution to performance problems with their database? Maybe they can tell you why this worked for some of their other clients. My guess it was accompanied by other measures, like index rebuild/reorg to address the fragmentation,
    – Dan Guzman
    Mar 26, 2021 at 11:15
  • @DanGuzman: yep you understand correctly. In the procedure I got they don't talk about index rebuild/reorg. That's why I tought there were some scenarios where shrinking the database helps performance. Mar 26, 2021 at 12:04

1 Answer 1

9

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.