Insufficient SQL Server Virtual Memory

Insufficient SQL Server Virtual Memory
Symptom
On a computer with 2 GB or more RAM, except for the 256 MB (SQL Server 7.0) or 384 MB (SQL Server 2000) virtual address space, SQL Server retains all the remaining virtual address space during startup for the buffer pool. In addition, to store data and process cache, SQL Server uses the buffer pool memory to provide services for most memory requests less than 8 KB from SQL Server Processes. The remaining reserved memory preparation is used to prevent other service allocations from being obtained from the buffer pool. These distributions include, but are not limited to, the following: • stacks of all threads created by SQL Server and associated thread environment blocks. After SQL Server creates all 255 worker threads, this is about 140 MB.

• Allocated by other DLL or processes running in the SQL Server address space (depending on the system), such as: • OLE DB Provider in any linked Server.

• COM objects loaded by using sp_OA stored procedures or extended stored procedures.

• Maps to any image (.exe or. dll) in the address space. These images usually use 20 to 25 MB, but if you use the linked server, sp_OA, or extended stored procedure, these images may use more space.

• Process heap and any other heap that SQL Server may create. During startup, this space is usually 10 MB, but if you use the linked server, sp_OA, or extended stored procedure, this space may be more.

• Distribution from SQL Server Processes larger than 8 KB, such as the allocation required for large query plans and network packet size configurations that are close to sending and receiving buffers when 8 KB occurs. To view this number, find the OS Reserved value reported in DBCC MEMORYSTATUS, which is reported as the number of 8 KB pages. Generally, the value is 5 MB.

• An array that tracks the status information of each buffer in the buffer pool. This value is usually about 20 MB, unless the address window extension (AWE) is enabled when SQL Server is running, in which case this value will increase significantly.

On systems with a large number of databases, the 64 KB allocation required for log formatting may occupy all the remaining virtual memory. The subsequent allocation will fail, resulting in one or more errors listed in the "symptoms" section in this article.

By using the-g startup parameter, You can instruct SQL Server to reserve the additional virtual memory available so that these log-related allocations and other normal allocations will not use up the virtual address space.

The following table lists the recommended initial values of-g based on the number of databases and Server versions: DatabasesSQL Server 7.0SQL Server 2000.

250-g134N/

500-g185N/

750-g237N/

1000-g288-g288

1250-g340-g340

1500-g392-g392

This table is calculated using the list of typical values, and this calculation is based on the assumption that no link is used for server activity, sp_OA, or extended stored procedure. It also assumes that you have not used AWE and SQL event probes. In any of the above cases, you need to add the-g value.

If the number of databases on the server exceeds this value, Microsoft recommends that you carefully consider the number before running the server, because the system overhead required for such a number of databases on the system will occupy a large amount of virtual memory in the buffer pool, which may cause the overall system performance to decline.

: Create SQL Server2000 security policies