Should I change my SQL initial size to ward off frequent growth events?

Richard Gouette
Richard Gouette used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dustin SaundersCo-Founder and Chief Architect
Top Expert 2016

Commented:
If you have expected growth, you can presize your database.  Ideally, you do this as the autogrowth in all likelyhood isn't going to find contiguous regions to expand to and this will cause phyiscal disk fragmentation.

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.
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
Yes, that's a serious problem.

10% is way too large.  Never use % for growth, always a fixed amount.  I've found % to often result in much slower growth even when the file is not already very large.

13GB is way too large as well.  1GB should be plenty (and the most I'd ever use).

Make certain IFI is enabled for the instance (unless you have very high security requirements for old data).  To enable IFI, make sure that the SQL service account has "Perform volume maintenance tasks" permissions (doing that from memory, but it should be very close at least).
Richard GouetteIT Manager

Author

Commented:
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
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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.
Dustin SaundersCo-Founder and Chief Architect
Top Expert 2016

Commented:
If you're using VMWare, are the disks Thick Eager?  You will incurr an additional write penalty on Lazy disks during first write.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial