Skip to Content
avatar image
Former Member

named cache smaller than bound object

hi folks.

we are on version:

Adaptive Server Enterprise/15.5/EBF 19902 SMP ESD#5.1/P/x86_64/Enterprise Linux/asear155/2594/64-bit/FBO/Wed Jun 6 01:20:27 2012

we use 2K pages

i have an 8 Gb tempdb database and a 5 Gb tempdb_cache with 3 Gb 2K pool and 2 Gb 16K pool. is it ok to have a cache smaller than a bound database ? do you think my 16K pool is oversized ?

thanks !

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Jan 13, 2017 at 07:28 PM

    Is it ok to have a cache smaller than the database that's bound to it? Yes, No, It depends ...

    - Yes; keep in mind that by default all databases are bound to the default data cache, with the default data cache usually being smaller than the database(s) bound to it

    - No; if you routinely fill up your cache(lets) you may find the excessive thrashing of your tempdb cache degrades performance; in your case, depending on which pool is used, could mean as little as 2-3GB of activity could lead to excessive thrashing of your tempdb cache

    - It depends; if you rarely generate enough activity to fill your tempdb cache and/or reads from disk into your tempdb cache are relatively small then you're likely ok; if you routinely fill the tempdb cache and/or see large volumes of reads from disk into your tempdb cache then your cache (or pools) are likely sized too small

    monDataCache and monDeviceIO can help with evaluating the performance of your tempdb cache (eg, too many physical reads/writes, performance of said reads/writes) to determine if your current config is sufficient.

    -------------

    Is the 16KB pool oversided? *shrug*

    You'll need to monitor pool usage during the various types of activity in your tempdb (eg, 'normal' user activity during biz hours ... vs ... after-hours/overnight/batch activity ... vs ... maintenance activities ... vs ... ???).

    I'd suggest you monitor the monCachePool table for telltale signs of needing to reconfigure your pools. [Keep in mind that you may want/need to reconfigure pools for different types of activity, eg, have 1 set of configs for routine user activity and perhaps another set of configs for maintenance activities, ymmv.]

    -------------

    Obviously (?) sizing a cache larger than it needs to be can lead to a waste of (relatively expensive) memory that might be of better use else where.

    I was at a client a few years back where the tempdb cache was sized too small with the net result being that normal user/day-to-day activity was suffering from excessive disk writes/reads on the tempdb devices. MDA tables showed upwards of 60% of all dataserver disk activity was on the tempdb devices (due to the tempdb cache being too small). By shifting a couple GB of memory to the tempdb cache I was able to eliminate roughly half of the tempdb disk activity, and of course improve overall query performance for users utilizing tempdb.

    NOTE: Another (approx) half of tempdb disk activity (for a total of ~99%) was eliminated by disabling the HK in the tempdb cache.

    There is no one-size-fits-all configuration for tempdb ... you'll need to size your cache(s) (and pools) based on the activity in your environment ...

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 13, 2017 at 08:46 PM

    cache size smaller than database size bound to it is NORMAL (default cache come to mind as one bound to all databases)

    ( If you had much memory to spare, you would rather go for in-memory database, especially for temporary databases)

    For databases SAP/Sybase recommendations have been three pool sizes :

    1 x page size pool (2k pool for you)

    2 x page size for transaction logs (4k in your case) and

    8 x page size for large i/o (1 extant = 8 pages read ahead where applicable, 16k in your case)

    For sizing caches and pools you will have to watch various monTable counters and sysmon outputs.

    HTH

    Avinash

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 16, 2017 at 10:38 AM

    You can create the named cache smaller to the database size depending upon the memory available with the ASE server. This will be helpful keeping the data in cache (memory).

    Regarding the large IO pool, This is mostly used for Queries such as select into , create index commands, Bulk copy operations, update statistics, dbcc checktable, and dbcc checkdb commands.

    You can monitor the cache usage using sp_sysmon command . If you are not running the commands using large IO , then you should adjust the pool size accordingly.

    Hope this is helpful.

    ~Gaurav Jowhry

    Add comment
    10|10000 characters needed characters exceeded