Degraded Performance on Dynamics NAV and the SQL Server TokenAndPermUserStore


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.

ISSUE

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:

  • 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. 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 the cause or 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

Comments (4)

  1. Sven Witthöft says:

    Hello Michael, I remember the issue from early 2010; in our case the issue was solved after disabling “Always rowlock”. Nevertheless, we had the experience only with one customer database, so I can´t draw a straight line from cause to solution.

  2. René Gubler says:

    Hi Michael

    We have exaclty the same symptoms on one sql server with a large amount of users. I will check this but I guess this is the cause. Thanks

    How many users do you have on this server?

    Regards

    René

  3. Michael De Voe says:

    Wierd, you would not think row locking would effect the permission cache, unless it is creating a permission cache entry for each rowlock.  That is definitely something I need to dig into.

    There were about 200 concurrent users at this customer when this was happening, but I am guessing the the numbers of users was only one factor that lead to the issue.  Right now I do not have a clear explination of why the TokenAndPermUserStore is an issue with some and not others.  My guess is that is also has to do with the number of thrid party applications accessing the NAV database especially if these apps issue a lot "ad-hoc" queries against the database each requiring its own permission cache entry.

    I will update the blog entry when I get some definitve answers.  

  4. joemarselo says:

    in SQL server 2012, [single_pages_kb + multi_pages_kb] becomes [pages_kb] (source: blogs.msdn.com/…/memory-manager-surface-area-changes-in-sql-server-2012.aspx)

Skip to main content