Estimating SQL Server Overhead

The following example shows how memory is allocated on a SQL Server with a memory configuration of 4096 pages (8 MB).

Note All calculations in the following example are approximations.

To estimate SQL Server overhead:

1. Subtract an amount for static server overhead (including the size of the SQL Server executable code) from the total memory available to the SQL Server process.

   Static server overhead, which is typically around 2 MB, is unaffected by configurable options.

   Assuming a static server overhead of 2 MB, the remaining total memory in this example is 6 MB (8 MB - 2 MB).

2. Subtract an amount for configurable options. The configurable options that use significant amounts of memory are:
   • User connections
   • Open databases
   • Open objects
   • Locks
   • Devices

   To calculate approximately how much memory is required by these configurable options, multiply the current sp_configure value for each option by the bytes per resource value for the option (as indicated by DBCC MEMUSAGE). Convert each calculated value from bytes to MB, and then add all the values to determine the total.

   For purposes of this example, assume the configurable overhead is 0.68 MB.

3. Estimate the total cache space by subtracting the static overhead (step 1) and the configurable overhead (step 2) from the total memory. In this example, subtract 2.68 MB (2 MB .68 MB) from 8 MB for a total cache space of about 5.32 MB.
4. Estimate the size of the procedure cache and the data cache. The proportion of the total cache that goes to the procedure cache depends on the run_value of the procedure cache option (as shown by sp_configure). The default value is 20, which indicates to use 20 percent of the total cache space as procedure cache and the remaining 80 percent as data cache.

   If the run_value of the procedure cache option is 20, the total cache space is divided as follows:

   Data Cache = 5.32 * 0.8 = 4.26 MB = 2182 Pages

   Procedure Cache = 5.32 * 0.2 = 1.06 MB = 545 Pages

   (Usually, the amount of procedure cache is slightly higher than the amount indicated in this calculation because the unused portion of the 5 percent miscellaneous overhead is added to the procedure cache.)

Important The amount of overhead is not dependent on the amount of memory available to SQL Server. If you add additional memory to SQL Server using the sp_configure memory option, the total cache space is increased by the amount of memory added. In the example above, if you increase the memory option from 4096 pages to 8192 pages, the total cache space increases by 4096 pages (8 MB) to 13.32 MB (8 MB  5.32 MB).