Solved

SQL Server 2008 Server Edition - Memory Management !!

Posted on 2013-01-11
7
606 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 333 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 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 167 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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 333 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

770 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