Sql-server – What are the downsides of running DBCC FREESYSTEMCACHE (‘TokenAndPermUserStore’) hourly

dbccmemoryperformancesql server

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.

Best Answer

From Comments:

This sounds like a prime candidate for a Microsoft Product Support engineer. - Max Vernon

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

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

Related Solutions

Sql-server – SQL Server – Anyone use SUMA, trace flag 8048, or trace flag 8015

This is an awesome post.

To answer your final question, I'd speculate that your answer is "yes".

That said, I probably would have pursued soft numa before resorting to the trace flags. I think you are right about the numa node allocation and that's could be at the root of your problem. Via soft numa, you could scale out the requests, depending on your count of numa nodes (4?) - to 4, if that's the correct number, and then assign, via ip address, each host to a specific numa node, in addition to that, I'd disable hyper threading. Combined, the issue would likely decrease, however, it would do so at the cost of fewer schedulers.

On a seperate thought, I'd look at forced parameterization - the fact that your load is driving your CPU so high is very interesting and it may be worth looking into that.

Lastly, on multi-numa node systems, I typically have the output of the following queries dumping to a table every N seconds. Makes for some interesting analysis when workload changes or trace flags are implemented:

SELECT getdate() as poll_time, node_id, node_state_desc, memory_node_id, online_scheduler_count, active_worker_count, avg_load_balance, idle_scheduler_count
FROM sys.dm_os_nodes WITH (NOLOCK) 
WHERE node_state_desc <> N'ONLINE DAC'

and

SELECT top 10 getdate() as sample_poll, wait_type, count (*)
FROM sys.dm_os_waiting_tasks
WHERE [wait_type] NOT IN
('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK',
'SQLTRACE_BUFFER_FLUSH','WAITFOR', 'BROKER_TASK_STOP',
'BROKER_RECEIVE_WAITFOR', 'OLEDB','CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT' ) 
GROUP BY wait_type
ORDER BY COUNT (*) DESC

Sql-server – How to interpret the log when I run DBCC TRACEON (3502, 3504, 3605, -1)

The trace flags that you have turned on will tell you what a checkpoint is doing behind the scenes.

3502: writes to the error log when a checkpoint starts and finishes
3504: writes to the error log information about what is written to disk
3605: allows trace prints to go to the error log

Refer to Paul Randall's blog post for more details on the above. Also, Fine Tuning for Optimal Performance has an excellent info - especially In Search of Spikes section.

Some really internals reading :

Instead of concentrating directly on checkpoint behaviour, I would suggest you to look at DMVs and Perfmon (disk related) -

sys.dm_io_virtual_file_stats
Physical Disk Object: Avg. Disk Queue Length
Avg. Disk Sec/Read
Avg. Disk Sec/Write
Physical Disk: %Disk Time
Avg. Disk Reads/Sec
Avg. Disk Writes/Sec

You can refer to Investigating I/O bottlenecks

Best Answer

Related Solutions

Sql-server – SQL Server – Anyone use SUMA, trace flag 8048, or trace flag 8015

Sql-server – How to interpret the log when I run DBCC TRACEON (3502, 3504, 3605, -1)

Related Question