August 8, 2022 at 9:12 pm
Hi ,
We have several SQL instances (200 servers). Some of them have SSRS & some of them have SSAS installed.
Receiving the SCOM alerts stating other processes using too much memory. Sometimes the alert is coming from 2-3 servers but sometimes it’s coming from 20 servers at same time.
How can we avoid these?
August 9, 2022 at 6:41 am
What process is SCOM actually erroring on? Sqlservr.exe / reporting services service.exe / msmdsrv.exe?
If it is these services what your seeing is normal, tell SCOM to change the metrics as these services are memory intensive. It’s not uncommon to see 90-95% memory usage by these processes.
Now if it is other processes you need to look at what the process is and if it is causing you any issues with it being on the SQL servers or not.
Some 3rd party software installed on a same server as SQL doesn’t really play nice so you need to verify what SCOM is telling you vs what you need to do.
August 9, 2022 at 8:06 am
Double check SQLServer "max server memory (MB)" setting has been configured on 64-bit systems ! ( or it will consume nearly all available ram and compete for it !)
Check "Server memory configuration options"
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 14, 2022 at 11:03 pm
Thank you. Yes, my SQL server memory settings are limited & leave the memory to OS. When SSAS is installed with default memory settings how it will work?
If SSAS is not in use, does it release the memory to SQL & OS?
August 16, 2022 at 2:31 pm
No, SSAS keeps the memory as it is usefull for caching. It will try to keep a minimum of free memory on the server. When lowmemory is triggered, start cleaning out caches. https://thedailydba.wordpress.com/2014/12/09/tabular-ssas-memory-settings/#:~:text=Tabular%20SSAS%20Memory%20Settings%201%20Mode%200%20%E2%80%93,allow%20VertiPaq%20data%20to%20exceed%20total%20physical%20memory
HardMemoryLimit is the maximum ( if above, start killing sessions).
By default SSAS keeps a minimum of lowmemorylimit for itself (65% by default) https://docs.microsoft.com/en-us/analysis-services/server-properties/memory-properties?view=asallproducts-allversions.
Meaning that other processes and OS have only 35% or less available
SSIS normally releases memory when it's done since no caching is needed
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply