Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. Queries take a longer time to finish running when the size of the TokenAndPermUserStore cache grows in SQL Server 2005

Queries take a longer time to finish running when the size of the TokenAndPermUserStore cache grows in SQL Server 2005

View products that this article applies to.

Bug #: 429501 (SQLBUDT)

↑ Back to the top

Symptoms

In Microsoft SQL Server 2005, you may experience the following symptoms:
  • 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.
To monitor the size of the TokenAndPermUserStore cache, you can use a query that resembles the following:
SELECT SUM(single_pages_kb + multi_pages_kb) AS 
   "CurrentSizeOfTokenCache(kb)" 
   FROM sys.dm_os_memory_clerks 
   WHERE name = 'TokenAndPermUserStore'

↑ Back to the top

Cause

The TokenAndPermUserStore cache store maintains the folllowing security token types:
  • LoginToken
  • TokenPerm
  • UserToken
  • SecContextToken
  • TokenAccessResult.
Different classes of TokenAccessResult entries are also present. This specific problem occurs because many TokenAccessResult entries that have a class of 65535 are present.


On an instance of SQL Server that has a high rate of random dynamic query execution, you notice lots of TokenAccessResult entries that have a class of 65535 in the sys.dm_os_memory_cache_entries view. TokenAccessResult entries that have a class of 65535 represent special cache entries. These cache entries are used for cumulative permission checks for queries. For example, you may run the following query: 
select * 
from t1 join t2 join t3
In this case, SQL Server computes a cumulative permission check for this query. This check determines whether a user has select on t1, t2, t3. These cumulative permission check results are embedded into a TokenAccessResult entry and are inserted into the TokenAndPermUserStore cache store with an ID of 65535. If the same user reuses or executes this query multiple times, SQL Server reuses the TokenAccessResult cache entry one time.

When this cache store grows, the time to search for existing entries to reuse increases. 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.

↑ Back to the top

Resolution

Service pack information

To resolve this problem, obtain the latest service pack for SQL Server 2005. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
913089 How to obtain the latest service pack for SQL Server 2005
To resolve this problem, SQL Server 2005 Service Pack 2 changes the caching behavior of the Permission tokens. By default, the TokenAccessResult security cache entry for ad hoc queries is only cached when a specific ad hoc query is executed again.

↑ Back to the top

Workaround

To work around this problem, use one or more of the following methods:
  • Explicitly parameterize ad hoc queries.

    Notes
    • This method lets you effectively reuse ad hoc queries and their plans.
    • When you use this method, you do not have to create a TokenAccessResult entry every time that you run the ad hoc query together with different parameters.
    • With this method, the size of the TokenAndPermUserStore cache remains under reasonable limits.
  • Wrap ad hoc queries within stored procedures, and use stored procedures instead of directly executing ad hoc queries.


    Notes
    • Execution plans are cached for the statements that are in the stored procedure.
    • The TokenAccessResult entry for each statement is associated with the execution plan entry.
    • As long as the execution plan for this stored procedure remains in the cache, every execution of the stored procedure effectively reuses the TokenAccessResult entries. Therefore, you do not have to create new TokenAccessResult entries.
  • Enable the FORCE_PARAMETERIZATION database option.

    Notes
    • This method lets you effectively reuse ad hoc queries and their plans.
    • When you use this method, you do not have to create a TokenAccessResult entry every time that you run the ad hoc query together with different parameters.
    • With this method, the size of the TokenAndPermUserStore cache remains under reasonable limits.
  • Add the login that executes varied ad hoc queries as a member of the sysadmin server group.

    Notes
    • TokenAccessResult entries are only created for an ad hoc query when the query is executed by a login that is not a member of the sysadmin server group.
    • Because the TokenAccessResult entries are not created, this behavior keeps the TokenAndPermUserStore cache size to a manageable size.
  • Flush entries from the TokenAndPermUserStore cache.

    Notes
    • To do this, run the following command:
      DBCC FREESYSTEMCACHE ('TokenAndPermUserStore')
    • Ideally, try to watch the threshold of the TokenAndPermUserStore cache size when problems start to appear.
    • You can create a scheduled SQL Server Agent job that performs the following actions:
      • Check the size of the TokenAndPermUserStore cache size. To do this, run the following command:
        SELECT SUM(single_pages_kb + multi_pages_kb) AS 
           "CurrentSizeOfTokenCache(kb)" 
           FROM sys.dm_os_memory_clerks 
           WHERE name = 'TokenAndPermUserStore'
      • If the cache size is bigger than the threshold that you observed, run the following command:
        DBCC FREESYSTEMCACHE ('TokenAndPermUserStore')

↑ Back to the top

References

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

933564 FIX: A gradual increase in memory consumption for the USERSTORE_TOKENPERM cache store occurs in SQL Server 2005

959823 How to customize the quota for the TokenAndPermUserStore cache store in SQL Server 2005 Service Pack 3

↑ Back to the top

Keywords: kb, kbtshoot, kbinfo, kbentirenet, kbsql2005tsql, kbprb

↑ Back to the top

Article Info
Article ID : 927396
Revision : 3
Created on : 3/30/2017
Published on : 3/30/2017
Exists online : False
Views : 393