Please help me with SQL memory pressure investigation.
1 month ago we upgraded from SQL2005 (on WindowsServer2008) to SQL2016 (on WindowsServer2016).
Since that moment we had few serious incidents caused by memory pressure.
How do I know that we had memory pressure?
SQL plans size dropped to 100MB instead of standard 8GB.
Multiple errors in log like: "AppDomain 22 (XXX.dbo[runtime].28) is marked for unload due to memory pressure."
Are there any well known reasons of internal/external memory pressure?
I suspect that it was internal memory pressure because:
- there was enough free system memory on server (not used by SQL)
- problem was solved by restart of SQL Server service
Our system description:
SQL 2016 13.0.4451.0 (SP1+CU5)
Server Memory: 262048 MB - divided between 3 SQL instances (MaxMemory: 160GB/32GB/16GB + over 40GB free for System)
Availability Groups enabled and replicated to Secondary Server.
Query Store currently disabled since first problem occured (it was suspected as source of problem).
Lock Pages in Memory and Instant File Initialization - enabled
DB Compatibility level = SQL2016 (few times we changed to SQL2012 because CPU was better, and it seems that we didn't have memory pressure).
Legacy Cardinality Estimation = ON (much better plans). Query optimizer fixes = ON
optimize for ad hoc workloads option = ENABLED
How can we prepare for next memory pressure disaster?
We monitor such parameters like #Compilations or PageLifeExpectancy so we noticed that there is disaster in progress.
But we need to monitor something to find reason of memory pressure.
Can we enable some additional logging?
Some useful event logs in WindowsServer?
Any well known memory pressure sources from your experience?