January 29, 2013 at 12:02 pm
Hello, not sure on what best practice is.
SQL 2008 STD
The database is set to auto grow the data file by 4096 MB.
The data file is almost full so will grow soon on a fairly high transaction system.
If I leave it to auto grow it may grow at any time and assuming their will be overhead involved in an auto grow performance may be affected?
Should the DBA monitor this free file space in the data file(s) in the database(s) and then before the auto grow happens manually increase the data file size during a down period?
Or is the overhead in increasing the data file size by auto grow so small that their is not an issue?
Is their a "rule of thumb" for how large the auto grow size should be relative to the database size.
I have my log file auto grow size fairly large (to minimise VLF growth if I understand the articles correctly) and I am unsure if the data files size should be similar or not?
thanks
January 29, 2013 at 1:44 pm
UncleBoris (1/29/2013)
Hello, not sure on what best practice is.SQL 2008 STD
The database is set to auto grow the data file by 4096 MB.
The data file is almost full so will grow soon on a fairly high transaction system.
If I leave it to auto grow it may grow at any time and assuming their will be overhead involved in an auto grow performance may be affected?
For data files, if instant initialization is enabled then it should not take much time at all. If instant initialization is not enabled (the default) then growing a data file by 4GB will likely take a noticeable amount of time for the lucky transaction that happens to force the file to grow.
How and Why to Enable Instant File Initialization
Should the DBA monitor this free file space in the data file(s) in the database(s) and then before the auto grow happens manually increase the data file size during a down period?
Ideally, yes, but with instant file initialization for data files it becomes less of a concern. Excessive physical fragmentation can be caused by many data files randomly growing in small increments. For log files the time to autogrow becomes a much bigger concern because instant file initialization is not available.
Misconceptions around instant file initialization[/url]
Is their a "rule of thumb" for how large the auto grow size should be relative to the database size.
It will depend on how fast your disk system is and how long you want to delay a transaction with an autogrow operation. There is bug in 2008 out there related to autogrowing log files when specifically using 4GB as the amount that may be of interest:
Bug: log file growth broken for multiples of 4GB[/url]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 29, 2013 at 5:37 pm
thanks for the info. I grew a database on the prod server by 4096MB and it took a couple of seconds and looking at the "Performance Volume Maintenance Task" it looks to be enabled.
I did a test on a dev server (slightly lower spec'd) and created a 4096 blank database and then grew it to 6000MB.
I did this with File Initialisation ENABLED then DISABLED.
Enabled:
Create DB: 1m 25s
Grow DB: 2s
Disabled:
Create DB: 4m 06s
GrowDB: 1m 15s
I did this a few times with the same results.
Too be honest I was a little surprised at the difference in results.
January 29, 2013 at 5:52 pm
UncleBoris (1/29/2013)
thanks for the info. I grew a database on the prod server by 4096MB and it took a couple of seconds and looking at the "Performance Volume Maintenance Task" it looks to be enabled.
2s sounds reasonable for a 4GB data file autogrow, maybe a little high honestly but I guess it could have been 1501 ms rounded up. If you're OK with any one transaction incurring a 2s hit when running a query then you're good to go, else you may want to lower that. On an OLTP system I typically will autogrow data files in much smaller increments, like 512MB.
I did a test on a dev server (slightly lower spec'd) and created a 4096 blank database and then grew it to 6000MB.
I did this with File Initialisation ENABLED then DISABLED.
Enabled:
Create DB: 1m 25s
Grow DB: 2s
Disabled:
Create DB: 4m 06s
GrowDB: 1m 15s
I did this a few times with the same results.
Too be honest I was a little surprised at the difference in results.
The 1m 25s could have been mostly attributed to the log file. How big was it in this newly created database?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply