The upper levels of the indexes contain pointers to pages in the next lower level. The level right above the leaf in a clustered index has a pointer to each of the table's 10,000 pages; for this index, the index rows at the upper levels will be 12 bytes each. Each index row contains the key value (5 bytes), a pointer to a page (6 bytes), and 1 byte of overhead. A total of 674 rows (8096 bytes available on a page divided by 12 bytes per row) will fit on a node-level index page. The index needs enough rows to point to all 10,000 pages, so it will have 15 index pages (10,000 divided by 674) at level 1. Now, index rows at level 2 point to all these 15 index pages, and since one page can contain 15 index rows, level 2 is the root. For the nonclustered index, the index rows at the higher levels will be 12 bytes each, so 674 index rows will fit per page, and you need two pages at level 1, with level 2 as the root page.
So, how big are these indexes compared to the table? The clustered index for a 10,000-page table had 16 index pages, which is less than 1 percent of the table size. I frequently use 1 percent as a ballpark estimate for the space requirement of a clustered index, although in this case, it's an overly large estimate. On the other hand, the nonclustered index for the 10,000-page table needed 683 pages, which is about 6 percent more space than the table data needs. Estimating the space necessary for nonclustered indexes is difficult. My example used a very small key. Nonclustered index keys are frequently much larger—or even composite—so keys of more than 100 bytes aren't unusual. In that case, you'd need many more leaf-level pages, and the total nonclustered index size could be 30 or 40 percent of the table size. (Remember that SQL Server lets you have as many as 249 nonclustered indexes!) Disk space is cheap, but is it that cheap? You need to plan your indexes carefully.
Several tools and commands let you examine index structures so that you can see exactly how much space an index needs and how many pages are at each level. The stored procedure sp_spaceused reports the total space used by all indexes on a table. Because the number is in kilobytes and each page holds 8KB, you need to divide by 8 to get the number of pages your indexes require. The value for the total index size includes the special allocation pages called index allocation maps (IAMs). Each table and each index has at least one IAM page. Note that no documented command can tell you the size of just one index. However, Enterprise Manager can show you the size of one index, so you can guess that SQL Server must have a procedure to return that information. Enterprise Manager actually calls the undocumented command sp_MSindexspace, which takes a table name and an index name as parameters. For example, the Northwind sample database has a 2055-row table called Order Details that contains one row for every item purchased in every order. The table has five indexes, including a clustered index called PK_Order_Details on the OrderID and ProductID columns. When I want to see the total space used just for the PK_Order_Details index, I can run the following command:
EXEC sp_MSindexspace [order details], PK_Order_Details
The value returned is simply the size of the index in kilobytes. You can run this command to examine the comparative sizes of different indexes on the table or see how an index will shrink or grow if you remove or add columns. Next month, I'll continue examining the structure of indexes, and I'll tell you about additional index information you can get from the sysindexes system table.