sp_dbcc_statisticsreport

Generates an allocation statistics report on the specified object in the target database.

Syntax

sp_dbcc_statisticsreport [dbname [, objectname [, date]]]

Parameters

dbname – specifies the target database. If dbname is not specified, the report contains information on all databases in dbccdb..dbcc_operation_log.
objectname – specifies the name of the table or index for which you want the report generated. If you do not specify objectname, the SAP ASE server reports statistics on all objects in the target database.
date – specifies the date on which the dbcc checkstorage operation was performed. If you do not specify date, the SAP ASE server uses the date of the most recent operation.

Examples

Example 1 – Generates a statistics report on the sysobjects table in thesybsystemprocs 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

Usage

There are additional considerations when using sp_dbcc_statisticsreport:

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.

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.

Permissions

The permission checks for sp_dbcc_statisticsreport differ based on your granular permissions settings.

Setting	Description
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.
Disabled	With granular permissions disabled, any valid user for the database name specified can run sp_dbcc_statisticsreport