Troubleshooting databases: Improving SQL Server memory management

Recently, in order to be able to run other applications in the database server, the database server has been upgraded on a soft and hardware basis without changing the database operating system version. On the software, upgrade the operating system from Windows 2000 to Windows Server 2003, and on the hardware, increase the memory in the server from the original 512MB to 1GB (1024MB).

During the first few weeks after the upgrade, the server performed well in use. However, it was soon discovered that other applications running concurrently on the server had an exception and occasionally reported a warning of insufficient memory allocation. After several traces, it turns out that SQL Server swallowed most of the memory. The memory consumed by SQL Server is increased from less than 400MB before the upgrade to now 900MB, and there is a growing trend.

You know this is the expected behavior of the SQL Server buffer pool by looking for the reason. By default, after you start SQL Server, SQL Server dynamically increases or shrinks the capacity of the cache memory based on the number of physical memory reported by the operating system. As long as the available physical memory remains between 4MB and 10MB, the SQL Server buffer pool continues to grow (leaving the available physical presence 4MB to 10MB to avoid the operating system changing pages frequently because of lack of memory). If the physical available memory becomes less, SQL Server releases some memory to the operating system.

In order for the applications running on the server to be more satisfying, and to allocate enough memory to other applications, you need to take steps to limit the amount of memory used by SQL Server. We can do this by setting the upper and lower limits of memory used by the SQL Server database engine. The specific steps are:

1. Open Enterprise Manager, expand Server Group.

2. Click the server, right-click, and click the Properties menu.

3. In the pop-up dialog box, click the Memory tab.

There are two ways to set up memory:

1. Set min server memory and max server memory in one scope segment.

For example, we set it to the minimum 0MB, the maximum 255MB. This method is useful when you are allocating memory for multiple applications running on a single server.

2. Set min server memory and max server memory as the same value.

For example, you can set its maximum and minimum values to 255MB. This setting method is consistent with another option in the window, "using fixed memory size."

Although the memory minimum and maximum settings are advanced options, after the setup is complete, it is a good idea to stop and re-run the SQL Server service so that SQL Server can better schedule memory.