[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.
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
*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.
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.
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.
More information about the TokenAndPermUserStore
Michael De Voe Premier Field EngineerMicrosoft Certified Master - SQL Server 2008
How long should the query take to run in SQL ?
I have never seen the first query run for more than a second or two. If it is taking longer it could be because there are 1000's of tokens, or it could be as simple as a hardware issue. You could run the DBCC command and then try and run t the statement. If that works them it is almost certain you are having an issue with the TokenAndPermUserStore.