Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
alejo46
Posting Yak Master
157 Posts |
 Posted - 2014-01-14 : 21:09:56
|
| Good eveniningI 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 dailyToday it took almost 14 hours to finish and it was the only running processi 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 KBhere it is:dm1_elite_Log17 I:\MSSQL\log\dm1_elite_Log17.ldf NULL 655304 KB Unlimited 0 KBdm1_elite_Log18 Q:\MSSQL\log\dm1_elite_Log18.ldf NULL 655304 KB Unlimited 0 KB1. 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
22859 Posts |
Posted - 2014-01-15 : 04:37:04
|
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) |
 |
|
|
alejo46
Posting Yak Master
157 Posts |
Posted - 2014-01-16 : 17:01:12
|
| Autogrow set to the log file dm1_elite_Log19 is set to 512000 KB, is this a good value ?FG SIZE MAXSIZE GROWTH USAGEdm1_elite_Log19 U:\MSSQL\dm1_elite_Log18.ldf NULL 36992000 KB Unlimited 512000 KB log onlysecondly, 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 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2014-01-20 : 07:48:52
|
| 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. |
|
|
|
|
|
|
|
|
Autogrow for a Datafile could impact Performance