What can cause MEMORYCLERK_SOSNODE clerk gradually taking available memory in SQL Server 2019 CU14?

Question

One of our production server is crashing after every few weeks due to low memory. We found that the instance memory is taken by MEMORYCLERK_SOSNODE clerk. When this clerk gradually use available memory, the plan cache becomes small and SQL engine starts compiling and re-compiling plans frequently. Also, the page life expectancy fluctuates a lot.

Now, I found that the Microsoft has put fix for this clerk gradually taking available memory in their CU11 and same fix they put in CU15 (Not sure if they actually fixed it)

Fix in SQL 2019 CU11 https://support.microsoft.com/en-us/topic/kb5003249-cumulative-update-11-for-sql-server-2019-657b2977-a0f1-4e1f-8b93-8c2ca8b6bef5#bkmk_13990055

Fix in SQL 2019 CU15 https://support.microsoft.com/en-us/topic/kb5008996-cumulative-update-15-for-sql-server-2019-4b6a8ee9-1c61-482d-914f-36e429901fb6

Both fix says, it can cause by using spatial index. We are not using it at all. Since Microsoft has KB article says they fixed it in CU11 and then again in CU15. I'm pretty sure, something is wrong here.

Can anyone help me narrow down the problem here?

Thank you.

Answer 1

That is completely normal and expected behavior. SQL Server never gives up RAM after it is allocated.

If you have a memory size issue, you must set your max server memory to a reasonable value to stop page swapping.

https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/monitor-memory-usage?view=sql-server-ver15

Answer 2

I don't really agree with Tom that this normal and expected. MEMORYCLERK_SOSNODE is not expected to top the pops.

Then again, you have set max server memory to 12 GB and that is not a whole lot.. Since the machine has 24 GB in total, I assume that there are other applications on the machine.

I would recommend that you increase the amount of RAM in the machine to at least 64 GB and set max server memory to 45000.

Answer 3

Hi @Anonymous ,

It is recommended that you change your max SQL Server memory, for example:

SQL instance wll grab all the memory available to it wich is why it is necessary to set the max server memory for the instance so that sufficient physical memory is available for other applications/services and the operating system.

You can use DBCC MEMORYSTATUS to monitor memory usage on SQL Server
Setting Max Memory for SQL Server

https://support.solarwinds.com/SuccessCenter/s/article/Calculate-the-maximum-memory-setting-for-SQL-Server?language=en_US

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.