The example detailed in “Information on data caches” shows a default data cache with 59.44 MB of cache space available before any user-defined caches are created. The server in this example 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.
Configuring a data cache can appear to increase or decrease the total available cache. The explanation for this lies in the amount of overhead required to manage a cache of a particular size, and the fact that the overhead is not included in the values displayed by sp_cacheconfig.
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 command shows the overhead for the default data cache before any changes were made:
sp_helpcache "59.44M"
4.24Mb of overhead memory will be needed to manage a cache of size 59.44M
This command shows the overhead for pubs_cache:
sp_helpcache "10M"
0.77Mb of overhead memory will be needed to manage a cache of size 10M
The following calculations add the overhead required to manage the original cache space and then subtract the overhead for pubs_cache:
Original total cache size (overhead not included) |
59.44 |
|---|---|
Overhead for 59.44 MB default cache |
+4.24 |
Total cache space, including overhead |
63.68 |
10MB pubs_cache and .53MB overhead |
-10.77 |
Remaining space |
52.91 |
Overhead for 52.95MB cache |
- 3.83 |
Usable size of the default cache |
49.08 |
Cache sizes are rounded to two places when printed by sp_cacheconfig, and overhead is rounded to two places by sp_helpcache, so you will see a small amount of rounding error in the output.
