Tags: SQL Server Internals, webinar
Thanks to everyone who attended! You can get the slides and demo files here:
Webinar 2.3 — Large Datatype Storage
And here is the link to the video.
Don’t miss the next webinar on November 14th: Clustered Indexes! You can register here!
————————————–
Homework discussion for Webinar #2.2:
Calculate the maximum number of IAMs a single object, including its indexes, can have.
Although this exercise looks like it’s a straightforward problem, with one numeric answer, it’s actually more of a discussion question. In addition, we haven’t yet discussed all the components pieces you would need to actually calculate an answer.
We have talked about IAMs as a type of allocation page which keeps track of which extents belong to an particular object. When I first mentioned them, I said that each IAM maps to a 4GB chunk of space on a particular file. (There is 1 bit in the IAM for each 64KB extent in the file.) So there could be many IAMs for a single file, one for each 4GB chunk of space in which the object has any extents. Last week I also told you that each storage format has its own IAMs, so in any 4GB chunk of a file there could be an IAM for in-row data, for row-overflow data and for LOB data.
But there’s more.
An object (table or index) is created in a particular filegroup, not in a file, so one object could be spread across multiple files. And I briefly mentioned that each partition has its own IAMs.
What is the maximum number of IAMs for a table including all its indexes? An object has one IAM for each 4GB chunk of space on which it has any extents, on each file, for each storage format, for each partition.
So, how do we calculate the maximum number of IAMs? We need answers to all of the following:
1. What is the maximum number of storage formats (we know that, it’s 3)
2. What is the maximum number of partitions an object can have?
3. What is the maximum number of files in a filegroup?
4. What is the maximum number of indexes a table can have?
5. What is the maximum file size?
The last answer is needed to figure out how many 4GB chunks we can have. But the answer for this one is ‘it depends’. It is dependent on the OS limits, which depend on the file system and the version of Windows. The other three questions (2-4), you should be able to research and find the answer. Or wait until we cover the answers in upcoming webinars.
