sql server memory consumption close to 100%

Question

sql server memory consumption close to 100%

Benki Chendu

SSCrazy Eights

Points: 8051
More actions
January 6, 2014 at 8:32 am

#302763

I have 6 SQL 2008 instances which is hosted on 4 physical servers in a cluster.
Instance 1 is on Node 1
Instance 2 is on Node 2
Instance 3, 4, 5,6 are on Node 3
and Node 4 is passive.
I hadn't noticed the memory consumtion for these servers as I hadnt heard any complaints from my application teams.
Now, one of my application teams has an issue with respect to the performance of the application and I notice that memory is close to 100% with very few MB's of memory left.
I would need help to find out what is consuming so much of memory in my SQL Server.
All these instances have dynamic memory allocated where the max memory is not mentioned. So, SQL Server is ideally taking entire RAM.
Please advice

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Bill (DBAOnTheGo) Hall of Fame Points: 3841 More actions · Answer 1

I have 6 SQL 2008 instances which is hosted on 4 physical servers in a cluster.
Instance 1 is on Node 1
Instance 2 is on Node 2
Instance 3, 4, 5,6 are on Node 3
and Node 4 is passive.

If 4 is passive and this is all one giant cluster, I'd move some of node 3 to node 4 depending on your licensing and if node 3 is the one seeing issues.

I hadn't noticed the memory consumtion for these servers as I hadnt heard any complaints from my application teams.
Now, one of my application teams has an issue with respect to the performance of the application and I notice that memory is close to 100% with very few MB's of memory left.

I would need help to find out what is consuming so much of memory in my SQL Server.

The below script was written by Glenn Berry[/url]. It does a great job of telling you what instance of SQL is hungry. I would set hard limits on what SQL can have and use this as a general point of reference. If you are running a server with applications on it, you might consider getting the Applications off your SQL server or at the least, defining what SQL can use so that it doesn't eat the applications resources as well.

SELECT DB_NAME(database_id) AS [Database Name],

CAST(COUNT(*) * 8/1024.0 AS DECIMAL (10,2)) AS [Cached Size (MB)]

FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)

WHERE database_id > 4 -- system databases

AND database_id <> 32767 -- ResourceDB

GROUP BY DB_NAME(database_id)

ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);

All these instances have dynamic memory allocated where the max memory is not mentioned. So, SQL Server is ideally taking entire RAM.

SQL will take all the ram it thinks it needs for caching or processing. If you do not set a limit, It will consume what it wants. I would look at the above query, see what instance is taking more ram than it used too... and decide.

1) Does instance just needs more ram and you need to look at hardware?

2) Is it a poorly run query that's now causing issues? (Look at Glenn's site. The script there will answer a lot of questions)

3) Was there a one time function that caused SQL to need a lot of ram more than normal?

4) Is it the multi instance SQL box? If so, can you move one of them off?

As a side note. If you're seeing pressure now, when one all of those servers fail over to a single node due to any number of issues including squirrels in the server room... Will the others hold up? What will fail if they all go to one node? I've seen it happen when one of my SA's pushed a patch to the server. It was clustered so nothing goes down right? right? /sad.... and in doing so, they all went down. The one that came up first started all services and now owned all instances.

Keep this in mind if you're already seeing performance issues on a multi instance cluster.

.

Benki Chendu SSCrazy Eights Points: 8051 More actions · Answer 2

^^I ran the above query and see that one database is taking around 32 GB of RAM and other 2 are taking around 7 GB each.

No other database seems to consume more % of RAM.

Bill (DBAOnTheGo) Hall of Fame Points: 3841 More actions · Answer 3

You have them defined as instance 1-6.

Node 1 Instance 1

Node 2 Instance 2

Node 3 Instance 3,4,5,6

Node 4 N/A

Can you tell me what the specs of these boxes are *ram wise* and what instances are showing this?

The one that's 32, is it on Node 3? If so, How big is that database? Temporarily you could set the others to 8 GB of ram and this one to 24. The fact that it asked for 32 though, means it wants that. If it's on Node 3, Consider moving it to Node 4. As a reminder, moving an instance does cause it to go down for a short time.

.