Post reply

Manual or auto - growth of datafiles

  • January 28, 2009 at 5:54 am

    #125956

    Since little time ago I was used to configurate autogrowth for my databases' datafiles, and I did'nt modify anything until all space was filled.

    Then I read about the risk of fragmentation during autogrowth, and now I monitor more frequently the databases, and when a datafile is near to autoextend, I manually grow its initial size, instead of waiting for it to autogrow. Does this avoid from fragmentation, or has it the same effect of autogrowth?

    Thanks,

    sb

  • January 28, 2009 at 7:13 am

    #933778

    My understanding is that manually growing the files can also contribute to physical file fragmentation.

    Look to the link for more information.

    http://www.mssqltips.com/tip.asp?tip=1481

  • January 28, 2009 at 7:14 am

    #933780

    Hello there,

    There is always going to be some extent of fragmentation when your database/tables grow (nature of the beast). But to watch your system that close is a GREAT idea but i do believe i would put that energy to other areas(optimatizations,security,backup/restores,etc) versus manually doing this. The fragmentation is something i believe is mentioned simply because you cant do it without that happening at some level. Now the biggest part of this is making sure it grows i enough increments such that you are not autogrowing every few seconds and at the same time it doesnt get soo big on auto grow that it takes forever to refill the empty space. Hope i was able to give some insight.

    -D-

    DHeath

  • January 28, 2009 at 7:39 am

    #933814

    One advantage of growing the file manually is that we can do it when the DB is not soo busy. AutoFile if it kicks in when the server is really busy with transaction, it will affect the performance drastically.

    -Roy

  • January 28, 2009 at 9:47 am

    #933938

    Fragmentation will depend on the speed of the disk subsystem, availability of contiguous disk space and the workload. Whether a file grows automatically or if forced to grow manually, it can still fragment.

    It is better to grow the files manually out of production hours to avoid performance issues caused by filegrowth during high usage. Rather than spending significant effort monitoring growth I recommend you size your database for 6 - 18 months growth and expand the files accordingly in one step. If the files do fragment during this manual increase in file size, defrag the drives, again out of hours, . Make sure you have backups on another set of disks before you defrag, although I have never had issues. That way you have contiguous files which you not need to grow for the following year.

    One thing I have found is that defraging log files on 32 bit SQL installations does lead to significant IO improvements.

  • January 28, 2009 at 2:41 pm

    #934228

    ian McCann (1/28/2009)

    My understanding is that manually growing the files can also contribute to physical file fragmentation.

    Look to the link for more information.

    http://www.mssqltips.com/tip.asp?tip=1481

    Actually, manual and automatic growth will both contribute to physical file fragmentation. Both can be done correctly, or incorrectly.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 28, 2009 at 2:43 pm

    #934232

    aali_online (1/28/2009)

    Fragmentation will depend on the speed of the disk subsystem

    How do you figure that the speed of the disk has anything at all to do with disk fragmentation?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 28, 2009 at 2:47 pm

    #934235

    There's only one thing that ultimately affects fragmentation... that's growth of files. Whether you set it manually or automatically, it's file growth. The best thing to do is to plan ahead... make the database files big enough for a year's worth of additional storeage and check it every month. If you have no idea what a year's worth of growth will be after two months of database lifetime, then you might need to pay a bit more attention.

    Having an already fragmented disk is problem that you should take care of... but unless growth occurs, the disk will not become more fragmented.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 29, 2009 at 3:33 pm

    #935144

    How do you figure that the speed of the disk has anything at all to do with disk fragmentation?

    --Jeff Moden

    The disks have to cope with concurrent IO requests from multiple threads which in my experience contributes to fragmentation of files.

  • January 29, 2009 at 3:57 pm

    #935160

    Sorry Jeff, I did not check me previous reply:

    How do you figure that the speed of the disk has anything at all to do with disk fragmentation?

    --Jeff Moden

    If file growth is taking place at the same time as other IO requests, in my experience this can cause fragmentation.

  • January 29, 2009 at 6:43 pm

    #935217

    aali_online (1/29/2009)

    Sorry Jeff, I did not check me previous reply:

    How do you figure that the speed of the disk has anything at all to do with disk fragmentation?

    --Jeff Moden

    If file growth is taking place at the same time as other IO requests, in my experience this can cause fragmentation.

    Ok... I get it... you're saying that file growth takes less time on a faster disk and that other "things" can grab a part of the disk the file growth might...

    I'm not sure, but I believe that file growth identifies and reserves the disk sectors it's going to use almost instantaneously so IO speed shouldn't really have any impact on fragmentation of individual growth spurts. Of course, I could be wrong...

    Maybe Gail would know for sure... hope she see's this...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply