[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How can I prevent SQL Server 2008 from exceeding the maximum memory?

Posted on 2011-05-10
21
Medium Priority
?
569 Views
Last Modified: 2012-05-11
Our SQL server (2008) is getting slower and slower after some time.
It seems that SQL is using all the memory.

The server itself has 12Gb of memory.
The SQL server uses 11Gb after a while (and doesn't release any memory to the system)

I have set the max memory setting in SQL to 8Gb but the server keeps using 11Gb.
How can I prevent this behaviour and limit it to 8Gb?

ps: all servicepacks have been applied.

Best regards,
Clyde
0
Comment
Question by:Clyde_Radcliffe
  • 9
  • 8
  • 2
  • +2
21 Comments
 
LVL 31

Expert Comment

by:merowinger
ID: 35727070
Hm?! This option should to it. Have you rebooted the Server!?
http://searchsqlserver.techtarget.com/tip/Configuring-SQL-Server-memory-settings
0
 

Expert Comment

by:Aquinos
ID: 35727104
Is it better only restart the SQL Server
However may be the problem eas not in the server but in the tables indexes
Did you have made a maintenance plan to the databases ?
0
 

Author Comment

by:Clyde_Radcliffe
ID: 35727128
@merowinger: The max memory setting was already set to 8Gb. The server has been rebooted but somehow SQL seems to ignore the setting.

@Aquinos: Haven't done that yet. I'll give it a try but I can't understand why SQL keeps lurking up all memory although I have set it to a maximum of 8Gb.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 3

Expert Comment

by:GSGDBA
ID: 35727135
Hi,
Not problem with sql server.
Check for your paging settings on the sql box.
0
 

Expert Comment

by:Aquinos
ID: 35727160
We need to think like that!
If the SQL need that memory he gonna pick it, right ?

So...

If you have a bigger database with a lots of users, and any replication, and the log file in the same server....

The users try to open the tables and (re)create the indexes.
If you don't shrink it it keep growing and growing
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 35727204
>> I have set the max memory setting in SQL to 8Gb but the server keeps using 11Gb.

I Seriously doubt it..
Just follow the steps specified below:

1. Right Click your Server in SSMS(after connecting it)
2. Choose Properties -> Click Memory
3. Make sure Maximum Memory field is set to 8000 MB ( Not that the default is MB).

Kindly confirm the value which is set now.
0
 

Author Comment

by:Clyde_Radcliffe
ID: 35727228
@rrjegan17:

AWE is turned off.
Min memory is 0 MB.
Max memory is set to 8096 MB.  (however sql is currently using 10.4Gb and growing)
Index creation memory is set to 0 KB.
Minimum memory per query is set to 1024 KB.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 35727854
>> Min memory is 0 MB.

Its recommended to set Min Memory to at least 2048 MB or 4096 MB.

>> Max memory is set to 8096 MB.

Ok, Try restarting the SQL Server Service once the Min Memory is set..
Ideally Restarting is not required but a try to see what happens once.
0
 

Author Comment

by:Clyde_Radcliffe
ID: 35769968
@rrjegan17: I have changed the settings and restarted the server. SQL still uses 11Gb of memory. (so there's 1Gb left for the system)
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 35775058
Seems like that you have more than one instance of SQL Server installed in your machine..
Kindly post a snapshot of the list of services running in SQL Server Configuration manager to confirm it..

An instance of SQL Server will never use memory allocated / set by Max Server Memory but other instances can also use it..
0
 

Author Comment

by:Clyde_Radcliffe
ID: 35775203
SQL Configuration Manager Task Manager SQL Server properties
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 35775814
Try applying the latest SP once for both SQL Server as well as OS and try it out.
0
 

Author Comment

by:Clyde_Radcliffe
ID: 35775910
@rrjegan17: All service packs and updates have been applied.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 35776070
Can you post the result of the query below once:

select *
from sys.configurations
where name like '%server memory%'
0
 

Author Comment

by:Clyde_Radcliffe
ID: 35776382
Query
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 35779414
As you see, the value_in_use for Maximum memory is still on 8096..
Therefore, do the following things to nail down where exactly the issue is:

1. Open task Manager -> Click View -> Select Columns
2. Choose Memory - working set, Memory - Peak working set, Memory - Commit size, etc.,
3. Now, you can see the difference that SQL server process is still using 8096

So, I hope the issue is that Virtual memory is not set in your system.
Kindly configure your Virtual Memory by following the steps below:

1. Right click My Computer -> Properties
2. Choose Advanced System Settings
3. Choose Advanced -> Click Settings -> Click Advanced menu again
4. Under Virtual memory -> Click Change
5. Make sure that you have Virtual memory set more  than the recommended value.
Rule of Thumb is that Virtual Memory = 1.5 * Physical Memory
Eg: If you have 4 GB of RAM, set Virtual memory to 6 GB

Restart your machine and try once.
0
 

Author Comment

by:Clyde_Radcliffe
ID: 35783537
@rrjegan17: Virtual memory was set to Automaticcly manage. I have changed this to manual and set it to 18432. (12288 * 1.5) I'm currently unable to reset the server but I'll let you know the results. Thanks for all the help. I appreciate it!
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 35784254
Sure, let me know if you face any issues so that I can help you out.
0
 

Author Comment

by:Clyde_Radcliffe
ID: 36009310
I have done a reset but the memory keeps lurking up all the memory.
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 2000 total points
ID: 36010528
Hope you are observing the Memory used value from Task Manager..
Since Virtual Memory can also be used in combination with your Physical Memory, there are chances that you might see a higher value than the allocated one..
Just run the query to see how much RAM is exactly used by SQL Server instance by looking into run_value column.

EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE

EXEC sp_configure 'max server memory (MB)'

If run_value shows values higher than config_value, then it is definitely an issue and can be reported to Microsoft..
0
 

Author Closing Comment

by:Clyde_Radcliffe
ID: 36145682
I have reported the issue to Microsoft. Although the problem still exists all the points  are rwarded to rrjegan17 for al l the help.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

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.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

829 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