Microsoft KB Archive/90758

= INF: Description of sysindexes and sp_spaceused =

Article ID: 90758

Article Last Modified on 11/14/2003

-

APPLIES TO


 * Microsoft SQL Server 4.21a Standard Edition
 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q90758



SUMMARY
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.



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 =  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 =. There is instead a row with 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 =  prefixed with &quot;t&quot; (as in t ), 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 =  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:

75191 INF: Explanation of Index ID's Found in SYSINDEXES

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, it will select only the sysindexes rows that relate to the table by first setting @id = object_id(' ') 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.
 * 1) rows=

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


 * 1) reserved=

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


 * 1) data=

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


 * 1) index_size=

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

unused=

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.
 * 1) reserved       -     (SELECT sum(used)

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 &quot;@updateusage=true&quot; 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:

183100 PRB: Incorrect Log Size Reported in SEM or Performance Monitor

Additional query words: system tables stored procedures

Keywords: kbinfo kbusage KB90758

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.