Slow SQL Query Execution in SQL Server 2005 SP3 x64

Recently, we've come across poor SQL performance on a very powerful SQL box and we were having trouble identifying the exact cause. After some talks with the software vendor and Microsoft, we were pointed to the TokenAndPermUserStore cache.

There are an increasing amount of articles online that speak about this cache, but it would still be difficult for most DBAs to come across by searching the Internet. I want to do my part to help increase visibility of this issue by hopefully providing a match for other people experiencing the issue.

Basically, the TokenAndPermUserStore cache stores security information for user sessions and queries. If your system executes a large amount of Dynamic SQL, the cache may begin to grow larger and larger. Our system was growing this cache into multiple gigabytes before we identified it as a problem.

Use the following query to determine the size of your TokenAndPermUserStore cache in megabytes. If your cache is over a couple hundred megabytes, you may start to experience slower performance with your queries.

SELECT SUM(single_pages_kb + multi_pages_kb)/1024.0 AS "SecurityTokenCacheSize(MB)" FROM sys.dm_os_memory_clerks WHERE name = 'TokenAndPermUserStore'

Luckily, there is a way to fix it, as described in this Microsoft KB article: https://support.microsoft.com/default.aspx?scid=kb;EN-US;959823

A registry key must be created and a trace must be turned on to get SQL Server to maintain the size of the cache. The information provided is somewhat limited, but the registry value is the number of entries allowed in the Token cache. Microsoft provides a formula to calculate how many token cache entries you may want to have. I found it more useful to decide on a size in megabytes that I wanted to limit the cache size to.

For example, if you want to max out the Token Cache at about 128MB, the registry value would be 4CD17 (314,647 entries). With some rough math, that says there are about 2,458 entries per MB of token cache, so adjust for your own needs to get a final value and convert it to Hex with a calculator.

Once the registry value is in place, turn on your trace:

DBCC TRACEON(4621, -1)

You will see the token cache size start to drop. If you want to help it along, you can actually reset it yourself

DBCC FREESYSTEMCACHE('TokenAndPermUserStore')

One last thing to note is that this trace will not restart when your SQL service restarts. If you want to keep the change in place, you will have to add the trace switch to your startup parameters, or find another creative way to set it when the service starts. This registry fix also works for SP4. SQL 2008 has built in these cache limits into sp_configure (access check cache quota and access check cache bucket count).

Hopefully this article has put you on the right path towards identifying an issue with this cache. I've only seen this happen on one of our newest servers, which has more cores and more RAM than any other server that we have.