SQL Server 2008 R2 – Memory Management on 64-bit OS

memorysql serversql-server-2008-r2

My application server is running SQL Server 2008 R2.
OS is Windows 2008 R2 with SP1.
Total physical memory is 16 GB.

When I looked into the Task Manager, the sqlservr.exe was using "6,734,588 K".

When I looked the Memory Setting, it's all default (see below).

It is obvious that the max memory can go as high as 2,147,483,647,000,000 in BYTES, which is more than the available physical memory.

How much is the recommended setting for typical server like mine?

My application sometimes throw "out of memory" error. I haven't linked that issue with the SQL Server, but I am trying to see what I can do in terms of memory usage.

If I changed the max memory setting, do I need to restart the SQL Server?

enter image description here

Best Answer

If application is not reserved for database engine purposes only, it was recommended to limit it to 60%-70% of the total resources available at the server. Enterprise applications those using open transactions when the application is running, most likely to reach maximum memory usage as it can reserve.

Changing maximum server memory via SSMS does the triggering automatically and no restart of the services needed. Hereby you can also use following script to get it programmatically which also doesn't need a service restart afterwards;

EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE 
GO 
EXEC sys.sp_configure N'max server memory (MB)', N'12000'  // Amount of Memory Limit in MegaBytes
GO 
RECONFIGURE WITH OVERRIDE 
GO 
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE ";
GO
Related Question