Detailed notes on reviewing existing DBCCDB for use with ‘check storage’
DBCCDB review
First, find out what ASE recommends you set the minimum values to using
> sp_plan_dbccdb <dbname>
Then, we work through these values and we pay attention to the settings below.
- check size of dbccdb
- check size of dbccdb_cache
- check scan ws value
- check text ws value
- check ‘process count’
Example:
> sp_plan_dbccdb NOVIA_PROD_ETL_MI_CompStat Recommended size for dbccdb database is 6158MB (data = 6156MB, log = 2MB). dbccdb database already exists with size 10000MB. Recommended values for workspace size, cache size and process count are: dbname scan ws text ws cache comp mem process count NOVIA_PROD_ETL_MI_CompStat 4081M 1021M 1021M 0K 9
Check size of dbccdb
Run ‘sp_helpdb dbccdb’ to see if the database is at least 6158MB data and 2MB log. In our example, the dbccdb was 9500MB data/500MB log, so no changes needed.
Check size of dbccdb_cache
The recommendation above says ‘1021M’ under the ‘cache’ column.
Use ‘sp_cacheconfig’ to check the value
1> sp_cacheconfig 2> go Cache Name Status Type Config Value Run Value ------------------ -------- -------- -------------- ------------ dbccdb_cache Active Mixed 1000.00 Mb 1000.00 Mb
In this example we have a dbccdb_cache of 1000MB, so we should increase this (round up to allow some headroom) so, let’s increase to 1100MB.
sp_cacheconfig 'dbccdb_cache', '1100M' sp_poolconfig 'dbccdb_cache', '1099M', '16k' -- adjust the 16k pool to use the additional memory
Test your values have been changed:-
> sp_poolconfig 'dbccdb_cache'
should display something like this
IO Size Wash Size Config Size Run Size scan_wsAPF Percent -------- ------------- ------------ ------------ ----------- 2 Kb 204 Kb 0.00 Mb 1.00 Mb 10 16 Kb 61440 Kb 1099.00 Mb 1099.00 Mb 10
Check scan ws value, text ws & max worker processes
Find the current settings for scan ws, text ws & max worker processes using the stored proc below
> sp_dbcc_configreport NOVIA_PROD_ETL_MI_CompStat Reporting configuration information of database NOVIA_PROD_ETL_MI_CompStat. Parameter Name Value Size database name NOVIA_PROD_ETL_MI_CompStat 348160000K dbcc named cache dbccdb_cache 643072K scan workspace scan_ws (id = 1440005130) 4177920K text workspace text_ws (id = 1472005244) 1044480K OAM count threshold 5% operation sequence number 51 max worker processes 2 IO error abort 3 linkage error abort 8
The sp_plan_dbccdb suggested scan workspace of 4081M. We have it set to 4080M(4177920/1024)
The sp_plan_dbccdb suggested text workspace of 1021M. we have it set to 1020M (1044480/1024)
The sp_plan_dbccdb suggested worker process of 9. We have it set to 2
If we had wanted to change the workspace values (we don’t need to), we could use the commands below.
sp_dbcc_alterws dbccdb, scan_ws, '4250M' sp_dbcc_alterws dbccdb, text_ws, '1100M'
However, the ‘max worker processes’ value does appeat to be incorrect, so we need to change this
exec sp_dbcc_updateconfig NOVIA_PROD_ETL_MI_CompStat, 'max worker processes', '9'
dbcc Named Cache size
Output from the command below will recommend a cahce size to be assigned for use by the database. However,, there is some debate on how this is used or if the values are important. Rob Verschoor suggests “any size seems fime, regardless of teh actual cache size”. In the output below the current value is 628MB and the suggested value is just 5760K (5.6MB)
1> sp_dbcc_evaluatedb NOVIA_PROD_ETL_MI_CompStat 2> go Recommended values for workspace size, cache size and process count are: Database name : NOVIA_PROD_ETL_MI_CompStat current suggested scan workspace size : 4250M 4081M text workspace size : 1100M 351M cache size : 628M 5760K process count : 9 9
