Link to home
Create AccountLog in
Avatar of snyderkv
snyderkv

asked on

SQL eating up memory?

EE,

I have a rather simple question I think. I have a SQL Server with 32GB. Even though I set minimum memory value of 5gb and max of 24gb, my operating system always runs on 1gb or less and I'm constantly at 96% utilization in total. I'm not sure how to prevent this from happening. Am I suppose to increase the Minimum memory in SQL?

Thanks again
Avatar of Patrick Bogers
Patrick Bogers
Flag of Netherlands image

Hi

SQL behavior is same as exchange servers, they occupy by default as much memory as the system has.
Nothing you can do about it.

Cheers
post screenshots of task manager
sorted by cpu and memory
Avatar of snyderkv
snyderkv

ASKER

How is that true you can set the Min and Max values?

Husey, not at workstation until tomorrow. But you can set the max value to 5-10gb less than total memory and that should be reserved for the OS right?
I never really got the idea of max memory to be honest, you set the maximum for the SQL Server Buffer Pool but there are other parts of sql that consume memory as well. All I can think off max memory (per instance) is to prevent memory starvation on very busy machines.
I do get the idea of allocating as much memory as possible as it makes perfect sense, the more memory available the faster the database can perform.

So as long as the os is/stays responsive your memory settings works fine.

Cheers
SQL Server cannot exceed the max memory memory usage unless the value of max server memory is raised.
SQL Server does not immediately allocate the memory specified in max server memory on startup. Memory usage is increased as needed by SQL Server until reaching the value specified in max server memory. SQL Server will utilize the max value provided and It never exceeds.

Hve a look here: https://technet.microsoft.com/en-us/library/ms178067(v=sql.105).aspx
Ok but if anyone knows, is it normal for SQL to have 1GB memory for the Operating System? This is really what I'm trying to increase. So if I set a Maximum memory of 24gb, ooes that mean 6gb would be left for the operating system on a 30gb server?
SQL Server won't consume more memory that you have set for max_server_memory configuration.
Besides SQL Server engine, what else do you have installed in that server? SSAS, SSRS, SSIS, another SQL Server instance, any other application, ...?
Two instances, each one has a SCOM database. That is all. No other apps except the usual corporate stuff
So you're still having issues with that server.
If you have 2 instances you need to set the max_server_memory in both instances and the SUM of both values shouldn't be greater that 28GB (32GB - 4GB for OS).
Remember when I asked you about the necessity of having 2 SQL Server instances? This is one of the main reason why you should have all databases in a single instance. Like this you'll need to share resources for both.
My recommendation is to set max_server_memory to 14GB in each SQL Server instance so it will up with 28GB and will left 4GB for OS.
I see. Just to clarify, The two instances are on a 6 node SQL Cluster. Sometimes it's the only instance, sometimes it's not. I'ts a little confusing on how to configure it but I get the general idea.
The two instances are on a 6 node SQL Cluster.
Are there more SQL Server instances in the Cluster or only these two instances for SCOM?

I'ts a little confusing on how to configure it but I get the general idea.
Main idea is to have enough memory in one node that will be able to support all SQL Server instances in case of 5 nodes are down. I know, it's somehow impossible to happen but this is how it should work.
You can always assume that will never happen and for example state that worst case scenario is to have half of nodes down, so you "just" need to assure that a node can support the work for regular 2 nodes and then provide the enough resources (CPU and RAM) to accomplish that.
I think two instances per node. So in the first scenario one 24gb server (max value) would have to support 12 instances or 2gb per instance? or 4gb/server if half failed.

It would appear as thought we should allocate more memory?
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
It would appear as thought we should allocate more memory?
Yes. Unless you review the need for so many SQL Server instances or nodes. You can always try to reduce the number of instances (e.g. having only one for SCOM).
Keeping them separate was a best practice if I remember correctly but I guess if both instances are on the same SQL server that doesn't really improve performance?
Keeping them separate was a best practice if I remember correctly but I guess if both instances are on the same SQL server that doesn't really improve performance?
In separate servers, yes but being in the same server I don't see any advantage to have two instances.