Go Under the Covers:iam chains and allocation units in SQL Server 2005

(I ' m sitting here-Seattle Airport at 7am Sunday waiting to catch the same flight to Boston that I caught II weeks a Go. Instead of TechEd, this time I ' m going to a training course at MIT. I ' d enjoy the air travel a lot and a bigger gap in between the flights. At least I got an aisle seat and another-air miles ...)

Anyway, time for part, and the discussion of IAM chains.

Yesterday I explained what IAM chains is and how they correspond to indexes in SQL Server 2000. A table can has a heap or clustered index, plus up to 249 non-clustered indexes, plus storage for LOB values (commonly CA lled the text index). This means is in SQL Server, there can be a maximum of 251 IAM chains per table.

In SQL Server 2005, IAM chains and IAM pages was exactly the same, but what they correspond to is different. A table can now has up to 750000 IAM chains! wow! What's on earth does we do?...

There is now three things this IAM chains map space allocations for:

1. Heaps and B-trees (a b-tree is the internal structure used to store an index)
2. LOB data
3. Row-overflow data

And we now call these units of space allocation tracking allocation units. The internal names for these three types of allocation unit is (respectively):

1. HOBT allocation Unit (HEAP OR B-Tree, pronounced ' Hobbit ', yes, as in the Lord of the Ring S
2. LOB allocation unit
3. Slob allocation Unit (Small-LOB)

[Edit] And the external names are, respectively:

1. In_row_data allocation unit
2. Lob_data allocation unit
3. Row_overflow_data allocation unit

Let's look at three new features on SQL Server 2005 that made these changes is necessary and boosted the number of potent Ial IAM chains per table.

Included Columns The ability for non-clustered indexes to include Non-key columns at the Leaf-level. This was useful for three reasons:

1. It allows a non-clustered index to truly cover a query where the query results include more than all columns or the Combina tion of column lengths in the "Query results is greater than" bytes (remember, a non-clustered index key is limited To columns and bytes)
2. It allows columns to is include in the non-clustered index that has data types that cannot is part of an index key (e.g. text or XML)
3. It allows a non-clustered index to cover a query without has to has all of the columns included in the index key. As the index key is included on rows at all levels of the B-tree, this allows the index to be smaller.

An example of space saving:imagine A to million row index, with a key length of bytes, if only the first of the Intege R Keys is really needed as the index key, the other 4 fixed-length columns could is included.

With the. Byte key, 8 rows can fit per database page (i.e. the fanout is 8). This means there'll be 12500000 pages at the leaf level, 1562500 pages at the next level up with the b-tree and so on, giv ing a total of 12500000 + 1562500 + 195313 + 24415 + 3052 + 382 + + 6 + 1 = 14285717 pages (including 1785717 to store The upper levels of the B-tree).

If We go with the included columns method then the key size shrinks to 8 bytes, and with the row overhead we can get th e row length in the upper levels of the b-tree down to the bytes (giving a fanout of approx. 537). Note the fanout at the Leaf-level are still going to being 8,  because the amount of data stored in each row at the Leaf-level is the same. So, this means there'll be 12500000 pages in the leaf level, 23278 pages at the next level up and so on, giving a total of 12500000 + 23278 + + 1 = 12523323 pages (including 23323 to store the upper levels of the B-tree). Compared to the full-size 900-byte key, which is a 12% saving of 1762394 pages, or 13.6gb! Obviously this was a contrived case if you can see how the savings can occur.

Anyway, I digress. The main reason for adding this feature it to enable true covering queries. A covering query is one where the "query optimizer knows it can get all the" query results from the non-clustered index and So the query can be satisfied without have to incur the extra IOs of looking up data in the base table-a significant p Erformance saving.

Now that non-clustered indexes can has included columns, and those columns can be LOB data types. This means that has a single LOB allocation unit (as in the case of the single text index in SQL Server) isn ' t pos Sible any and because each index may has its own set of lobs. Now, if you would ask why we didn ' t just has a single set of lobs with multiple references from various indexes plus the base Table. We considered that but it would has made things a lot more complicated.

So, with this new feature, each index needs-allocation units-one for the data or index rows (the HOBT allocation uni T) and one for any LOB data.

Large Rows

One of the things that have plagued schema designers for a long time was the 8060 byte limit on table row sizes so we ' ve rem Oved This restriction in SQL Server 2005. The the-the-variable-length columns (e.g. varchar, sqlvariant) to get pushed off -row when the row size gets too big to fit on a single page.

But where does these column values get pushed to? We effectively turn them into mini LOB columns. The column value in the row are replaced with a 16-byte pointer to the Off-row column value, which are stored as if its a LO B value in a seperate allocation unit-the row-overflow (or slob) allocation unit. These values is stored in text pages in exactly the same as regular LOB values is, just using a seperate allocation Unit. The Slob allocation unit is a created when the first column of value is pushed Off-row.

This feature works for non-clustered indexes too-if your Consider the ability to has included columns in non-clustered I Ndexes then you could easily has non-clustered index rows that won ' t fit on a page. It would has been short-sighted of us to get rid of the 900-byte limit and replace it with a 8060-byte limit by not Exte Nding the Row-overflow feature to non-clustered indexes too.

Now with the addition of this new feature, each index can has up to three allocation units-hobt, LOB, and slob. Even with this, which is only makes a maximum of the of the of the, IAM chains per table (remember an IAM chain now maps the storage Allocati ONS for a allocation unit, so indexes * 3 allocation units = + + IAM chains). But I mentioned thousand IAM chains per table Earlier-where does all the rest come from?

Partitioning

This is what gives us the 1000x multiplier. As already know, partitioning is the new feature that allows tables and indexes to being split into a series of range s, with each range stored separately (most commonly in seperate filegroups). Partitioning is a topic for a separate post.

If each range or partition of the table or index is stored seperately and then all are going to need its own HOBt al Location unit. Of course, the LOB values associated with all partition need to being stored with it, and so each partition also needs a LOB Allocation unit. Also, the Row-overflow feature is Per-row, and so rows in each partition would overflow into slob allocation units just as For un-partitioned tables and indexes. Thus each partition of a table or index can has up to three allocation units (and hence three IAM chains).

Still, where does that is come in? Each table or index can has up to partitions. This gives us indexes x partitions x 3 allocation units = 750000 IAM chains. Realistically this probably won ' t happen, but it's possible.

Now you know some more about how things is organized in SQL Server, SQL Server 2005 and hopefully this would help und Erstanding Some of my future posts.

Ah-here comes the breakfast cart ...

<textarea id="ctl00_content_ctl00_w_205503__af096d_ctl00_ctl15_ctl01_Tags" style="width: 97%; height: 100%;" rows="2" cols="30"></textarea> On-disk Structures

Go Under the Covers:iam chains and allocation units in SQL Server 2005