Should I change my SQL initial size to ward off frequent growth events?
I'm wondering if it's prudent to adjust the Database "Initial Size" value for my environment?
SQL version: 2014
Recovery model: Full
Database Size:" 158GB
I recent ran a utility which indicated to me that: "89 growths took more than 15 seconds each. Consider setting file autogrowth to a smaller increment."
Autogrowth / Maxsize setting appears to be default of: 10% unlimited, and we DO see unexplained performance issues on this database.
Does it make sense to calculate an anticipated DB size,(for say, 1-3 years down the road?) reset Initial Size to that size, and keep the autogrowth setting as-is?
I've also seen on Microsoft's site that the growth % should be about 1/8" the size of the database...Should I change autogrowth from 10% to around 13GB?
Thoughts?
SQL version: 2014
Recovery model: Full
Database Size:" 158GB
I recent ran a utility which indicated to me that: "89 growths took more than 15 seconds each. Consider setting file autogrowth to a smaller increment."
Autogrowth / Maxsize setting appears to be default of: 10% unlimited, and we DO see unexplained performance issues on this database.
Does it make sense to calculate an anticipated DB size,(for say, 1-3 years down the road?) reset Initial Size to that size, and keep the autogrowth setting as-is?
I've also seen on Microsoft's site that the growth % should be about 1/8" the size of the database...Should I change autogrowth from 10% to around 13GB?
Thoughts?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
we are indeed a VMWare shop...
I assume if I change the Initial Size from say, 158GB to 250GB, then I cause my disk to gobble up an
additional 92GB , yes?
And this change can be done at anytime, with no impact upon users ?
Thanks much,
Rich
I assume if I change the Initial Size from say, 158GB to 250GB, then I cause my disk to gobble up an
additional 92GB , yes?
And this change can be done at anytime, with no impact upon users ?
Thanks much,
Rich
You'll need to modify growth file by file.
If you'd like, I can provide a script to generate the growth changes (and apply them if/when you want the code to exec).
Log file space must be pre-formatted, even when IFI is on, so you should limit log growth to a smaller amount, and one that your disk system can handle in a reasonable period of time (50ms?! say). Log growth pauses other activity in the db, so it is critical that it complete quickly.
If you'd like, I can provide a script to generate the growth changes (and apply them if/when you want the code to exec).
Log file space must be pre-formatted, even when IFI is on, so you should limit log growth to a smaller amount, and one that your disk system can handle in a reasonable period of time (50ms?! say). Log growth pauses other activity in the db, so it is critical that it complete quickly.
With IFI on, data autogrowth is not that big a deal. In my company's situation, it was cheaper to reduce pre-allocated space and just let the db grow if (and only if) it needs to.
If you're using VMWare, are the disks Thick Eager? You will incurr an additional write penalty on Lazy disks during first write.
Log space should be pre-allocated. Log autogrowth should be allowed, but it should extremely rare.
If a log has grown substantially over time, you should check the number of VLFs. If it's excessive (more than 100, say), then you'd want to shrink the log and reallocate in larger chunks. That can be tricky if the db is not in SIMPLE recovery model.
If a log has grown substantially over time, you should check the number of VLFs. If it's excessive (more than 100, say), then you'd want to shrink the log and reallocate in larger chunks. That can be tricky if the db is not in SIMPLE recovery model.
Same deal with your index sizing, if you're sizing too low you're going to get fragmentation much quicker. This is why its not recommended to shrink databases.
From my experience, unexplained performance issues are generally the result of poor index and statistics health. The more you can do to prevent that the better. If you have the space to allocate up front for longer term growth then I would do so. One of the consequences of virtualization (if you are virtualized) is that there is a tendency to size for things as they are and expand the disk as needed which doesn't always make this possible.