April 26, 2022 at 1:06 pm
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
April 26, 2022 at 2:51 pm
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.
April 26, 2022 at 5:06 pm
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.
April 26, 2022 at 5:54 pm
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.
April 26, 2022 at 5:58 pm
I only get the error when the tabular models are processing.
April 26, 2022 at 7:03 pm
Doesn't matter... sounds like you need more RAM for whatever you having the server do either way.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 26, 2022 at 8:06 pm
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.
April 27, 2022 at 1:08 pm
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
May 18, 2022 at 5:15 pm
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
May 18, 2022 at 6:30 pm
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.
May 18, 2022 at 7:00 pm
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply