SQL Server Memory Exhaustion

Question

Post reply

SQL Server Memory Exhaustion

astrid 69000

Hall of Fame

Points: 3208
More actions
April 26, 2022 at 1:06 pm

#4022466

Hello,
I have a virtual server that had 4 cpu and 192 ram. we are running version 2017.
I had allocated 100 ram to the sql max memory and everything was ok.
We added 4 more cpu to the server and now I keep getting errors with SQL Server Memory Exhaustion.
What are the things you would recommend me to check?
Thanks
Astrid

Viewing 11 posts - 1 through 10 (of 10 total)

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

ratbak Ten Centuries Points: 1382 More actions · Answer 1

It sounds like you were CPU-bottlenecked, and since that is addressed, now you are memory constrained.

Are you using Enterprise Edition? What else do you have running on the VM?

92GB is a lot to reserve for the OS. Unless you have a lot of other services running on the server, you could probably increase max memory to 128GB for Standard Edition, & more if Enterprise.

astrid 69000 Hall of Fame Points: 3208 More actions · Answer 2

Hi, we have an enterprise edition.

We are running on the same virtual machine the SSAS and SSIS, nothing else apart from that.

I capped it to 100 max for the sql because of the SSAS server, we have several tabular models that get processed daily.

ratbak Ten Centuries Points: 1382 More actions · Answer 3

You should probably start w/ determining what is using the most memory, & seeing if there opportunities to tune expensive queries via better indexes and/or more efficient queries.

Beyond that, the obvious answer is add more memory... or move SSAS and/or SSIS.

astrid 69000 Hall of Fame Points: 3208 More actions · Answer 4

I only get the error when the tabular models are processing.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 5

Doesn't matter... sounds like you need more RAM for whatever you having the server do either way.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

frederico_fonseca SSCoach Points: 16167 More actions · Answer 6

likely your tabular server memory settings not setup correctly - these can be tricky and need to be set according to whatever memory is available to the particular tabular instance - incorrect settings will cause it to use too much memory and/or to use paging when its not required - and however you are doing the tabular processing may not be the best way for your resources.

a process full will require more memory vs a process clear + process data + process recalc - process full will require memory equal to 2.5 times your biggest table

if the memory error is on SQL Server then setting minimum memory may help as well.

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 7

SQL Server is a greedy cuss. Running SSIS and SSAS on the same server is going to cause quite a bit of contention.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

WHug Ten Centuries Points: 1248 More actions · Answer 8

SSAS memory settings are the culprit here. In Enterprise edition, the memory limit for SSAS is the server max, so it is very probable that SSAS is competing against SQL for resources. SQL will not give up memory headroom easily. so the memory settings in SSAS advanced settings should be carefully considered. Set the SQL Server max memory setting AFTER setting memory in SSAS. See the still valid Analysis Services Operations Guide on how to set the values: https://download.microsoft.com/download/b/e/1/be1aabb3-6ed8-4c3c-af91-448ab733b1af/ssasopsguide2008r2.docx

--Will

This reply was modified 2 years, 7 months ago by WHug.

frederico_fonseca SSCoach Points: 16167 More actions · Answer 9

WHug wrote:

SSAS memory settings are the culprit here. In Enterprise edition, the memory limit for SSAS is the server max, so it is very probable that SSAS is competing against SQL for resources. SQL will not give up memory headroom easily. so the memory settings in SSAS advanced settings should be carefully considered. Set the SQL Server max memory setting AFTER setting memory in SSAS. See the still valid Analysis Services Operations Guide on how to set the values: https://download.microsoft.com/download/b/e/1/be1aabb3-6ed8-4c3c-af91-448ab733b1af/ssasopsguide2008r2.docx
--Will

that link is no longer valid and it also does not apply to this case - 2008R2 only had SSAS Dimensional and the OP is using SSAS Tabular - memory settings are totally different.

WHug Ten Centuries Points: 1248 More actions · Answer 10

Hmm, link just tested fine. You are right though, it won't have tabular settings, but it does have good guidance for collocating SQL with SSAS and some of the memory settings are the same. Here is a more relevant link: https://docs.microsoft.com/en-us/analysis-services/server-properties/memory-properties?view=asallproducts-allversions

--Will