Post reply

filegrowth

  • November 18, 2005 at 7:23 am

    #92299

    I was reading my book on SQL Server and got halted at the following statement:

    Because

    FILEGROWTH can be defined as small as 64KB, automatically increasing the file size can be detrimental to performance if it happens too frequently.

    Is this because of fragmentation occurring or is there an overhead in allocating disk space. If so, what kind of overhead is it? Please clarify.

    Thank you.

    Karim

  • November 18, 2005 at 7:58 am

    #604636

    It's mostly because of the fragmentation of the database file.  The files are not in consecutive sectors in the hard drives. It causes the hard drives to take more time to move disk heads to read data.

    Frequently increasing a file is certainly bad. It's more efficient for the OS to allocate (say 64MB) once than allocate 64KB 1000 times.

    For a busy OLTP system, it's better to allocate enough space when the database is created. And set the auto growth to a fixed number of MBs, e.g. 200MB

     

  • November 18, 2005 at 8:16 am

    #604650

    Thanks peterhe. But you did not explain what the nature of the overhead in allocating additional disk space is. Why is there overhead? What is involved in allocating additional space?

Viewing 3 posts - 1 through 2 (of 2 total)

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