Troubleshoot SQL Server virtual memory _mssql

Troubleshooting SQL Server Virtual memory problem
Symptoms
On computers with 2 GB or more of RAM, in addition to the 256 MB (SQL Server 7.0) or 384 MB (SQL Server 2000) virtual address space, SQL Server retains all remaining virtual address spaces during startup to allow the buffer pool to Use. In addition, to store data and process caching, SQL Server uses buffer pool memory to service most other memory requests from SQL Server processes that are less than 8 KB. The remaining reserved memory is ready for other allocations that cannot be serviced from the buffer pool. These allocations include, but are not limited to, the stack of all threads created by SQL Server and the associated thread environment block. This is approximately 140 MB after SQL Server has created all 255 worker threads.

• Allocations by other DLLs or processes running in the SQL Server address space (different from the specific system), such as: • OLE DB providers in any linked server.

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

• Any images (. exe or. dll) that are loaded into the address space that typically use 20 to MB, but may use more space if you are using linked servers, sp_OA, or extended stored procedures.

• Process heap and any other heap that SQL Server may create. This space is typically ten MB during startup, but this space may be more if you are using linked servers, sp_OA, or extended stored procedures.

• Allocations that are greater than 8 KB from the SQL Server process, such as those required for large query plans, network packet size configuration options that are close to 8 KB for sending and receiving buffers, and so on. To view this number, look for the OS Reserved value reported in DBCC Memorystatus, which is reported as the number of 8 KB pages. Typically, this value is 5 MB.

• An array of trace state information for each buffer in the buffer pool. This value is typically approximately MB unless the Address Windowing extension (AWE) is enabled by SQL Server runtime, in which case the value is significantly increased.

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

By using the-G startup parameter, you can instruct SQL Server to keep additional virtual memory available so that these log-related allocations and other normal allocations will not run out of virtual address space.

The following table contains some recommended initial values for the-G value based on the number of databases and the server version: Databasessql Server 7.0SQL Server 2000

250-g134n/a

500-g185n/a

750-g237n/a

1000-g288-g288

1250-g340-g340

1500-g392-g392

This table is computed using the typical values listed, and this calculation is based on the assumption that no linked server activity, sp_OA, or extended stored procedure is used. It also assumes that you are not using AWE and SQL Profiler. Any one of these situations requires that you increase the value of-G.

If the number of databases on the server exceeds this number, Microsoft recommends that you consider this server carefully before you run it, because the system overhead required for such a number of databases on your system will consume a large amount of virtual memory in the buffer pool, which may result in overall system performance degradation.

: Creating a security policy for SQL Server2000