SQL 2012: Dirty pages flushed in a single write

  • GP Van Eron

    SSCarpal Tunnel

    Points: 4626

    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

  • Nakul Vachhrajani

    SSChampion

    Points: 10209

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

    Thanks & Regards,
    Nakul Vachhrajani.
    http://nakulvachhrajani.com
    Be courteous. Drive responsibly.

    Follow me on
    Twitter: @sqltwins

  • Carlo Romagnano

    SSC-Insane

    Points: 21943

    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: 258965

    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: 10209

    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
    Be courteous. Drive responsibly.

    Follow me on
    Twitter: @sqltwins

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    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

    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

    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

    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

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

  • TomThomson

    SSC Guru

    Points: 104773

    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

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

  • George Vobr

    SSCrazy Eights

    Points: 9171

    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: 10209

    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
    Be courteous. Drive responsibly.

    Follow me on
    Twitter: @sqltwins

  • This was removed by the editor as SPAM

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

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