INF: Description of sysindexes and sp_spaceused

  - Microsoft SQL Server version 4.2 for OS/2

SUMMARY

The system table sysindexes records the number of pages allocated and used by each table, index, and text or image column in a database. The system procedure sp_spaceused 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.

MORE INFORMATION

Sysindexes will have 1 to 255 rows for each table in the database. All rows for a given table will have the tables object id in their id column. All sysindexes rows for a table can be found with the command:

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

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.

   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.

   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.

Note that the data returned by selects 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 1024 to get the number of kilobytes, which is the value returned by sp_spaceused. The pagesize in spt_values for SQL Server on OS/2 is 2048, so multiplying the values in the sysindexes columns by 2 gives the numbers of kilobytes reported by sp_spaceused.

If the user enters sp_spaceused against a table (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 the user did not enter a table name, then sp_spaceused does not restrict the selects with id = @id, so each SELECT returns rows for all tables in the database. The SELECTs 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 selects are the same except that the id = @id clause is not used.

The following selects show how sp_spaceused calculates the number of pages in each space usage category it reports. sp_spaceused 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

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

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

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

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