SQL Server 2008 Server Edition - Memory Management !!

We have our SQL Server running on Windows 2008 Server R2 Edition with 48 GB of RAM.

Around 20 - 25 users use our Front End Application which communicates the SQL Database.

Apparently our SQL Server is taking too much of memory and keep growing to a level of 40GB of RAM ..!!! This forces us to restart the computer on weekly basis.

How should i set the limit of buffer usage and avoid excessive memory usage ?
chokkaStudentAsked:
Who is Participating?
 
LIONKINGConnect With a Mentor Commented:
The highlighted number is the one you want to change, not the Minimum Memory per query.

Screenshot
0
 
LIONKINGConnect With a Mentor Commented:
If you don't limit the amount of memory SQL can use in the server, it will use all the memory that is available. Of course, it will start "freeing" memory as the server requires it, but you can also put a threshold for this.

You can read these links:

Server Memory Server Configuration Options
http://msdn.microsoft.com/en-us/library/ms178067.aspx

Effects of min and max server memory
http://msdn.microsoft.com/en-us/library/ms180797(v=sql.105).aspx
0
 
chokkaStudentAuthor Commented:
I read all those articles. Has bunch of theory.

This is our third party application using SQL Database.

When i right click on properties and checked the  Maximum Server Memory in (MB) :
2147483647

I am not sure, How the memory is growing beyond 2 GB ?
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
2147483647 MB means 2147483647/1024 GB ...

so, you should change that setting to 10240, for example, to limit the memory consumption to 10GB ...
0
 
LIONKINGCommented:
Like I said, if you leave the windows open for SQL to use all the memory, it will use it.
That value (2147483647 MB) indicates that SQL is free to use "ALL" the available memory on the server.

If you would want to limit the amount of memory that SQL can use, you need to change that number.

1024 MB = 1 GB

You can update that value according to your needs.
0
 
chokkaStudentAuthor Commented:
2147483647 MB / 1024 KB

Change to


2147483647 MB / 10240 KB

i.e Minimum Memory per query (in KB):

10240.

On doing so, we can limit the usage of SQL Memory to 10 GB. Hope this will not crash, if it comes around 10 GB Limit
0
 
chokkaStudentAuthor Commented:
Thank you, Increased to 16 GB which is around 16384 MB. Hope this does not cause any crash !!

Thank you experts !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.