Post reply

How SQL server fills empty spaces in data files ?

  • October 1, 2015 at 2:48 am

    #305113

    Hi All,

    I understand that we shouldn't shrink data files as it might cause heavy fragmentation along with log usage, high IO/CPU etc.

    In a DB in which lot of DML transaction occur, there will be empty spaces whenever deletions occur.

    Will SQL Server fill that part with data when new insertions occur ?.

    Thanks in advance.

    San.

  • October 1, 2015 at 6:46 am

    #1830728

    Joy Smith San (10/1/2015)

    Hi All,

    I understand that we shouldn't shrink data files as it might cause heavy fragmentation along with log usage, high IO/CPU etc.

    In a DB in which lot of DML transaction occur, there will be empty spaces whenever deletions occur.

    Will SQL Server fill that part with data when new insertions occur ?.

    Thanks in advance.

    San.

    It depends entirely on the table definition, slots in pages are re used where possible

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • October 1, 2015 at 9:02 pm

    #1830943

    Reindexing will seal up the holes, as well.

    --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)

  • October 1, 2015 at 9:42 pm

    #1830947

    Thanks Perry and Jeff.

    Any link to refer for understanding the process in detail ?.

    (How sql server fills the empty spaces caused by deletion). Thanks again.

  • October 1, 2015 at 10:19 pm

    #1830950

    This is basically the process behind the scenes.

    https://technet.microsoft.com/en-us/library/ms175195(v=sql.105).aspx

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 5 posts - 1 through 4 (of 4 total)

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