Pages in Buffer Cache

Question

Pages in Buffer Cache

Lonely Rogue

SSCrazy

Points: 2579
More actions
September 15, 2014 at 10:54 pm

#310942

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

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

Lonely Rogue SSCrazy Points: 2579 More actions · Answer 2

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

Alexander Suprun SSCertifiable Points: 6253 More actions · Answer 4

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