Determining the optimal memory configuration for a SQL Server solution is crucial to achieving stellar performance. SQL Server uses memory for its procedure cache, data and index page caching, static server overhead, and configurable overhead. SQL Server can use up to 2 GB of virtual memory (the maximum configurable value). In addition, Windows NT and all of its associated services also require memory.
Windows NT provides each 32-bit application with a virtual address space of 4 GB. This address space is mapped by the Windows NT virtual memory manager (VMM) to physical memory and can be 4 GB in size depending upon the hardware platform. The SQL Server application only knows about virtual addresses; it cannot access physical memory directly. Physical memory is controlled by the VMM. In addition, Windows NT allows for the creation of virtual address space that exceeds the available physical memory. Therefore, it is possible to adversely affect the performance of SQL Server by allocating more virtual memory than there is available physical memory.
To make a suitable estimate for an optimal memory configuration, refer to the following table for SQL Server configurable and static overhead memory requirements.
|
|
| Bytes per resource |
|
User connections | Yes | 25 | 40,960 | 0.43 |
Open databases | Yes | 10 | 650 | 0.01 |
Open objects | Yes | 5,000 | 72 | 0.04 |
Locks | Yes | 5,000 | 28 | 0.13 |
Devices | No | 256 | 300 | 0.07 |
Static server overhead | No | N/A | 2,000,000 | 2.00 |
Total overhead | 2.68 | |||
You can use this information to calculate a more exact memory-configuration estimate with respect to actual memory usage. This is done by taking the calculated total overhead above and applying it to the following formula:
SQL Server physical memory – total overhead = SQL Server memory cache
The SQL Server memory cache is the amount of memory that is dedicated to the procedure cache and the data cache.
The procedure cache is the amount of the SQL Server memory cache that is dedicated to the caching of stored procedures, triggers, views, rules, and defaults. Consequently, if your system takes advantage of these data objects and the stored procedures are used by many users, then this value should be proportional to such requirements. These objects are stored in the procedure cache based on the frequency of their use; you want the most-used data objects to be accessed in cache versus retrieved from disk. The system default for procedure cache is 30 percent of the available memory cache.
The data or buffer cache is the amount of the SQL Server memory cache that is dedicated to the caching of data and index pages. These pages are held in the data cache based on the frequency of their use; you want the most-used data and index pages to be accessed in cache versus retrieved from disk. The system default for data cache is 70 percent of the available memory cache.
The following example for a dedicated SQL Server illustrates a more accurate estimate of SQL Server memory requirements.
Note Although the following table is a good place to start, remember that each system's needs are different. You may need more than 16 MB of physical memory on your Windows NT machine.
Resource | Estimated value | Bytes per resource | Space (MB) |
User connections | 50 | 40,960 | 0.9 |
Open databases | 10 (default) | 650 | 0.01 |
Open objects | 5,000 (default) | 240 | 0.04 |
Locks | 15,000 | 60 | 0.42 |
Devices | 256 (default) | 34 | 0.07 |
Static server overhead | N/A | 2,000,000 | 2.0 |
Total overhead | 3.44 | ||
If total physical system memory equals 48 MB (Windows NT physical memory equals 16 MB and SQL Server physical memory equals 32 MB), and you subtract the previous overhead figure (3.44) from the SQL Server physical memory, you will have approximately 28 MB total memory cache to work with on the SQL Server (procedure cache: 28.56 * 0.3 = 8.568 MB and data cache: 28.56 * 0.7 = 19.992 MB).
As overhead requirements, such as user connections and locks, increase, this value decreases, which may lead to performance problems that require tuning.