SQL Server file growth and performance

Our SQL Server 2008 DB seems to slow down in the afternoon.   I am wondering if the auto grow settings within SQL are incorrect and if they could be adjusted to greatly improve our performance.  I believe it is either that or we just need faster hardware.  I pulled a query of growth events.  Here is a very small snippet with some of the slowest times:

Data File Auto Grow      DB      DB_CurrentVersion_Data      10/17/2017      29:43.5      29:43.8      246000
Data File Auto Grow      DB      DB_CurrentVersion_Data      11/9/2017      33:17.4      33:17.6      226000
Data File Auto Grow      DB      DB_CurrentVersion_Data      11/9/2017      33:22.0      33:22.3      220000

Data File Auto Grow      tempdb      tempdev      11/11/2017      35:52.9      35:53.1      206000

I also pulled the growth report from within mgmt studio, and see that in the afternoon there are thousands of growth events, 1 Mb each.  Sometimes 4 or 5 per second.  This is an OLTP database, but reports have to be pulled while it is running as well.  The size of the database (on disk) seems to increase rapidly as well.

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.

Vitor MontalvãoMSSQL Senior EngineerCommented:
1MB for the autogrow is very bad. This will make the file grow almost on every second and it will make a lot of disk IO. Change that value for example to 100MB or even more. Ideally you should set a value that will extend the data file for long without the need to grow in the next days or weeks.
Scott PletcherSenior DBACommented:
1) A fixed amount of growth is good.  But too low a fixed amount, such as 1MB, is bad.  Increase the size.  Typically 50MB is enough for most dbs.
Do not use % growth on any file, ever!  Use only fixed amounts.

2) Also critical: make sure that IFI (instant file initialization) is enabled for the instance. (Unless you have extremely high security requirements.)  If IFI is not enabled, SQL will have to pause and pre-format all db space,  That is a very slow operation.

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
s_sykesAuthor Commented:
I looked and sql is running under Local System.  This should have the authority to make IFI work correct?

If I make it 100 MB, would it come to a screeching halt when it did grow?  The log file is currently set to 10%.
Make Network Traffic Fast and Furious with SD-WAN

Software-defined WAN (SD-WAN) is a technology that determines the most effective way to route traffic to and from datacenter sites. Register for the webinar today to learn how your business can benefit from SD-WAN!

Scott PletcherSenior DBACommented:
The log file is currently set to 10%.
Change that!  Depending on your drive speed, somewhere between 10MB and 30MB should be OK.  Never use % growth: it's slower and it can cause huge growth when the files get large.  I know that 10% is MS's default, but they finally do correct this in more recent version(s) of SQL Server, and they have now quit using % growth, as others have.

sql is running under Local System.  This should have the authority to make IFI work correct?
Perhaps.  I'm not a Windows/security person, and I never run SQL under a local system account.  Just make sure that the account running SQL has authority to "Perform Volume Maintenance Tasks".
Vitor MontalvãoMSSQL Senior EngineerCommented:
Be aware with the virtual log files (VLFs) when working with transaction log files. It has a big impact in the performance. My recommendation is to use a value that's multiple of 64. You can start with 64MB for the autogrow value. Depending on the size of your database you might need to increase this value and if that's true then go with 128MB, 256MB and so on until you find the right value. Mind that you'll need to shrink the transaction log to 1MB for better results as this will remove all existing VLFs and will start from zero.
Scott PletcherSenior DBACommented:
FYI, be aware that SQL always has to pre-format all log space.  That takes time, of course.  Therefore, be very careful to limit the size of log growth to an amount that your disk subsystem can handle.
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.