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?
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.
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.
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%.
WEBINAR: 10 Easy Ways to Lose a Password

Join us on June 27th at 8 am PDT to learn about the methods that hackers use to lift real, working credentials from even the most security-savvy employees. We'll cover the importance of multi-factor authentication and how these solutions can better protect your business!

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.