[Ed. Note - this is a repost of an article Michael DeVoe posted to the NAV Team blog, but it applies equally to Dynamics AX]

I recently came across an issue with a large Dynamics ERP customer where queries were becoming slower over time, and blocking significantly increased.  After going through the normal troubleshooting activities, there seemed to be no easily explainable cause for the degraded performance.  Essentially we were seeing queries that should take just a handful of milliseconds, were suddenly taking hundreds of milliseconds to complete causing increased blocking and degraded performance across the board.

ISSUE

After doing some research on this issue, we came across a potential cause for the degraded performance. The issue has to do with the SQL Server TokenAndPermUserStore growing significantly over time which causes increased query times and blocking across the board , and generally poor performance with a degraded end user experience.  In summary the TokenAndPermUserStore is a security cache that maintains the following security token types; LoginToken, TokenPerm, UserToken, SecContextToken, and TokenAccessResult.  When the cache store grows, the time to search for existing security entries to reuse increases, causing slower query times.  Access to this cache is controlled so that only one thread can perform the search. This behavior eventually causes query performance to decrease, and more CPU utilization occurs.       

Some Symptoms of TokenAndPermUserStore issue

• Queries that typically run faster take a longer time to finish running.
• CPU utilization for the SQL Server process is more than usual.
• When you experience decreased performance when you run an ad hoc query, you view the query from the sys.dm_exec_requests or sys.dm_os_waiting_tasks dynamic management view. However, the query does not appear to be waiting for any resource.
• The size of the TokenAndPermUserStore cache store grows at a steady rate.
• The size of the TokenAndPermUserStore cache store is in the order of several hundred megabytes (MB)*.
• In some cases, execution of the DBCC FREEPROCCACHE command provides temporary relief**.

*You can use the following query to check the size of the TokenAndPermUserStore

   SELECT SUM(single_pages_kb + multi_pages_kb) AS "CurrentSizeOfTokenCache(kb)" 

   FROM sys.dm_os_memory_clerks WHERE name = 'TokenAndPermUserStore'

**In SQL Server 2008 R2 there is a bug that when you run DBCC FREESYSTEMCACHE to clear the TokenAndPermUserStore it does not work.  There is a fix for this included in cumulative update package 3 for SQL 2008 R2.  The latest update package for R2 is CU 4.

http://support.microsoft.com/kb/2277078

CAUSE

In the case of this customer, the TokenAndPermUserStore had grown to 6.8GB.  Why this grew to 6.8GB is pure speculation at this point.  Normally this should not grow to more than a few hundred Megabytes, and even at that level could potentially cause issues depending on how much memory SQL Server has available.

RESOLUTION/WORKAROUND

An easy way to test whether this issue is causing a performance problem is to run the following query to clear the TokenAndPermUserStore, and if performance improves it is likely that this was one of the causes of your performance issues.

                DBCC FREESYSTEMCACHE ('TokenAndPermUserStore')

There are two potential fixes/workarounds.

• Create a SQL Job that runs DBCC FREESYSTEMCACHE ('TokenAndPermUserStore') every few hours to keep the size of the cache under control
• Set a limit for the size for the size of the TokenAndPermUserStore.

http://support.microsoft.com/kb/959823

More information about the TokenAndPermUserStore

http://support.microsoft.com/kb/927396/en-us

Michael De Voe 
Premier Field Engineer
Microsoft Certified Master - SQL Server 2008