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
Output:
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
Additional Info:
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:
240000 MB
. - Server B (runs the secondary replica at the same datacenter). Max Server Memory:
240000 MB
- Server C (runs the secondary replica at the distant datacenter). Max Server Memory:
250880 MB
.
There are 2 AGs.
The first one has 7 databases.
The second one has 11 databases.
select state,COUNT(*) from sys.conversation_endpoints group by state