?
Solved

SQL Server 2008 Server Edition - Memory Management !!

Posted on 2013-01-11
7
Medium Priority
?
618 Views
Last Modified: 2013-01-11
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 ?
0
Comment
Question by:chokka
  • 3
  • 3
7 Comments
 
LVL 13

Assisted Solution

by:LIONKING
LIONKING earned 1332 total points
ID: 38768667
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
 

Author Comment

by:chokka
ID: 38768704
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
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 668 total points
ID: 38768726
2147483647 MB means 2147483647/1024 GB ...

so, you should change that setting to 10240, for example, to limit the memory consumption to 10GB ...
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
LVL 13

Expert Comment

by:LIONKING
ID: 38768735
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
 

Author Comment

by:chokka
ID: 38768740
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
 
LVL 13

Accepted Solution

by:
LIONKING earned 1332 total points
ID: 38768758
The highlighted number is the one you want to change, not the Minimum Memory per query.

Screenshot
0
 

Author Comment

by:chokka
ID: 38768772
Thank you, Increased to 16 GB which is around 16384 MB. Hope this does not cause any crash !!

Thank you experts !
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question