Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL Server 2008 Server Edition - Memory Management !!

Posted on 2013-01-11
7
607 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 143

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

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.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

856 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