We have an Always On setup where in we have two secondary replicas to our primary replica. One of the secondaries is placed in the same datacenter as the primary and the other one is placed at a distant DR site. Currently we have both the secondaries configured with Asynchronous availability mode.
We have a peculiar issue, wherein a specific memory clerk on the primary replica gradually eats up all the memory allocated to SQL Server in a few days time and we are forced to restart the SQL Server services on the primary to get the memory allocation back to the buffer pool.
We are running SQL Server 2016 SP1-CU8 Enterprise Edition on all the 3 SQL Server instances involved. We approached MS with the issue and they suggested to upgrade to SP2 CU1 and then come back. We tried to upgrade to SP2 CU1 in our test environment and somehow the installation itself failed.
I am attaching a snapshot of the memory allocation across different memory clerks to indicate the particular one that eats up all of it. Haven't been able to find much on this issue. Any help/support/direction would be appreciated.
Query to find Top clerks consuming memory.
select sum(pages_kb+awe_allocated_kb)/1024 as Memory_in_MB, type from sys.dm_os_memory_clerks group by type order by sum(pages_kb+awe_allocated_kb)/1024 desc
153824 MEMORYCLERK_SQLBUFFERPOOL 74591 OBJECTSTORE_SERVICE_BROKER 7971 OBJECTSTORE_SNI_PACKET 526 MEMORYCLERK_SOSMEMMANAGER 168 MEMORYCLERK_SQLSERVICEBROKERTRANSPORT 115 MEMORYCLERK_SOSNODE 91 MEMORYCLERK_SQLSTORENG 79 USERSTORE_SCHEMAMGR 65 OBJECTSTORE_LOCK_MANAGER 42 MEMORYCLERK_SQLTRACE
All the 3 machines have a physical memory of
262033 MB. They all run 16 logical processors each.
- Server A (runs the primary replica). Max Server Memory:
- Server B (runs the secondary replica at the same datacenter). Max Server Memory:
- Server C (runs the secondary replica at the distant datacenter). Max Server Memory:
There are 2 AGs.
The first one has 7 databases.
The second one has 11 databases.