Examples of sp_plan_dbccdb output

If dbccdb does not exist, sp_plan_dbccdb returns:

The values recommended for the cache size are approximate because the optimum cache size for dbccdb depends on the pattern of the page allocation in the target database. The following example from a server that uses 2K logical pages shows the output of sp_plan_dbccdb for the pubs2 database when dbccdb does not exist:

sp_plan_dbccdb pubs2
Recommended size for dbccdb is 4MB.

Recommended devices for dbccdb are:

Logical Device Name   Device Size   Physical Device Name

sprocdev              28672         /remote/SERV/sprocs_dat
tun_dat               8192          /remote/SERV/tun_dat
tun_log               4096          /remote/SERV/tun_log

Recommended values for workspace size, cache size and process count are:

dbname        scan ws    text ws    cache    process count

pubs2         64K         64K        640K     1

(return status = 0)

If dbccdb already exists, sp_plan_dbccdb returns:

The following example shows the output of sp_plan_dbccdb for the pubs2 database when dbccdb already exists:

sp_plan_dbccdb pubs2
Recommended size for dbccdb database is 23MB (data = 21MB, log = 2MB).
dbccdb database already exists with size 8MB.
Recommended values for workspace size, cache size and process count are:
dbname                            scan ws    text ws    cache    process count 
pubs2                             64K        48K        640K     1
(return status = 0)

If you plan to check more than one database, use the name of the largest one for the target database. If you do not provide a target database name, sp_plan_dbccdb returns configuration values for all databases listed in master..sysdatabases, as shown in the following example:

sp_plan_dbccdb
Recommended size for dbccdb is 4MB.

dbccdb database already exists with size 8MB.

Recommended values for workspace size, cache size and process count are:

dbname           scan ws    text ws    cache    process count

master           64K        64K        640K     1 
tempdb           64K        64K        640K     1 
model            64K        64K        640K     1 
sybsystemprocs  384K       112K       1280K     2 
pubs2            64K        64K        640K     1 
pubs3            64K        64K        640K     1 
pubtune         160K        96K       1280K     2 
sybsecurity      96K        96K       1280K     2 
dbccdb          112K        96K       1280K     2

For more information, see “sp_plan_dbccdb” in the Reference Manual.