asked on

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.

Thanks!

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

s_sykes

ASKER

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%.

Scott Pletcher

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ão

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 Pletcher

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.