I am trying to create the dbccdb database in order to use "dbcc checkstorage" command. I have read all the documentation and there is one step regarding the cache that I don't understand. This is the output for sp_plan_dbccdb xxxdb:
1> sp_plan_dbccdb xxxdb
Recommended size for dbccdb database is 1793MB (data = 1791MB, log = 2MB).
No suitable devices for dbccdb in master..sysdevices.
Recommended values for workspace size, cache size and process count are:
dbname scan ws text ws cache comp mem process count
xxxdb 1180M 295M 295M 0K 2
With this information I understand that I need to create a cache with 295M of size. I made it:
1> sp_cacheconfig dbccdb_cache, "295M"
The change is completed. The option is dynamic and ASE need not be rebooted for the change to take effect.
(return status = 0)
Now I want to create the pool for the 16K pages. I have read that I only need "634K" and I have also read that I should use most of the total space form the cache (200M?).
Somebody know how many space should have the pool? It is correct to use a 295M cache?
Contrary to popular belief, you can actually use the default data cache for dbccdb as well. You do not have to specifically create a data cache for that - especially if available memory is limited.
In your case, you can likely create a 384Mb cache, and allocate about 64 - 128M to the 16K pool. You will probably find that the recommended figures go up slightly after the first actual run.
Bear in mind that a lot of the pages that are cached are actual "allocation pages", and that these will be read into cache as individual pages, i.e. 2K I/O via the smallest pool.
Some info on data caches :
Each ASE has a default cache named 'default data cache'.
For 12.5.x and pre-15.1 versions of ASE, this was defaulted to a very "economical" size of only 8Mb - way to small to be of any use in a production environment. Resize this to the maximum size that you can based on your specific hardware and environment.
In any normal operation, memory is usually a luxury that is not to be wasted. If you allocate memory to a dedicated cache that is only used for the odd times that you may use a specific db, or perform the odd dbcc then I'd rather suggest to use the default data cache for the dbcc, and allocate that extra memory to a cache that you bind to your tempdb. At least it will be properly used there.
You can even create a named cache and bind some of your "hot" (frequently-used) objects to the named cache.
It just translates to more bang for your buck if you optimally use the available memory.
Back to the topic :
Configuring caches is fairly straightforward:
sp_cacheconfig '<name of the cache>', '<size>'
sp_cacheconfig 'tempdb_cache', '500M'
This will create the named cache tempdb_cache with a size of 500Mb. To resize it, use :
By default, memory for additional pools is taken from the smallest pool - 2K for a server with 2K pages, 4K for a server with a 4K page size, etc.
The pools are basically linked memory pages that is sized at 1,2,4 or 8 times the server's configured page size. This translates to a pool size of :
Server Page size, 1 x Page Pool, 2 x Page Pool, 4 x Page Pool, 8 x Page Pool
2K server = 2k, 4K, 8K, 16K
4K server = 4K, 8K, 16K, 32K
8K server = 8K, 16K, 32K, 64K
16K server = 16K, 32K, 64K, 128K
The pool sizes indicates the maximum size of a single disk transfer ( I/O operation) if data is accessed and read into a cache or written to disk from a cache.
There are no right or wrong configs for the page pools here, rather use sp_sysmon or the mon tables to check proper usage of the pools.
sp_sysmon now also includes a cache wizard as of ASE 15.
If you create pools, do not create a pool of 4 x page size AND a pool of 8 x page size. ASE will only use the largest pool available, and ignore the smaller pool. If you have both pools, the memory used by the smaller pool is wasted. Transaction log will typically use a pool of 2 x page size if it's available, so it's fine to have pools of 1 x, 2x and 4x page size, or 1x, 2x, and 8x page size, but not 1x, 2x, 4x and 8x page size pools.
So, I understand that it is not so critical to have a cahe for each database to be used whith checkstorage. Nevertheless, I have enought resources so I am going to create some caches and I am going to use about 50% for a 16K pool. Later I will check with sp_sysmon if the current size is good enought.