INF: Description of sysindexes and sp_spaceused (90758)

MORE INFORMATION

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:

SELECT *
FROM sysindexes
WHERE id = object_id('table_name')
				

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:

   dpages   = Number of data pages, excluding text and image columns.
   reserved = Number of pages reserved for dpages and ALL indexes.
   used     = Number of pages used for dpages and ALL indexes.
   rows     = Number of rows in the dpages.
				

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:

   dpages   = Number of data pages, excluding text and image columns.
   reserved = Number of pages reserved for the dpages and ALL indexes.
   used     = Number of pages used for dpages and ALL indexes.
   rows     = Number of rows in the dpages.
				

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:

   reserved = Number of pages reserved for all text or image data in the 
              table (all columns).
   used     = Number of pages used for all text or image data in the table 
              (all columns).
   dpages and rows will be 0.
				

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.

#rows=

 SELECT rows
 FROM sysindexes
 WHERE indid < 2 and id = @id


#reserved=

 SELECT sum(reserved)
 FROM sysindexes
 WHERE indid in (0, 1, 255) and id = @id


#data=

 (SELECT sum(dpages)               +    (SELECT sum(used)
  FROM sysindexes                        FROM sysindexes
  WHERE indid < 2 and id = @id)          WHERE indid = 255 and id = @id)


#index_size=

 (SELECT sum(used)
  FROM sysindexes
  WHERE indid in (0, 1, 255) and id = @id)     -     #data


unused=

 #reserved        -     (SELECT sum(used)
                         FROM sysindexes
                         WHERE indid in (0, 1, 255) and id = @id)
				

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: