SQL Server is a powerful database management system that requires careful configuration to ensure optimal performance. One important aspect of SQL Server configuration is memory allocation. In this article, we will discuss the concept of over-committing memory and how to address it when configuring the “max server memory” setting.

What is Over-Committing Memory?

Over-committing memory occurs when the value defined for “max server memory” (RAM) is not physically present on the server. This can lead to issues with SQL Server services not starting or functioning properly.

Configuring AWE for SQL Server

In order to access physical memory above the 4 GB limit, SQL Server requires the use of the /PAE switch in the Windows Boot.INI configuration file. This is a requirement for AWE (Address Windowing Extensions) memory access above 4 GB. Any changes to the Boot.INI file will require a server restart.

To enable AWE on SQL Server, you can use the system stored procedure “sp_configure” from the query analyzer. Before enabling AWE, make sure to grant the LOCK PAGE IN MEMORY permission to the SQL Server account. This permission may be enabled if Windows 2003 is on SP1.

sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'min server memory', 1024
RECONFIGURE
GO
sp_configure 'max server memory', 30720
RECONFIGURE
GO

In the above example, the “max server memory” setting was mistakenly set to 307200 MB instead of the intended 30720 MB (30 GB).

Resolving Over-Committing Memory Issues

If you encounter issues with SQL Server not starting after configuring the “max server memory” setting, you can work around the problem by starting SQL Server in minimal configuration mode using the “sqlservr.exe” command with the “-c -f” options.

For a SQL Server 2000 Default Instance:

sqlservr.exe -c -f

For a SQL Server 2000 Named Instance:

sqlservr.exe -c -f -s Instance_Name

After starting SQL Server in minimal configuration mode, connect to SQL Server through Query Analyzer and reconfigure the “max server memory” setting to the correct value.

sp_configure 'max server memory', 30720
RECONFIGURE
GO

Once the correct configuration is in place, shut down the SQL Server instance by pressing CTRL+C in the command prompt and typing “Y”. When you start SQL Server again, it should come up fine and the “max server memory” issue will be resolved.

Remember to always double-check your memory configuration settings to avoid over-committing memory and ensure optimal performance for your SQL Server.

Feedback is highly appreciated.