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

  • Can you also add the edition of the SQL Server engine please?
    – Ouscux
    Dec 29, 2014 at 8:36
  • It is SQL Server Standard Edition (64 bit), version 10.50.4000.0.
    – ewlung
    Dec 29, 2014 at 8:48

1 Answer 1


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 
EXEC sys.sp_configure N'max server memory (MB)', N'12000'  // Amount of Memory Limit in MegaBytes
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE ";
  • Thanks, I think 12GB is enough for my server. And thanks for the script.
    – ewlung
    Dec 29, 2014 at 9:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.