February 27, 2015 at 2:20 am
Just a quickie.
When my db approaches current capacity, is there any benefit to adding an extra data file /s as opposed to allowing it to autogrow?
For the sake of keeping it simple, if I allowed autogrow by 10 GB or added an extra 10 GB file.
Many thanks.
February 27, 2015 at 2:41 am
No benefit, unless the additional file resides on a different (physical) disk and the sizes of the data files in the same filegroup are identical.
You should grow the files manually before available space becomes a concern and use autogrow for unplanned growth only.
Autogrow by 10GB is too big. I would use smaller autogrow amounts, such as 100 MB. Also, make sure you enable instant file initialization, or SQL Server will have to wait for the allocation of the space in the file system (especially for big autogrow amounts)
-- Gianluca Sartori
February 27, 2015 at 6:05 am
My preference would be to have pre-grown data files. I can control when it grows and how much it grows. This matters because auto-grow will cause blocking while the growth occurs. This can be an issue depending on your system and it's load.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply