Shulou(Shulou.com)05/31 Report--

This article is to share with you about how to solve the shortage of SQL Server virtual memory. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

What are the phenomena of insufficient virtual memory in SQLServer

Symptom: on computers with 2GB or more RAM, except for the 256MB (SQLServer7.0) or 384MB (SQLServer2000) virtual address space, SQLServer reserves all remaining virtual address space for use by the buffer pool during startup. In addition, to store data and process caching, SQLServer uses buffer pool memory to service most other memory requests from SQLServer processes that are smaller than 8KB. The remaining unreserved memory is reserved for other allocations that cannot be serviced from the buffer pool. These allocations include, but are not limited to, the following:

The stack and associated thread environment blocks for all threads created by SQLServer. After SQLServer has created all 255 worker threads, this is about 140MB. Allocation by other DLL or processes running in the SQLServer address space, which varies from system to system, such as the OLEDB provider in any linked server. COM objects loaded by using sp_OA system stored procedures or extended stored procedures. Any image (.exe or .dll) loaded into the address space that usually uses 20 to 25MB, but if you use a linked server, sp_OA, or extended stored procedure, these images may use more space. Process heap and any other heap that SQLServer may create. This space is usually 10MB during startup, but it may be more if you use linked servers, sp_OA, or extended stored procedures. Allocations larger than 8KB from SQLServer processes, such as larger query plans, network packet size configuration options close to 8KB send and receive buffers, and so on. To see this number, look for the OSReserve value reported in DBCCMEMORYSTATUS, which is reported as the number of 8KB pages. Typically, this value is 5MB. An array that tracks the status information of each buffer in the buffer pool. This value is usually about 20MB, unless the address windowing extension (AWE) is enabled by the SQLServer runtime, in which case it will increase significantly.

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

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

How to solve the shortage of virtual memory in SQLServer

The following table lists some recommended initial values for the-g value based on the number of databases and the server version: DatabasesSQLServer7.0SQLServer2000

250-g134N/A

500-g185N/A

750-g237N/A

1000-g288-g288

1250-g340-g340

1500-g392-g392

This table is calculated using the typical values listed, and the calculation is based on the assumption that there are no linked server activities, sp_OA, or extended stored procedures. It also assumes that you are not using AWE and SQL profilers. Either of these situations requires you to increase the value of-g.

If the number of databases on the server exceeds this number, Microsoft recommends that you consider carefully before running the server, because the overhead required to have such a number of databases on the system will take up a large amount of virtual memory in the buffer pool, which may lead to overall system performance degradation.

The above is how to solve the shortage of virtual memory in SQL Server. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.