INF: Description of sysindexes and sp_spaceused

The sysindexes system table records the number of pages allocated and used by each table, index, and text or image column in a database. The sp_spaceused system stored procedure reports this information to users. This article explains some of the data in sysindexes and how it is used by sp_spaceused to calculate the values it reports.

The sysindexes table will have 1 to 255 rows for each table in the database. All rows for a given table will have the table's object ID in their ID column. All sysindexes rows for a table can be found with the following query: The sysindexes rows for a table fall into the following categories: Every table without a clustered index has a row with name = table_name and indid = 0. In this row: A table with a clustered index has no row with name = table_name. There is instead a row with name = clustered_index_name and indid = 1. In this row: A table with at least one text or image column will have a row with name = table_name prefixed with "t" (as in ttable_name), and indid = 255. In this row: Every nonclustered index has a row with name = index_name and indid between 2 and 250. The values in these rows for dpages, reserved, used, and rows are not used by sp_spaceused. For additional information on index IDs, click the article number below to view the article in the Microsoft Knowledge Base: Note that the data returned by SELECT statements on sysindexes is in numbers of pages. The number of pages is multiplied by the pagesize from spt_values to get the number of bytes in the pages, then divided by 1,024 to get the number of kilobytes, which is the value returned by sp_spaceused. The pagesize in spt_values for SQL Server 6.5 and earlier is 2,048, so multiplying the values in the sysindexes columns by two gives the numbers of kilobytes reported by sp_spaceused.

If you run sp_spaceused against a table, as in exec sp_spaceused table_name, it will select only the sysindexes rows that relate to the table by first setting @id = object_id('table_name') and then using (id = @id) in the WHERE clause to restrict the selects to the table named. If you did not enter a table name, then sp_spaceused does not restrict the SELECT statements with id = @id, so each SELECT returns rows for all tables in the database. The SELECT statements given here are in the form that is used if a table name is specified. If sp_spaceused is issued against the entire database, the SELECT statements are the same except that the id = @id clause is not used.

The following SELECT statements show how sp_spaceused calculates the number of pages in each space usage category it reports. It then adjusts these numbers using the algorithm above to determine the numbers of kilobytes it reports for each category. NOTE: To reduce system overhead, sysindexes is not updated until a checkpoint. If you need to get a current report, issue a CHECKPOINT command before issuing sp_spaceused or selecting from sysindexes.

For the most thorough update of sysindexes (or if you suspect that the information in sysindexes is incorrect), run DBCC UPDATEUSAGE or use the optional "@updateusage=true" parameter for sp_spaceused. Note that DBCC UPDATEUSAGE may take a long time to run and can consume a significant amount of system resources. Also, the dpages values in sysindexes are not guaranteed to be accurate. For additional information, click the article number below to view the article in the Microsoft Knowledge Base: