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?

Richard GouetteIT ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dustin SaundersDirector of OperationsCommented:
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.
Scott PletcherSenior DBACommented:
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).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Richard GouetteIT ManagerAuthor 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,
SolarWinds® VoIP and Network Quality Manager(VNQM)

WAN and VoIP monitoring tools that can help with troubleshooting via an intuitive web interface. Review quality of service data, including jitter, latency, packet loss, and MOS. Troubleshoot call performance and correlate call issues with WAN performance for Cisco and Avaya calls

Scott PletcherSenior DBACommented:
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 DBACommented:
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 SaundersDirector of OperationsCommented:
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 DBACommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.