Buffer Pool

Question

Post reply

Buffer Pool

SQLSACT

SSC-Insane

Points: 21838
More actions
November 9, 2012 at 12:06 am

#264611

Hi All
This is a newbie question but I really cant seem to get my head around it
In SQL's buffer pool, you have clean and dirty pages.
I understand that Checkpoint removed dirty pages to minimize recovery time and Lazy writer removes dirty pages to keep available space in buffer pool
What I'm trying to figure out is what happens to clean pages? Do they just stay indefinately? If they are removed, which process removes them?
Thanks

Viewing 15 posts - 1 through 15 (of 16 total)

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

see these links

http://www.sqlservercentral.com/blogs/sql_server_dba/2011/11/28/sql-dirty-pages-/

http://msdn.microsoft.com/en-us/library/aa337560(v=sql.105).aspx

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

SQLSACT SSC-Insane Points: 21838 More actions · Answer 2

Bhuvnesh (11/9/2012)
see these links
http://www.sqlservercentral.com/blogs/sql_server_dba/2011/11/28/sql-dirty-pages-/
http://msdn.microsoft.com/en-us/library/aa337560(v=sql.105).aspx

Thanks

Those articles deal with Dirty pages in the buffer pool

I'm trying to understand the behaviour of clean pages in the buffer pool

Thanks

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

SQLSACT (11/9/2012)
I'm trying to understand the behaviour of clean pages in the buffer pool

see this

"Dirty" pages are pages where data has been changed but has not yet been written to disk. After a page is written to disk, it is considered "clean", and remains in the buffer pool. The space occupied by clean pages can be used for new pages, and is available for migration to an associated extended storage cache (if defined).

this is taken from https://www1.columbia.edu/sec/acis/db2/db2d0/db2d0122.htm

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

SQLSACT SSC-Insane Points: 21838 More actions · Answer 4

Bhuvnesh (11/9/2012)
SQLSACT (11/9/2012)
I'm trying to understand the behaviour of clean pages in the buffer pool
see this
"Dirty" pages are pages where data has been changed but has not yet been written to disk. After a page is written to disk, it is considered "clean", and remains in the buffer pool. The space occupied by clean pages can be used for new pages, and is available for migration to an associated extended storage cache (if defined).
this is taken from https://www1.columbia.edu/sec/acis/db2/db2d0/db2d0122.htm

Looks like DB2, does the same apply for SQL Server?

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 5

Once a dirty page has been written to disk it is either kept in the buffer pool for further access or it is returned to the free buffer list for new requests to take advantage of. The algorithm that determines this is called the LRU-K algorithm and its determination is based on the last 2 times a page was accessed.

Take a look at Kalen Delaney's excellent book "Microsoft SQL Server 2008 Internals"

Joie Andrew
"Since 1982"

SQLSACT SSC-Insane Points: 21838 More actions · Answer 6

Joie Andrew (11/9/2012)
Once a dirty page has been written to disk it is either kept in the buffer pool for further access or it is returned to the free buffer list for new requests to take advantage of. The algorithm that determines this is called the LRU-K algorithm and its determination is based on the last 2 times a page was accessed.
Take a look at Kalen Delaney's excellent book "Microsoft SQL Server 2008 Internals"

Thanks

I'm trying to understand what happens to clean pages in the buffer pool (when a select statement brings pages into the buffer pool).

What happens to the pages once the select statement is done with them?

Thanks

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 7

SQLSACT (11/9/2012)
Bhuvnesh (11/9/2012)
SQLSACT (11/9/2012)
I'm trying to understand the behaviour of clean pages in the buffer pool
see this
"Dirty" pages are pages where data has been changed but has not yet been written to disk. After a page is written to disk, it is considered "clean", and remains in the buffer pool. The space occupied by clean pages can be used for new pages, and is available for migration to an associated extended storage cache (if defined).
this is taken from https://www1.columbia.edu/sec/acis/db2/db2d0/db2d0122.htm
Looks like DB2, does the same apply for SQL Server?

i think same concept get applied to sql server too

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

hs24 Default port Points: 1440 More actions · Answer 8

This is handled by the LazyWriter process by using a clock algorithm to sweep thru the buffer pool. This then frees up and clean buffer to make sure there is a supply of buffers empty for the next set of data pages and is carried out when the number of pages on the free list falls below a minimum value. This value is computed as a percentage of the overall buffer pool size.

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

SQLSACT (11/9/2012)

I'm trying to understand what happens to clean pages in the buffer pool (when a select statement brings pages into the buffer pool).
What happens to the pages once the select statement is done with them?

Nothing.

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.

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

SQLSACT SSC-Insane Points: 21838 More actions · Answer 10

GilaMonster (11/9/2012)

SQLSACT (11/9/2012)
I'm trying to understand what happens to clean pages in the buffer pool (when a select statement brings pages into the buffer pool).
What happens to the pages once the select statement is done with them?
Nothing.
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.

Ok

Now I'm really confused

The way I understood it is:

>> Insert, Update or Delete statement is received by SQL Server

>> Required pages are copied from disk into buffer pool

>> Changes are made to affected pages

>> Checkpoint/Lazy Writer process writes the pages back to disk, replacing the original page on disk.

Am I missing something here?

Thanks

CapnHector SSCoach Points: 16143 More actions · Answer 11

SQLSACT (11/9/2012)

GilaMonster (11/9/2012)
SQLSACT (11/9/2012)
I'm trying to understand what happens to clean pages in the buffer pool (when a select statement brings pages into the buffer pool).
What happens to the pages once the select statement is done with them?
Nothing.
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.
Ok
Now I'm really confused
The way I understood it is:
>> Insert, Update or Delete statement is received by SQL Server
>> Required pages are copied from disk into buffer pool
>> Changes are made to affected pages
>> Checkpoint/Lazy Writer process writes the pages back to disk, replacing the original page on disk.
Am I missing something here?
Thanks

one last step, the now "clean" pages remain in the buffer pool as clean pages until SQL Server needs the memory for something else.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 12

Joie Andrew (11/9/2012)
Take a look at Kalen Delaney's excellent book "Microsoft SQL Server 2008 Internals"

+1 An excellent source of this type of information.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

SQLSACT SSC-Insane Points: 21838 More actions · Answer 13

capnhector (11/9/2012)

SQLSACT (11/9/2012)
GilaMonster (11/9/2012)
SQLSACT (11/9/2012)
I'm trying to understand what happens to clean pages in the buffer pool (when a select statement brings pages into the buffer pool).
What happens to the pages once the select statement is done with them?
Nothing.
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.
Ok
Now I'm really confused
The way I understood it is:
>> Insert, Update or Delete statement is received by SQL Server
>> Required pages are copied from disk into buffer pool
>> Changes are made to affected pages
>> Checkpoint/Lazy Writer process writes the pages back to disk, replacing the original page on disk.
Am I missing something here?
Thanks
one last step, the now "clean" pages remain in the buffer pool as clean pages until SQL Server needs the memory for something else.

Are these clean pages or clean buffers?

Thanks

SQLSACT SSC-Insane Points: 21838 More actions · Answer 14

GilaMonster (11/9/2012)

SQLSACT (11/9/2012)
I'm trying to understand what happens to clean pages in the buffer pool (when a select statement brings pages into the buffer pool).
What happens to the pages once the select statement is done with them?
Nothing.
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.

Thanks Gail

Please help me understand this process

When a checkpoint runs, it doesn't actually remove the page from memory, it marks the page as clean? Does this mean that it removes the contents of the page?

Thanks