Solved

SQL Server 2008 Server Edition - Memory Management !!

Posted on 2013-01-11
7
605 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now