2

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.

Memory Clerks

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.

enter image description here

Improve this question
8
  • If MS suggested you to upgrade to Sp2 Cu1 then you should focus on this. I believe the question should be what caused the service pack upgrade to fail, it is quite likely you were facing bug which MS guys knows and and that is why they specifically suggested you to upgrade to SP2 CU1.
    – Shanky
    Oct 9, 2018 at 15:22
  • On the contrary, the MS guys sounded absolutely clueless on this one. They could not identify this as a documented bug or a known issue. We are able to restart the service in our scheduled down times. Our focus is to mainly understand the cause behind this issue, which we have not been able to find yet.
    – Ritesh Chawla
    Oct 9, 2018 at 16:45
  • I have formatted the result and added it into the question, you can see the biggest consumer of memory is buffer pool but yes SB is taking some good amount of memory too. So my question is are you using SB ? if yes what is output of select state,COUNT(*) from sys.conversation_endpoints group by state
    – Shanky
    Oct 10, 2018 at 7:04
  • Thank you for editing the question. This looks much better now. Yes, we used to have an SB based solution tied to event notifications to identify blocking and deadlocks but we took that off as soon as we found this problem. Currently we are not using SB explicitly for any purpose, though internally it must be getting used by the SQL Server. I have edited the question above to add a picture of the result received from the query provided in the last comment.
    – Ritesh Chawla
    Oct 10, 2018 at 13:44
  • 1
    @Ritesh Chawla I believe this is a bug, and it is unfortunate that you were not able to get help from MS. Did you not escalated the matter to premier field engineers ? You can do that instead of just closing the case. Please drop a mail to me on email ID mentioned in my website link. Before that I strongly suggest you to apply latest SP and CU
    – Shanky
    Oct 10, 2018 at 15:07

1 Answer 1

Reset to default
2

You mentioned that you're not explicitly using service broker anymore, but Availability Groups (AGs) use service broker behind the scenes. This system is called the Usage Control Service (UCS) and is used for queuing and sending groups of log blocks from the primary to the secondaries.

I was able to find at least one bugfix for this system causing memory issues:

FIX: Service Broker UCS task leaks memory in SQL Server 2014 or 2016

Assume that you have AlwaysOn Availability Groups configured in SQL Server 2014 or 2016. If an application uses the TABLOCK hint, the Service Broker Usage Control Service (UCS) task leaks memory. In this situation, you can see that the number of tasks in the sys.dm_os_tasks function continuously grows. This causes AlwaysOn synchronization to become slow.

Note that this bug was fixed in SQL Server 2016 (RTM) CU2, so unless this is a regression, this isn't the specific bug you're facing.

All that to say, this could be a legitimate bug in the product that's been fixed in a newer version. If you're having trouble updating to SP2, consider trying to at least updating to the latest CU for SP1 to see if the problem has been resolved there.

Improve this answer
2
  • Yes, we did have a look at this article on the MS support site. We are currently running SQL Server 2016 SP1 CU8 which should cover the described hot fix. And we do not observe the symptoms given in the article. We do not observe the number of tasks growing continuously in sys.dm_os_tasks result set. thanks for sharing this information.
    – Ritesh Chawla
    Oct 10, 2018 at 16:44
  • Yes, I mentioned you should have this specific fix =) I was just clarifying that AGs use service broker, and that there have been issues with it in the past, and that this is likely a bug.
    – Josh Darnell
    Oct 10, 2018 at 16:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.