SQL 2012: Dirty pages flushed in a single write

Question

Post reply

SQL 2012: Dirty pages flushed in a single write

GP Van Eron

SSCarpal Tunnel

Points: 4626
More actions
August 9, 2016 at 11:31 pm

#400549

Comments posted to this topic are about the item SQL 2012: Dirty pages flushed in a single write
Additional documentation ~ recommended by Nakul Vachhrajani ~ "Writing Pages" - https://technet.microsoft.com/en-us/library/aa337560(v=sql.105).aspx
August 12, 2016 at 1:07 am

This was removed by the editor as SPAM

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

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

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 1

Thank-you for the question! Made me revisit the documentation and refresh the concepts (Good!)

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

Carlo Romagnano SSC-Insane Points: 22668 More actions · Answer 2

Nakul Vachhrajani (8/9/2016)
Thank-you for the question! Made me revisit the documentation and refresh the concepts (Good!)

+1

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

Nakul Vachhrajani (8/9/2016)
Thank-you for the question! Made me revisit the documentation and refresh the concepts (Good!)

Which documentation? I couldn't find any 🙂

Great question btw.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 4

Koen Verbeeck (8/10/2016)
Nakul Vachhrajani (8/9/2016)
Thank-you for the question! Made me revisit the documentation and refresh the concepts (Good!)
Which documentation? I couldn't find any 🙂

Here you go: "Writing Pages" - https://technet.microsoft.com/en-us/library/aa337560(v=sql.105).aspx

The documentation is for SQL 2008 R2. Since I was unable to find anything after that untill I hit SQL 2016, I assumed that no changes were made in these versions.

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

Nakul Vachhrajani (8/10/2016)
Koen Verbeeck (8/10/2016)
Nakul Vachhrajani (8/9/2016)
Thank-you for the question! Made me revisit the documentation and refresh the concepts (Good!)
Which documentation? I couldn't find any 🙂
Here you go: "Writing Pages" - https://technet.microsoft.com/en-us/library/aa337560(v=sql.105).aspx
The documentation is for SQL 2008 R2. Since I was unable to find anything after that untill I hit SQL 2016, I assumed that no changes were made in these versions.

Thanks!

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

TomThomson SSC Guru Points: 104773 More actions · Answer 6

Good question. But I got it wrong.

That will teach me to be more careful instead of just using the last number I saw (128, in the vast quantity of documents I've been pointed at to persuade me to go to SQL Server 2016, but I was forgetting that 128 was an improvement on earlier versions).

Many of that vast quantity of documents were a useful read, but all of them were pointless from the view of getting me to upgrade, as I had already decided that I would definitely upgrade but would not do it until a week or two before the end of the free upgrade offer by which time the handling of disk errors might have improved - it was terrible the first time I upgraded, almost a year ago, so when my system died and I reinstalled the original OS - windows 8.0 I loaded patches and upgrades only as far as the latest version of windows 8.1 which in my experience seemed to be better at hardware error management and reporting than windows 10 was way back then. I haven't had any broken disks (as far as I know) in the last few weeks, so I still don't really know if I can trust Windows 10 to report them when I do.

Tom

GP Van Eron SSCarpal Tunnel Points: 4626 More actions · Answer 7

Thanks for the positive feedback! My hope was to not make the question too tough, trigger learning more than challenge. But looking at the % correct feel a bit bad.

GP Van Eron SSCarpal Tunnel Points: 4626 More actions · Answer 8

I agree, good documentation on this prior to 2012 is limited. There is some good information in this book https://books.google.com/books?id=HvD2-aqsDtAC&pg=PA442&lpg=PA442&dq=sql+server+buf+array&source=bl&ots=POqF2EmvRg&sig=kJM2ygTBRcTSaNq3NzyZ2Q7eBMY&hl=en&sa=X&ved=0ahUKEwjru8G0grfOAhVP-mMKHW1bATMQ6AEIHjAA#v=onepage&q=sql%20server%20buf%20array&f=false

Under "BUF Array" and "Flushing and Freeing pages"

GP Van Eron SSCarpal Tunnel Points: 4626 More actions · Answer 9

Thanks Nakul, that's a good one too I didn't see before. link has been included up top

TomThomson SSC Guru Points: 104773 More actions · Answer 10

GP Van Eron (8/10/2016)
Thanks for the positive feedback! My hope was to not make the question too tough, trigger learning more than challenge. But looking at the % correct feel a bit bad.

Don't feel bad about 42% (that's what I see now) of people getting it right. 42% shows that it's a reasonably easy question, and at the same time 58% wrong shows that it's a question from which a substantial number of people can learn something. And they may even be learning something important (like me, learning to be less careless).

So write some more questions please - the more questions people write, the more different people we have producing questions with a different approach, a different standpoint, the better this QotD feature of SQLServerCentral will be.

Tom

GP Van Eron SSCarpal Tunnel Points: 4626 More actions · Answer 11

Thanks for the feedback Tom. Definitely planning on writing more as it forces me to keep learning new stuff.

George Vobr SSCrazy Eights Points: 9996 More actions · Answer 12

Nakul Vachhrajani (8/10/2016)
Koen Verbeeck (8/10/2016)
Nakul Vachhrajani (8/9/2016)
Thank-you for the question! Made me revisit the documentation and refresh the concepts (Good!)
Which documentation? I couldn't find any 🙂
Here you go: "Writing Pages" - https://technet.microsoft.com/en-us/library/aa337560(v=sql.105).aspx
The documentation is for SQL 2008 R2. Since I was unable to find anything after that untill I hit SQL 2016, I assumed that no changes were made in these versions.

I accidentally came out of this documentation https://technet.microsoft.com/cs-cz/library/cc966500.aspx ,

in which it stated: "For example, during the checkpoint process up to sixteen 8-KB pages can be flushed with a single WriteFileGather invocation.""

The only current and accurate documentation is listed in the link to today's QotD:

https://blogs.msdn.microsoft.com/bobsql/2016/06/03/sql-2016-it-just-runs-faster-indirect-checkpoint-default/

It says here: "SQL Server 7.0 through SQL Server 2008 gathered 16 dirty pages, whenever possible, should page id

and flushed them in a single write. SQL Server 2012 and newer versions gather 32 dirty pages, whenever possible.

For SQL 2016 that value has been increased to 128 pages."

This is a substantial change compared to SQL 2008, SQL 2012 and SQL 2014.

I remain with best regards G. V.

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 13

GP Van Eron (8/10/2016)
Thanks Nakul, that's a good one too I didn't see before. link has been included up top

Thank-you! And again, it's a great question because it helps the learning process - which is exactly what the QotD feature is all out. Please continue to share more questions! 🙂

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins