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.
Update for 2024:
Microsoft apparently has put out an article detailing the information from this post and related posts. It can be reviewed here:
