2

I'm experiencing a very weird problem with userstore_tokenperm clerk recently.

We have an environment with some databases in contained mode executing in version SQL Server 2017. Anyway i'm facing this problem since the SQL Server 2016.

At a certain moment this cache starts to grow at a rate of 6GB by day. When this ocurr we have to do a failover and restart the principal node of AG.

Run DBCC FREESYSTEMCACHE ('TokenAndPermUserStore') does not clear the cache. We are considering to use the trace flags 4621 as suggested by microsoft to avoid this memory pressure, but i want to know if someone has a tip to help me to understand this behavior. This seems like a memory leak, because even running DBCC FREESYSTEMCACHE the objects are not released from memory.

We have around to 70 users in contained mode in this server and when the cache starts to grow it grow until starve the server for memory.

I tried to find out what kind of class of token is present and the worse instance have only this entrys.

In sys.dm_os_memory_clerks we already faced more than 30GB of consumption

Someone please can help with some troubleshooting hint or even with an experience about this kind of problem.

Thks in advance

  • We're using default language for all users. In this case english. – Rafael Cardoso Araujo Jun 28 '19 at 13:31
  • old question, but are you using sp_setapprole and sp_unsetapprole ?? – Mitch Wheat Mar 29 at 6:11
2

Edit: sorry for not returning with a solution for this case. The MS support suggested me change the language of user from us_english to NONE. The last response from the support was ... "Probably a memory leak when the user store token, which is 8 kb, needs to be created to store a certain word due to the default language setting. To have more details and confirm what word is causing this high memory usage, these other collections would be necessary. " As I had no problem after the adjustment, it was not possible to capture any new dump and therefore the solution may have occurred due to another scenario including the applications that are used on this server. Thanks Brent Ozar by the hint

| improve this answer | |
  • How did Microsoft tell you to change the language of a user to "none"? (Or how did you do it?) – Brent Ozar Apr 25 at 11:12
  • hello Brent, For us, changing the language of a contained user to NONE did not affect any premise of the applications. I was unable to reproduce the root cause of this leak, nor could I detail the cause. I believe I still have the dumps in this case. Some databases were migrated from the login / user to the contained one and we do not use the stored procedure suggested in docs for this purpose. I believe that there is or has been a leak in a very specific situation. The entry in sys.dm_os_cache_entries was always unique and reached 60 GB in some cases. – Rafael Cardoso Araujo May 2 at 17:46
  • 2
    You may want to change your answer then - you said that changing the language of the user solved your problem, but your comment indicates that this is not true. Just trying to save folks agony if they think this will actually work. Thanks! – Brent Ozar May 2 at 19:00
  • Thanks for the guidance, Brent. I tried to edit the answer to avoid this scenario. – Rafael Cardoso Araujo May 4 at 4:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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