Pages in Buffer Cache

  • Hey folks -

    While I was discussing the concept of SQL Server loading the data into Buffer for modification, a doubt troubled me -

    Knowing that based on WHERE predicate of UPDATE, the Storage Engine loads all those Pages that has rows that satisfied the WHERE filter. Implication is that Engine loads complete Page even if that Page has only 1 Row that matched, along with several other non-matched rows.

    So, the question is - after the Query Processor completes modifying the data of that 1 Row, does entire Page gets flushed back to disk or that only 1 Row that was modified ?

    Am confused.. and if you are too, I will re-phrase my question 😀

    --In 'thoughts'...

    --In 'thoughts'...
    Lonely Rogue

  • The minimum unit for a read or write to/from disk is the page. The row couldn't possibly be written alone, the page headers change when a row changes.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thank you @gilamonster.

    So, the page is loaded, a row is updated and whole page is written back to disk.

    and what happens with INSERT & DELETE? further, isn't it an unnecessary effort to write back the unmodified rows too.. I wonder.

    -- In 'thoughts'...

    --In 'thoughts'...
    Lonely Rogue

  • Lonely Rogue (9/16/2014)


    and what happens with INSERT & DELETE?

    The same.

    further, isn't it an unnecessary effort to write back the unmodified rows too.. I wonder.

    Hmm... What's more effort...

    Write 8kb of data to disk at a standard offset in the file?

    or

    figure out what part of the of the page the changed row is on, write the row (a variable amount of data at a not standard offset in the file)

    then figure out what portion of the page header has changed, write that (a variable amount of data at a not standard offset in the file)

    then figure out what portion of the page footer has changed, write that (a variable amount of data at a not standard offset in the file)

    and then hope and pray that all three writes succeeded otherwise it's a guaranteed corrupt page and data loss?

    And that's not even touching compressed pages...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • And also page is not immediately written to the disk. So it might happen that by the time when server has to write the page to the disk there are 10 records changed. So it would be even more work involved to write it row by row...


    Alex Suprun

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

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