January 20, 2012 at 4:51 am
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!)
January 20, 2012 at 5:11 am
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. 😎
January 20, 2012 at 5:31 am
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:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 20, 2012 at 5:40 am
Love my easy point on a Friday. Thanks Steve.
http://brittcluff.blogspot.com/
January 20, 2012 at 5:43 am
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
January 20, 2012 at 5:57 am
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!
January 20, 2012 at 5:58 am
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)
January 20, 2012 at 6:03 am
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?)
January 20, 2012 at 6:05 am
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
January 20, 2012 at 6:49 am
Interesting question!
January 20, 2012 at 6:51 am
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! 😀
January 20, 2012 at 7:14 am
Thanks Steve. Great, easy question for a Friday. :hehe:
January 20, 2012 at 7:33 am
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...
January 20, 2012 at 7:52 am
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/
January 20, 2012 at 8:11 am
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 49 total)
You must be logged in to reply to this topic. Login to reply