The sysindexes system table contains information (such as disk space used) that isn't available through any supplied system procedure, function, or view. Many people might never need this information, but if your needs go beyond the tools Microsoft supplies, it can be helpful to understand the information in this table. Let's look at the columns that contain information about table and index size, then I'll give you some information about index-key columns.
The documented procedure sp_spaceused uses the columns containing size information to report the total size of all indexes on a table. The undocumented procedure sp_MSindexspace uses these same columns to provide information about index sizes and can report the size of each individual index. (For more information about sp_MSindexspace, see my November 2004 column, "Get Into Index Structures," InstantDoc ID 43939.) One benefit of understanding these columns is that you can use that understanding to adapt sp_MSindexspace if you want different kinds of information than the set it produces. For example, executing this procedure returns a row for each set of column statistics, which don't take up any space. You probably don't want that information if you're investigating space usage. However, sysindexes contains a row for each set of statistics and a row for each index, and sp_MSindexspace doesn't differentiate between the two types of data in the sysindexes table. In SQL Server 7.0, sp_helpindex also retrieves statistics, but Microsoft changed it so that it no longer retrieves statistics in SQL Server 2000. Microsoft didn't update sp_MSindexspace, however, so sp_MSindexspace may return more information than you want or need.
Another change you might want to make to the output of sp_MSindexspace is to include the full size of the clustered index. As the procedure is written, it returns only the size in kilobytes of the index tree above the data. As I've mentioned before, the clustered index's leaf level is the data itself, so you need to consider whether to include the data pages in the computation of the index size. The data pages will take up storage space in your database anyway, so some DBAs just want to know how much extra space the clustered index needs. The full size of the clustered index tree, including the leaf-level data is easier to compute than the value that sp_MSindexspace returns.
To see how you might make that change to the procedure, you need to know about three columns: reserved, used, and dpages. In "Get Into Index Structures," I discussed the difference between reserving space and using it, including the allocation of extents. If SQL Server allocates a uniform extent of eight pages to a table or index, that extent might not be completely used, but sysindexes counts the full eight pages as reserved space. Only the pages that contain data or index rows will count as used. In addition, any index allocation map (IAM) pages for the table or index count as used pages. For the sysindexes row that contains information about the clustered index, the dpages value represents the number of data pages and the used value is the total number of pages for all indexes, including the data in the clustered index's leaf level.
To compute the size of the clustered index tree above the leaf, sp_MSindexspace includes the query that Listing 1 shows. This code takes the value stored in the used column, subtracts the number of actual data pages (dpages), and subtracts the total of all the used pages for the nonclustered indexes. Nonclustered indexes have an indid value of more than 1 but less than 255. If you want to include the clustered index's leaf level, you can remove the dpages column from the second line of Listing 1.
SQL Server Books Online (BOL) is incomplete in its description of the dpages column in sysindexes. For a clustered index, the description is clear, but for nonclustered indexes, BOL gives identical meanings for the dpages and used columns. In sysindexes for any table's nonclustered indexes, these two values are different. The dpages value is the number of pages in the index's leaf level. So for the clustered index, the dpages value is the number of data pages. For nonclustered indexes, the leaf level is separate from the data. In addition to the leaf level index pages, SQL Server always uses at least one additional index page for each nonclustered index because there's always a root page that's separate from the leaf.
A Key Feature
One of the most mysterious columns in sysindexes is the keys column, which contains the index keys. BOL describes this column as a "list of the column IDs of the columns that make up the index key." However, the data stored in the keys column is indecipherable at first glance. SQL Server knows which columns are key columns for any index, and the sp_helpindex procedure lists them, so it must somehow decipher the data in the keys column. However, the code for sp_helpindex provides no clues about how to interpret the data in the keys column. The sp_helpindex procedure uses the index_col() function to extract the names of the index columns. Index_col() is a system function and thus isn't defined in T-SQL, so there's no way to see how this function works. The sp_helpindex procedure needs to know how many columns make up each index, so it calls index_col() for each column in the key. SQL Server needs to know how many columns are in the key, so it uses the sysindexes column called keycnt.
Keycnt seems like it should be a straightforward value: the number of keys in an index. However, the keycnt values in sysindexes might surprise you. Two features of SQL Server's index organization can produce unexpected results. First, clustered indexes have to be unique. If you don't declare an index to be unique, SQL Server makes it unique by adding an extra, hidden key field. Thus, any nonunique clustered index will have a keycnt value that's one greater than the number of columns you supplied in the index definition.
The second feature of index organization is that nonclustered indexes contain the clustered index key as part of the leaf level, and these columns are always part of the keycnt value. So, a nonclustered index will always have as a keycnt value the sum of the number of columns you defined for the index, plus the keycnt value for the clustered index, minus any columns that appear in both the nonclustered and clustered index definitions (unless the table has no clustered index).
Prev. page
[1]
2
next page 
