sp_dbcc_evaluatedb

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

There are additional considerations when using sp_dbcc_evaluatedb:

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.

See also dbcc in Reference Manual: Commands.

Permissions

The permission checks for sp_dbcc_evaluatedb 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 `manage checkstorage` privilege on the specified database.
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.