sp_dbcc_statisticsreport

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

Syntax

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

Parameters

Examples

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.

See also:
  • dbcc in Reference Manual: Commands

  • dbcc_counters in Reference Manual: Tables

Permissions

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

SettingDescription
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

Related reference
sp_dbcc_fullreport
sp_dbcc_summaryreport
sp_dbcc_updateconfig