Jaa

SQL Server 2005 performance problems due grow of TokenAndPermUserStore cache

  • Artikkeli

TokenAndPermUserStore is one of the many caches present in the SQL Server 2005 memory architecture. As the name implies, this cache stores various security related information used by the SQL Server Engine.

To find out the amount of memory consumed by this token cache, you can query the DMV’s as follows:

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

FROM sys.dm_os_memory_clerks

WHERE name = 'TokenAndPermUserStore'

Typical symptoms:

1. Queries which normally finish faster take a long time

2. CPU usage of SQL Server process is relatively higher. CPU usage could come down after remaining high for a period of time.

3. Connections from your applications keep increasing (specifically in connection pool environments)

4. You encounter connection or query timeouts

Possible workarounds (assuming that you are running SQL Server 2005 SP2 build >= 9.00.3179:

1. Use Trace flag -T4618 to restrict the number of entries maintained in the TokenAndPermUserStore cache. This trace flag was introduced in SQL Server 2005 SP2. You can use Trace Flag –T4610 in conjunction with –T4618 to relax the limit on number of entries in the cache. Also -T4610 increases the number of buckets in the hash tables used to maintain this cache.

2. Execute the command DBCC FREESYSTEMCACHE ('TokenAndPermUserStore') at regular intervals to obtain relief (this is useful to prove that you area really experiencing TokenAndPermUserStore cache grow problem)

3. Use some of the best practices mentioned in the above articles to reduce the cache growth (e.g. Parameterize ad-hoc queries, forced parameterization, avoiding recompiles, etc).

With SP3 there is a new option to control TokenAndPermUserStore cache store and avoid this problem. Please see more details on the following KB article:

959823 How to customize the quota for the TokenAndPermUserStore cache store in SQL Server 2005 Service Pack 3
https://support.microsoft.com/default.aspx?scid=kb;EN-US;959823

On SQL Server 2008 this cache mechanism has been improved anyway you might noticed 2 new advanced configuration options in sp_configure named “access check cache bucket count” and “access check cache quota