If dbccdb does not exist, sp_plan_dbccdb returns:
Minimum size for dbccdb
Devices that are suitable for dbccdb
Minimum sizes for the scan and text workspaces
Minimum cache size
Number of worker processes
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:
Minimum size for dbccdb
Size of existing dbccdb database
Minimum sizes for the scan and text workspaces
Minimum cache size
Number of worker processes
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.
