SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Autogrow for a Datafile could impact Performance
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

alejo46
Posting Yak Master

Colombia
116 Posts

Posted - 01/14/2014 :  21:09:56  Show Profile  Reply with Quote
Good evenining
I need your help,
There is a process that inserts about 50 millions of rows to an empty table and the average for this process takes about 5 hours to finish daily

Today it took almost 14 hours to finish and it was the only running process

i tried to find out what caused this overload and in sql servers logs found this several times:

Autogrow of file 'dm1_elite_Log21' in database 'dm1_elite' took 76830 milliseconds. Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file.

i Most of Datafiles have autogrow feature set to 0, but datafile dm1_elite_Log21 was set to 512000 KB

here it is:
dm1_elite_Log17 I:\MSSQL\log\dm1_elite_Log17.ldf NULL 655304 KB Unlimited 0 KB
dm1_elite_Log18 Q:\MSSQL\log\dm1_elite_Log18.ldf NULL 655304 KB Unlimited 0 KB


1. this Datafile autogrow set to 512000 KB could impact the performance ?
2 In real life is better autogrow all datafiles to 0 ? or what considerations shoul be taken to set autogrow to 0 or another value ?

I appreciate your help in adcvanced

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/15/2014 :  04:37:04  Show Profile  Reply with Quote
quote:
Originally posted by alejo46

Autogrow of file 'dm1_elite_Log21' in database 'dm1_elite' took 76830 milliseconds.


That's only 1 minute ... not sure its relevant in your 5-hours to 14-hours time increase.

Set your AutoGrow to a FIXED size (not percentage) and a size large enough that it only happens once every month or two (frequent extensions will fragment the file more than fewer, larger, extensions)

If this 5-hours to 14-hour increase was a one-off I expect that something was blocking the import, causing it to slow down - perhaps Update Stats was firing, or the import occurred at the same time as some other scheduled task (whereas normally the two tasks would happen at different times)
Go to Top of Page

alejo46
Posting Yak Master

Colombia
116 Posts

Posted - 01/16/2014 :  17:01:12  Show Profile  Reply with Quote
Autogrow set to the log file dm1_elite_Log19 is set to 512000 KB, is this a good value ?
FG SIZE MAXSIZE GROWTH USAGE
dm1_elite_Log19 U:\MSSQL\dm1_elite_Log18.ldf    NULL 36992000 KB Unlimited 512000 KB log only

secondly, what do you mean when you say: and a size large enough that it only happens once every month or two (frequent extensions will fragment the file more than fewer, larger, extensions)

Thanks in advanced

Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/20/2014 :  07:48:52  Show Profile  Reply with Quote
Work out how much the data file grows in two months, set the extension to that size.

If the growth in two months is less than 50MB then I would set to 50MB (i.e. smallest extension value I would use is 50MB)

512MB is a good value for your Log file provided that it doesn't ever grow that much, and the maximum it grows is somewhere near that amount.

If the database is in Full Recover Mode look at the size of the Log Backups and monitor the largest one over a 2 month period. Twice that size would be a good value to start with.

Once you have set that value then monitor if it grows bigger. If it grows then investigate why. If it was one-off event (data import for example) then shrink back to your "normal size" value. If there is a new requirement, and a bigger size is required, then again examine the size of log backups over a period of time and set the size of the Log File with some "safety space" included. If that size then changes in the future repeat this step.

Edited by - Kristen on 01/20/2014 08:06:14
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000