1

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

migrated from stackoverflow.com Dec 29 '14 at 19:01

This question came from our site for professional and enthusiast programmers.

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

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 
  • Thanks, I think 12GB is enough for my server. And thanks for the script. – ewlung Dec 29 '14 at 9:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy