SQL Server Memory Exhaustion

  • 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

  • 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.

  • 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.

  • 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.

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

     

  • 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)

  • 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.

  • 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

  • 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, 6 months ago by  WHug.
  • 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.

  • 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