When I initially start talking about space allocation on the first day of my five-day SQL Server Internals class, I have a simple demo using the sp_spaceused procedure which I use to illustrate how much space is actually allocated to a table. I use an example that creates a table with very large rows, so that one row fills a page, which is always 8KB in size. With these rows, I can add lots of pages to my table very quickly.
Related: How are single-page allocations tracked?
The following is the table definition:
USE tempdb GO SET NOCOUNT ON GO CREATE TABLE LargeRows (col1 int identity, col2 char(8000) default '8000 bytes of data'); GO
After I insert one row into the table, I look at the space used with the following command:
INSERT INTO LargeRows DEFAULT VALUES; EXEC sp_spaceused LargeRows; GO
Figure 1 shows the results of this command:
Figure 1: Table space results
In addition to the one page of data, there's another 8KB, or one page, indicated for index information and I ask if anyone has an idea what that is, as I haven’t created an index on this table. Usually, there are some guesses (and no, the identity column doesn’t automatically have an index built on it), but frequently no one gets it right because I haven't yet talked about Index Allocation Map (IAM) pages, which is what this one "mystery" index page actually is.
IAM isn't just for indexes. Every table and every index has at least one IAM, which is a big, single-page bitmap indicating which extents belong to the table or index (an extent is SQL Server's main unit of space allocation, and contains eight contiguous pages in a file, with the first page always having a page number that’s a multiple of eight). Because a single page is 8KB, with a 96-byte header and a few bytes for the information about the bitmap itself, there are about 8,000 bits for the bitmap, and each represents an extent in a file. Because an extent is eight pages, or 64KB, 8,000 bits can keep track of about 4GB worth of space on a file. Each bit represents one extent, and if the bit is set, it means that the corresponding extent belongs to the table or index that owns the IAM.
So how many IAMs can you have for a single object? Certainly, if the table or index is more than 4GB, you’ll need more than one, but you can actually have multiple IAMs even for a relative small table. The table could have just a couple of extents, but if one starts at page 480,000 and one starts at page 528,000, each extent is in a different 4GB chunk of the file, so you need two IAMs. Or, you could have a small table created on a file group that has multiple files, so there are just a few extents on each file. You need separate extents for each file.
But you might need more than that. SQL Server keeps track of space used for large object (LOB) allocations (which can include columns of type varchar(MAX), varbinary(MAX), text, image, and xml) separately from the space used for regular row data. It also keeps track separately of the space for row_overflow data (variable length character or binary columns that are less than 8,000 bytes but don’t fit on the data page). And SQL Server keeps track of each partition's space separately.
So, each table or index will have one IAM for each of the following:
- 4GB chunk of space on each file for each partition on which the entity stores any regular rows
- 4GB chunk of space on each file for each partition on which the entity stores any large object data
- 4GB chunk of space on each file for each partition on which the entity stores any row overflow columns
To actually answer the question of how many IAMs one object could have, you would need to know the maximum size of a file, the maximum number of files per file group, and the maximum number of partitions. I'll leave that computation as an exercise for the reader.
And, of course, the minimum number is always one for any table that has any space used at all, and space used for the IAM pages themselves is counted in the index_size value in the output of sp_spaceused.
So, the mystery page is no longer a mystery. I was a bit concerned that writing this article might then mean more of my students would know the answer to my question. But if that happens, it's a good thing. And seeing as how I frequently forget what I've written within a few months, I wouldn't be surprised if many people reading this forget what that mystery page is before they end up in one of my classes.
