I have recently come across an issue with a large Dynamics NAV customer where queries that normally ran VERY quickly became slower and slower over time and blocking was increased significantly. 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 normally 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 the issue we came across a potential cause for the degraded performance. The potential cause was a rather obscure issue (obscure in the fact that it is something I have not seen it cause issues with NAV in the past). The issue has to do with the SQL Server TokenAndPermUserStore growing significantly over time which causes increased query times across the board, which leads to increased blocking and generally poor performance and 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. 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 the cause or 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
Other Microsoft Sites
I'm a Customer
I'm a Partner
Use the official Twitter tags:
#MSDYNCOMM | #CONV13