Does performance of a database (SQL Server 2005) decrease if I shrink it?

Question

Does performance of a database (SQL Server 2005) decrease if I shrink it?

What exactly happen to the mdf and ldf files when shrink is applied (Internals???)

Answer 1

Yes it could affect performance a bit. When a database is in operation it doesn't care to much about its diskspace usage, more about efficient data retrieval/persistance.

Answer 2

When shrinking a database it will consume resources to shrink the DB. Where it runs into issues is when the DB needs to grow again, and assuming you have auto grow set, it will consume more resources to auto grow. Constant auto shrink (or shrink as part of maintenance plan) will cause physical disk fragmentation.

If you have auto grow enabled and it is set to the default of 1MB then constant auto grows will consume a lot of resources.

It is best practice to size your database to a size that is suitable, expected initial size plus expected growth over a period (month, year, whatever period you see fit). You should not use auto shrink or use shrink as part of a maintenance program.

You should also set your auto grow to MB (not a % of the database as when auto growing it needs to calculate the % first, then grow the database). You should also set the auto grow to a reasonable amount to ensure that it isnt going to be growing every 10 mins, try and aim for 1 or two growths a day.

You should also look at setting Instant Initialisation for your SQL Server.

Good luck,

Matt