29
Dec

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

Leave a Reply

Your email address will not be published. Required fields are marked *