Effects of Overhead on Total Cache Space

Configuring a data cache can appear to increase or decrease the total available cache. This is due to the amount of overhead required to manage a cache of a particular size; the overhead is not included in the values that are shown by sp_cacheconfig.

The example in Viewing Information About Data Caches, above, shows a default data cache with 59.44 MB of available cache space before any user-defined caches are created. The server uses a 2K logical page. When the 10MB pubs_cache is created, the results of sp_cacheconfig show a total cache size of 59.44 MB.

Using sp_helpcache to check the overhead of the original 59.44MB default cache and the new 10MB cache shows that the change in space is due to changes in the size of overhead. The following example shows the overhead for the default data cache before any changes were made:

sp_helpcache "59.44M"

4.10Mb of overhead memory will be needed to manage a cache of size 59.44M

This example shows the overhead for pubs_cache:

sp_helpcache "10M"

0.73Mb of overhead memory will be needed to manage a cache of size 10M

This example shows the overhead for a cache size of 49.44MB:

sp_helpcache "49.44M"

3.46Mb of overhead memory will be needed to manage a cache of size 49.44M

4.19MB (which is equal to .73MB + 3.46MB) is the required overhead size for maintaining two caches of size 10MB and 49.44MB, and is slightly more than the 4.10MB overhead than is necessary to maintain one cache of 59.44MB.

Cache sizes are rounded to two places when printed by sp_cacheconfig, and overhead is rounded to two places by sp_helpcache, so the output includes a small rounding discrepancy.