5

I have been seeing some concerning behavior in the SQL Server memory caches on one of our production servers. We are on SQL Server 2014 SP2 Enterprise Edition, 16 cores, 400GB RAM (300 GB dedicated to SQL Server).

Using this query (credits to Glenn Berry):

SELECT TOP(10) mc.[type] AS [Memory Clerk Type], 
       CAST((SUM(mc.pages_kb)/1024.0) AS DECIMAL (15,2)) AS [Memory Usage (MB)] 
FROM sys.dm_os_memory_clerks AS mc WITH (NOLOCK)
GROUP BY mc.[type]  
ORDER BY SUM(mc.pages_kb) DESC OPTION (RECOMPILE);

I have discovered an issue. The USERSTORE_TOKENPERM values grow to a point where they seem to overwhelm the cache, as a result, almost all plans are compiled and none are cached. That is CACHESTORE_SQLCP and CACHESTORE_OBJCP will sit at around 200 MB each, while the USERSTORE_TOKENPERM cache is upwards of 11 GB. With over 168,000 tokens stored, for about 100 concurrent/600 total users with 3000-5000 connections. These numbers seem way off.

Unfortunately, the vendor that provides this application doesn't have crazy things like "System Requirements" documentation, and they won't commit to supporting the application on untested Service Packs/CU's. So my hands are tied to SQL Server 2014 SP2, as other threads I've read suggest upgrading to SQL Server 2014 SP2 CU7 or higher. That is the long-term goal. So I needed another solution for the short term.

After running the command suggested in this thread from 2009.

Specifically:

DBCC FREESYSTEMCACHE ('TokenAndPermUserStore')

I see our CPU usage dropping from 30%-80% utilization, to under 30%. User complaints of "slowness" in the application have trailed off. Plan caches are now growing to 10 GB or more.

As the day goes on, the token cache grows again. Near the end of the day we start to see a the problematic caching behavior again. I have scheduled the DBCC command to run nightly, outside of business hours.

I understand that I can apply trace flag 4610 and 4618 here to limit the number of stored tokens.

Increases the size of the hash table that stores the cache entries by a factor of 8. When used together with trace flag 4618 increases the number of entries in the TokenAndPermUserStore cache store to 8,192.

By the end of the day, we end up with about 40,000 tokens cached.

Does anyone have any experience (or knowledge) with this issue that can say whether using trace flags, or clearing the cache, is a more effective solution?

What are the downsides of clearing the TokenAndPermUserStore cache more frequently than once per day? Say, hourly? Is there a "too often"?

And what would be the downsides of limiting the token cache through trace flags? Will I have end users hit bottlenecks with token limits, or otherwise (thus causing another issue)?

My major concern here is that, this is medical data, and I do not want to lose patient data as a result of a problem associated with a cache flush.

  • 4
    This sounds like a prime candidate for a Microsoft Product Support engineer. – Max Vernon Dec 20 '19 at 20:32
  • 1
    We experienced the same issue and our final decision was to use DBCC FREESYSTEMCACHE ('TokenAndPermUserStore') on a daily basis. We consider the solution as more predictable and managed than trace flags using. Unfortunely, MS wasn't able to find and offer some better solution. – Denis Rubashkin Dec 21 '19 at 11:03
  • 1
    ...and we've been using SQL Server 2016 SP2 Enterprise Edition, so I don't think that upgrading to CU 7 will help with your issue. – Denis Rubashkin Dec 21 '19 at 11:24
  • 1
    This is an old bug, I have a hunch that latest SP/CU should fix it. If not, you may need to contact MS support. As for question I faced this issue a lot in SQL Server 2008 R2 version and selectively clearing cache did not cause any issue. – Shanky Dec 23 '19 at 7:13

Your Answer

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

Browse other questions tagged or ask your own question.