billy-yons and billy-yons of rows

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    paul.knibbs (1/20/2012)


    Hugo Kornelis (1/20/2012)


    I don't know the exact cause of the 16 TB limit for each file. Maybe it has to do with the structure of special pages that apply to the entire file (like GAM, SGAM and PFS pages, or IAM pages). Or maybe it is simply a file size limitation imposed by the Windows OS or the NTFS internals?

    Or it could be that a signed 32-bit integer would allow for 2147483647 pages, which is 16Tb minus a page...no idea if that's the case or not, though!

    Sounds plausible. Pages do have a page number.

    (Though I don't understand why MS would choose to use a signed integer to store page numbers; it's not as if there will ever be negative page numbers!)


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • SqlMel

    SSCrazy

    Points: 2891

    Can't remember how many times I've had to answer that question but it sure prompted some very interesting posts.

    Good job, guys.

    ---------------
    Mel. 😎

  • Paul White

    SSC Guru

    Points: 150442

    Hugo Kornelis (1/20/2012)


    (Though I don't understand why MS would choose to use a signed integer to store page numbers; it's not as if there will ever be negative page numbers!)

    They have form in this area: unique-ifiers use only the positive range of an INT4.

    The other side of the row limitation calculation is to consider page compression :w00t:

  • Britt Cluff

    SSCertifiable

    Points: 5083

    Love my easy point on a Friday. Thanks Steve.

    http://brittcluff.blogspot.com/

  • Thomas Abraham

    SSChampion

    Points: 10761

    Hugo Kornelis (1/20/2012)


    Just for kicks ...

    ...you would need to have almost 200 million of those machines.

    You left out the most important part of the calculation, for the money anyway. What would be the cost of 0.01% (20,000) of these servers? If you don't want it, I'll take it.

    (Mistakenly attributed 0.01% to Paul in original post.)

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • paul.knibbs

    SSCoach

    Points: 15270

    Hugo Kornelis (1/20/2012)


    Sounds plausible. Pages do have a page number.

    (Though I don't understand why MS would choose to use a signed integer to store page numbers; it's not as if there will ever be negative page numbers!)

    Well, they seem to use one for the file number too--that 32767 you mentioned earlier is the maximum possible value for a SIGNED 16-bit integer; 32-bit would go up to 65535!

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    Thomas Abraham (1/20/2012)


    Hugo Kornelis (1/20/2012)


    Just for kicks ...

    ...you would need to have almost 200 million of those machines.

    You left out the most important part of the calculation, for the money anyway. What would be the cost of 0.01% (20,000) of these servers? If you don't want it, I'll take it.

    (Mistakenly attributed 0.01% to Paul in original post.)

    I was not able to find a price quote on that machine. But I am sure that, whatever the price is, multiplying it by 20,000 will suffice for me to retire and live the rest of my life in luxury!

    (If I had asked for a full percent, I'd probably have been able to save the Greece economy as well)


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    paul.knibbs (1/20/2012)


    Hugo Kornelis (1/20/2012)


    Sounds plausible. Pages do have a page number.

    (Though I don't understand why MS would choose to use a signed integer to store page numbers; it's not as if there will ever be negative page numbers!)

    Well, they seem to use one for the file number too--that 32767 you mentioned earlier is the maximum possible value for a SIGNED 16-bit integer; 32-bit would go up to 65535!

    I assume you meant to write that SIGNED 16-bit goes up to 32767 and UNSIGNED 16-bit goes up to 65535. 32 bits goes quite a bit further!

    And you are right, that had occurred to me as well. Apparently, MS decided to use signed integers for both page and file numbers. Maybe it never occured to them that people would want more than 16 TB in a file, or more then 32,767 files. Or maybe there is some performance advantage of using signed versus unsigned integers?

    (Would you believe, after all of this discussion, that my largest database is 500 MB in size and lives on my laptop?)


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Thomas Abraham

    SSChampion

    Points: 10761

    Hugo Kornelis (1/20/2012)[/bI was not able to find a price quote on that machine. But I am sure that, whatever the price is, multiplying it by 20,000 will suffice for me to retire and live the rest of my life in luxury!

    That was my thought. 20,000 times anything would likely be enough.

    (If I had asked for a full percent, I'd probably have been able to save the Greece economy as well)

    Funny you should mention that. I'm taking my wife to Greece this Summer for two weeks. (Trying to talk her into retiring there.) That's where I would have gone with the 0.01%.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • paul s-306273

    SSChampion

    Points: 10615

    Interesting question!

  • paul.knibbs

    SSCoach

    Points: 15270

    Hugo Kornelis (1/20/2012)


    (Would you believe, after all of this discussion, that my largest database is 500 MB in size and lives on my laptop?)

    You're right, I did mean 16-bit unsigned earlier, my bad...and my biggest database is around 5Gb, but I find it an interesting discussion nonetheless! 😀

  • Dana Medley

    SSCertifiable

    Points: 6764

    Thanks Steve. Great, easy question for a Friday. :hehe:



    Everything is awesome!

  • mtassin

    SSC-Insane

    Points: 23099

    Hugo Kornelis (1/20/2012)


    paul.knibbs (1/20/2012)


    Hugo Kornelis (1/20/2012)


    (Would you believe, after all of this discussion, that my largest database is 500 MB in size and lives on my laptop?)

    No I wouldn't....

    I don't think I have any production DB's on my servers that small...



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • Sean Lange

    SSC Guru

    Points: 286536

    Nice straight forward question with a nod to Carl Sagan. Great ending to the week and "keep looking up". 😀

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • SQLRNNR

    SSC Guru

    Points: 281243

    The question title made me think of this

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 15 posts - 16 through 30 (of 50 total)

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