January 24, 2013 at 9:52 am
I have read autogrowth could increase physical NTFS fragmentation of the underlying database files.
My question is if we increase the size manually, will that also create fragmentation?
Or this doesn't matter, what did create the fragmentation is the frequency of autogrowth and the time..
January 24, 2013 at 10:36 am
It is best to size you files so that autogrowth does not occur and manually extend your files in response to data growth.
As you said this gets you away from the file fragmentation that a frequent file growth may cause.
Also, every time autogrowth occurs, writes may pause for some N milliseconds, while the file is being extended.
January 24, 2013 at 12:30 pm
It will create fragmentation as well but the severity can be greatly reduced if your autogrow settings are sensibly set. The serious problems with physical fragmentation arrive when you have many databases on an instance, all have autogrow settings where the database files grow in very small increments (e.g. 1MB) and many database files autogrow regularly. You can imagine how the various autogrow operations cause the physical database files to become interleaved, 1 MB at a time.
The point made earlier about the client delay when a file has to autogrow is relevant especially for log files where instant initialization is not available and the autogrow setting is non-trivial, e.g. 500 MB, or when instant initialization is not enabled for data files and again the autogrow setting is non-trivial.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 24, 2013 at 1:36 pm
Thanks, that helps a lot
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply