sp_dbcc_evaluatedb

Description

Recomputes configuration information for the target database and compares it to the current configuration information.

Syntax

sp_dbcc_evaluatedb [dbname]

Parameters

dbname: specifies the name of the target database. If you do not specify dbname, sp_dbcc_evaluatedb compares all databases listed in the dbcc_config table.

Examples

Example 1

Recomputes configuration information for the current database, sybsystemprocs, and suggests new values for some parameters:

1> sp_dbcc_evaluatedb
2> go

Recommended values for workspace size, cache size and process count are:
 
Database name : one_G
                                      current         suggested
scan workspace size :                    750M               16M
text workspace size :                      2K               48K
cache size          :                  10240K             1280K
process count       :                       3                 2
compression mem size:                   2048K               12M
 
Each of the reported quantities is reported in a scaled unit according to
G if size > 10G
M if 10M < size <=10 G
K otherwise

Usage

When there is an archive database with a compressed data or log device, the output includes a line with the recomendation of the compression memory size.

sp_dbcc_evaluatedb recomputes configuration information for the target database and compares the data to the current configuration information. It uses counter values recorded for the target database in the dbcc_counters table.

The cache size is the size of the 16K buffer pool in the cache. For a 2K buffer pool, the minimum size of this cache must be the recommended value, plus 512.

When the size and data distribution pattern of the target database changes, run sp_dbcc_evaluatedb to optimize the configuration information.

To gather configuration information for the target database the first time, use sp_plan_dbccdb.
To make sure you are evaluating the most current configuration parameters, run sp_dbcc_updateconfig before running sp_dbcc_evaluatedb.

Permissions

The permission checks for sp_dbcc_evaluatedb 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 manage checkstorage privilege on the specified database.
Granular permissions disabled	With granular permissions disabled, you must be the database owner of the specified database, a user with sa_role. Only a System Administrator can run sp_dbcc_evaluatedb without specifying a database name.