When you change the memory configuration option, the amount of memory dedicated to the data and procedure caches changes as well. Similarly, when you change the value of the procedure cache option, memory is shifted between the two caches, so each cache has a different size than in the previous configuration.
There is not a one-step method to increase or decrease only the data cache or the procedure cache. This makes it harder to determine how much data cache and procedure cache is best for a particular SQL Server.
To more accurately measure the effects of your changes, it is best to change one cache size at a time. To change only one cache size at a time, use the memory and procedure cache options together, as described in the following example.
Consider a SQL Server configured with 4096 pages (8 MB) of memory, 50 user connections, a 28K stack size, and a total cache of 1084 pages (of which 876 pages is data cache and 208 pages is procedure cache). Now, assume that the main stored procedures need 440 pages of procedure cache to store one copy of each in the cache at one time.
To accommodate the main procedures without significantly changing the size of the data cache, you must increase both the memory and the procedure cache options. The new memory option setting is simply the old setting plus the number of additional pages needed:
Memory = 4096 440 - 208 = 4328 Pages
Because adding memory does not increase the amount of overhead, the total cache space increases to 1316 pages (1084 440 - 208). The new procedure cache option setting is as follows:
Procedure Cache = (Total Cache Space) * (Procedure Cache %)
Because the procedure cache percentage is equal to the desired procedure cache divided by the total cache space, the procedure cache percentage in this example is 30 percent (400 / 1316). These same calculations can be used to increase the data cache and leave the procedure cache constant.
When evaluating performance associated with different cache sizes under Windows NT, you can use the Windows NT Performance Monitor. Of particular interest are the Cache Hit Ratio and Physical I/O statistics. Optimally, you want to maximize Cache Hit Ratio and minimize Physical I/O.