November 28, 2012 at 7:34 am
Hi Guys
I have posted a few times regarding this topic, I have taken into account all advice given and I have researched the topic. Still, I am confused as ever when it comes to the topic of SQL's buffer pool and clean/dirty pages.
When a Page is modified in memory, at some point those changes need to be reflected in the data file. The Lazy Writer/CheckPoint process takes care of this.
Now, when that change gets reflected on disk, does the changed page in Memory replace the corresponding page on disk?
I understand that whatever happens with the above, the page is then marked as clean. By clean, does this mean it becomes a clean buffer(A Clean frame ready to hold another page) or is it just another page taking up space in the buffer pool.
Thanks
November 28, 2012 at 8:37 am
SQLSACT (11/28/2012)
Now, when that change gets reflected on disk, does the changed page in Memory replace the corresponding page on disk?
The 8kb page in memory is written to disk, to the appropriate 8kb chunk of the data file.
I understand that whatever happens with the above, the page is then marked as clean. By clean, does this mean it becomes a clean buffer(A Clean frame ready to hold another page) or is it just another page taking up space in the buffer pool.
The page is marked clean. Meaning that the 8kb database page in memory no longer contains changes that have not been written back to disk yet. That is all.
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
November 28, 2012 at 10:00 am
Thank You Gail
Please bear with me
The 8kb page in memory is written to disk, to the appropriate 8kb chunk of the data file.
On one of my posts regarding this, this is what you replied to my previous thread on this topic:
Neither checkpoint nor lazywriter 'removes' dirty pages from the buffer pool. They just write the changes back to disk so that the pages are considered clean.
See my confusion?
The page is marked clean. Meaning that the 8kb database page in memory no longer contains changes that have not been written back to disk yet. That is all.
These clean pages have absolutely nothing to do with Free Buffers?
If that is the case and there are no dirty pages to be found then does the Lazy Writer start removing Clean pages to free up buffers?
Thanks
November 28, 2012 at 10:20 am
SQLSACT (11/28/2012)
Thank You GailPlease bear with me
The 8kb page in memory is written to disk, to the appropriate 8kb chunk of the data file.
On one of my posts regarding this, this is what you replied to my previous thread on this topic:
Neither checkpoint nor lazywriter 'removes' dirty pages from the buffer pool. They just write the changes back to disk so that the pages are considered clean.
See my confusion?
No, I don't see why you're confused. I've been saying the same thing, again and again and again. No contradictions, no changed stories.
The page is marked clean. Meaning that the 8kb database page in memory no longer contains changes that have not been written back to disk yet. That is all.
These clean pages have absolutely nothing to do with Free Buffers?
Clean page = data page that does not have any changes on it (as compared to the page in the data file on disk).
Free buffer = buffer pool page that is not currently in use.
If that is the case and there are no dirty pages to be found then does the Lazy Writer start removing Clean pages to free up buffers?
Maybe. Depends if there's demand for free buffers and a lack of pages on the free list.
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
November 28, 2012 at 11:22 am
SQLSACT (11/28/2012)
Now, when that change gets reflected on disk, does the changed page in Memory replace the corresponding page on disk?
"replace" is a poor choice of words here. When SQL writes changed data from the buffer to the disk, it doesn't replace anything, really. It overwrites it. There is no good way to describe this using non-computer terms, but I'll try.
Think of this like an Etch-a-Sketch. Any changes made to an existing picture will be reflected on the screen of the Etch-a-Sketch after you make them. They are "written to disk." But making those changes doesn't get you a new Etch-a-Sketch. You still have the old one. Make sense?
November 28, 2012 at 11:26 pm
Thanks Gail
Please don't get me wrong, I'm not saying that you're are being contradictory. There's obviously a huge difference between what you're implying and what I'm inferring.
The 8kb page in memory is written to disk, to the appropriate 8kb chunk of the data file.
To me, this seems like the page is written to disk
They just write the changes back to disk
This seems like the page is not written back to disk, but rather the page contents that have changed are written to the corresponding pages on disk. I think this in unlikely because ALL page changes are made in memory
Unless....
They just write the changes back to disk
Means, the changed pages are written back to disk.
Clean page = data page that does not have any changes on it (as compared to the page in the data file on disk).
Free buffer = buffer pool page that is not currently in use.
Clean Page - This obviously takes up space in the Buffer Pool
Free Buffer - Is this the same as the 'Frame' that I've read about?
Thanks
November 28, 2012 at 11:28 pm
Brandie Tarvin (11/28/2012)
SQLSACT (11/28/2012)
Now, when that change gets reflected on disk, does the changed page in Memory replace the corresponding page on disk?"replace" is a poor choice of words here. When SQL writes changed data from the buffer to the disk, it doesn't replace anything, really. It overwrites it. There is no good way to describe this using non-computer terms, but I'll try.
Think of this like an Etch-a-Sketch. Any changes made to an existing picture will be reflected on the screen of the Etch-a-Sketch after you make them. They are "written to disk." But making those changes doesn't get you a new Etch-a-Sketch. You still have the old one. Make sense?
Thanks Brandie
To be honest, I have never seen an Etch-a-Sketch before, besides on TV. I don't think we have those here :w00t:
I am slowly grasping the concept of this process
Thanks
November 28, 2012 at 11:48 pm
SQLSACT (11/28/2012)
This seems like the page is not written back to disk, but rather the page contents that have changed are written to the corresponding pages on disk.
And what exactly is the difference between the page (which is an 8kb chunk of disk or memory) been written back to disk and the page contents (which is 8kb of data that makes up the page) been written back to disk?
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
November 28, 2012 at 11:55 pm
GilaMonster (11/28/2012)
SQLSACT (11/28/2012)
This seems like the page is not written back to disk, but rather the page contents that have changed are written to the corresponding pages on disk.And what exactly is the difference between the page (which is an 8kb chunk of disk or memory) been written back to disk and the page contents (which is 8kb of data that makes up the page) been written back to disk?
The way I understand it......
page (which is an 8kb chunk of disk or memory) been written back to disk
Page completely replaces the corresponding page on disk
the page contents (which is 8kb of data that makes up the page) been written back to disk?
Contents of page is copied, contents of page on disk is deleted, contents of changed page in memory is
pasted on corresponding page on disk
November 29, 2012 at 12:03 am
And the difference between those two is....???
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
November 29, 2012 at 12:10 am
One is a replacement of the entire page and one is the replacement of the contents of the page. To me, this seems like 2 different things, clearly in this context, it's not. Understood
Given that all changes are done in memory, I would safely assume that the former is true
November 29, 2012 at 12:33 am
A page is just an 8kb chunk of data. Given that, what exactly is the difference between a page (an 8kb chunk of data) and the contents of the page (8kb of data)?
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
November 29, 2012 at 12:38 am
GilaMonster (11/29/2012)
A page is just an 8kb chunk of data. Given that, what exactly is the difference between a page (an 8kb chunk of data) and the contents of the page (8kb of data)?
I see where my understanding was throwing me off now..
I my mind, I pictured a Page to be a seperate entity that SQL creates and then subsequently stores info on that.
November 29, 2012 at 1:04 am
There's a reason people have been referring you to Kalen's internals book on a regular basis....
http://sqlskills.com/blogs/paul/post/Inside-the-Storage-Engine-Anatomy-of-a-page.aspx
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
November 29, 2012 at 6:03 am
Before you ask any more questions, or post any more threads, you really need to read the book everyone has been referring you to. Then ask questions after you've worked through that.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply