Generates an allocation statistics report on the specified object in the target database.
sp_dbcc_statisticsreport [dbname [, objectname [, date]]]
specifies the target database. If dbname is not specified, the report contains information on all databases in dbccdb..dbcc_operation_log.
specifies the name of the table or index for which you want the report generated. If you do not specify objectname, Adaptive Server reports statistics on all objects in the target database.
specifies the date on which the dbcc checkstorage operation was performed. If you do not specify date, Adaptive Server uses the date of the most recent operation.
Generates a statistics report on the sysobjects table in the sybsystemprocs database:
sp_dbcc_statisticsreport 'sybsystemprocs', 'sysobjects'
Statistics Report on object sysobjects in database sybsystemprocs Parameter Name Index Id Value ------------------------- -------- ------------ count 0 241.0 max size 0 99.0 max count 0 22.0 bytes data 0 19180.0 bytes used 0 22113.0 count 1 14.0 max size 1 9.0 max level 1 0.0 max count 1 14.0 bytes data 1 56.0 bytes used 1 158.0 count 2 245.0 max level 2 1.0 max size 2 39.0 max count 2 71.0 bytes data 2 4377.0 bytes used 2 6995.0 Parameter Name Index Id Partition Value Dev_name --------------- -------- --------- ------ ------------- page gaps 0 1 13.0 master pages used 0 1 15.0 master extents used 0 1 3.0 master overflow pages 0 1 0.0 master pages overhead 0 1 1.0 master pages reserved 0 1 7.0 master page extent gaps 0 1 11.0 master ws buffer crosses 0 1 2.0 master page extent crosses 0 1 11.0 master pages used 1 1 2.0 master extents used 1 1 1.0 master overflow pages 1 1 0.0 master pages overhead 1 1 1.0 master pages reserved 1 1 6.0 master page extent gaps 1 1 0.0 master ws buffer crosses 1 1 0.0 master page extent crosses 1 1 0.0 master page gaps 2 1 4.0 master pages used 2 1 6.0 master extents used 2 1 1.0 master overflow pages 2 1 0.0 master pages overhead 2 1 1.0 master pages reserved 2 1 2.0 master page extent gaps 2 1 0.0 master ws buffer crosses 2 1 0.0 master page extent crosses 2 1 0.0 master
sp_dbcc_statisticsreport generates an allocation statistics report on the specified object in the target database. It uses data from the dbcc_counters table, which stores information about page utilization and error statistics for every object in the target database.
If sp_dbcc_statisticsreport returns a number for object_name, it means the object was dropped after the dbcc checkstorage operation completed.
sp_dbcc_statisticsreport reports values recorded in the dbcc_counters table for the datatypes 5000–5024. See dbcc_counters in Reference Manual: Tables for more information.
For bytes data, bytes used, and overflow pages, sp_dbcc_statisticsreport reports the sum of the values reported for all partitions and devices.
For count, max count, max size and max level, sp_dbcc_statisticsreport reports the largest of the values reported for all partitions and devices.
sp_dbcc_statisticsreport reports information for each device and partition used by objects in the target database for the following rows:
extents used
io errors
page gaps
page extent crosses
page extent gaps
page format errors
pages reserved
pages overhead
pages misallocated
pages not allocated
pages not referenced
pages used
The page gaps, page extent crosses, and page extent gaps indicate how the data pages for the objects are distributed on the database devices. Large values indicate less effectiveness in using larger buffer sizes and in data prefetch.
If multiple dbcc checkstorage operations were run on a target database on the same day, sp_dbcc_statisticsreport generates a report based on the results of the last dbcc checkstorage operation that finished before the specified time.
The permission checks for sp_dbcc_statisticsreport differ based on your granular permissions settings.
Granular permissions enabled |
With granular permissions enabled, you must be the database owner of dbccdb (or dbccalt), or have the report checkstorage privilege on the specified database. |
Granular permissions disabled |
With granular permissions disabled, any valid user for the database name specified can run sp_dbcc_statisticsreport |
Commands dbcc
dbcc stored procedures sp_dbcc_fullreport, sp_dbcc_summaryreport, sp_dbcc_updateconfig
