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

Article ID: 959823 - View products that this article applies to.
Expand all | Collapse all

On This Page

INTRODUCTION

In Microsoft SQL Server 2005, performance issues may occur and CPU usage may increase when the size of the TokenAndPermUserStore cache store increases to several hundred megabytes. To address these issues, SQL Server 2005 Service Pack 3 enables you to customize the quota for the TokenAndPermUserStore cache store.

Quota defines the threshold for the number of entries in the cache store. As soon as a new entry is added that exceeds the quota, an internal clock hand movement is made that decrements the cost of each entry in the store, and those entries whose cost reaches zero are released.

You can monitor the number of entries that are removed in the cache store during the clock hand movement. To do this, query the sys.dm_os_memory_cache_clock_hands Dynamic Management View.
Back to the top | Give Feedback

MORE INFORMATION

Performance issues with the current setting of the TokenAndPermUserStore cache store

The TokenAndPermUserStore cache store does not scale well as the number of entries in the cache store increases. By default, the size of the TokenAndPermUserStore cache store is limited only by available memory. If the cache store is too large when SQL Server reaches the limit of available memory, access to the cache store becomes a performance bottleneck. To limit the number of entries in the TokenAndPermUserStore cache store, trace flag 4618 and trace flag 4610 were introduced in SQL Server 2005 Service Pack 2.

When you use trace flag 4618, the number of entries in the cache store is limited to 1,024. When you use trace flag 4618 together with trace flag 4610, the number of entries in the cache store is limited to 8,192. When the limit is reached, SQL Server 2005 removes some entries from the TokenAndPermUserStore cache store.

However, for some workloads, limiting the cache store to 1,024 or to 8,192 entries may not be an optimal solution. This is because trace flag 4618 forces SQL Server to prematurely clean up the TokenAndPermUserStore cache store even if you are not experiencing any performance issues. Also, additional overhead may occur on the server when SQL Server prematurely cleans up the TokenAndPermUserStore cache store. This additional overhead occurs for one of the following reasons:
  • SQL Server must repopulate the cache store more frequently.
  • SQL Server is forced to re-read permission metadata from the disk and to recalculate permission checks that may have been cached.

New trace flag in SQL Server 2005 Service Pack 3

Important This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base:
322756
(http://support.microsoft.com/kb/322756/ )
How to back up and restore the registry in Windows
Microsoft SQL Server 2005 Service Pack 3 introduces a new mechanism to customize the quota for the TokenAndPermUserStore cache store that is based on the current workload. To customize the quota for the TokenAndPermUserStore cache store, follow these steps:
  1. Enable trace flag 4621.

    To enable trace flag 4621, run the DBCC TRACEON(4621, -1) statement.

    To disable this trace flag, run the DBCC TRACEOFF(4621, -1) statement.

    You can also enable trace flag 4621 when SQL Server starts. To do this, follow these steps:
    1. Open SQL Server Configuration Manager.
    2. Click SQL Server 2005 Services, and then double-click SQL Server (<InstanceName>).
    3. In the SQL Server (<InstanceName>) Properties dialog box, click the Advanced tab.
    4. On the Advanced tab, add the following text at the end of the existing string in the Startup Parameters box:
      -T4621
    5. Click OK.
    Note To verify that the trace flag is enabled, run the DBCC TRACESTATUS (-1) statement.
  2. Add the TokenPermQuota registry entry to customize the quota. To do this, follow these steps:
    1. Click Start, click Run, type Regedt32, and then click OK.
    2. In Registry Editor, locate the following registry subkey if you are running 32-bit versions of SQL Server 2005 on 32-bit versions of Windows operating systems, or if you are running 64-bit versions of SQL Server 2005:
      HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<MSSQL.X>\MSSQLServer
      If you are running 32-bit version of SQL Server 2005 on x64 versions of Windows operating systems, locate the following registry subkey:
      HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\<MSSQL.X>\MSSQLServer
      Note <MSSQL.X> is the instance of SQL Server 2005 for which the trace flag is enabled.
    3. Under the MSSQLServer registry subkey, add a registry entry that has the following properties:
      • Name: TokenPermQuota
      • Type: DWORD
      • Value: <QuotaSize in Hex>

        Note A good starting value for the quota is 4,096. To set the quota of 4,096 for the TokenAndPermUserStore cache store, set the value of the TokenPermQuota registry entry to 00001000.
    4. Close Registry Editor.
Note If the instance of SQL Server is a failover cluster instance, you must add the TokenPermQuota registry entry on the active node that owns the SQL Server resource.

How to determine the custom value for the quota

You can determine the custom value for the quota by using the following formula:
Quota = 1,024 * 2 * ( <Number of Distinct Logins> + <Total Number of Users in each database>)
For example, an instance of SQL Server 2005 has 3,000 distinct logins. All these logins connect to a single database. In this case, the quota value is 1,024 * 2 * (3,000 + 3,000) = 12,288,000.

Note You cannot enable trace flag 4621 together with trace flag 4618. When trace flag 4621 and trace flag 4618 are enabled together, trace flag 4618 takes precedence. Trace flag 4621 is ignored. You can enable trace flag 4621 together with trace flag 4610. Trace flag 4610 increases the size of the hash table that stores the cache entries by a factor of 8.

If you enable trace flag 4610, you substitute 8,192 for 1,024 in the formula. Therefore, the formula is the following:
Quota = 8,192 * 2 * (<Number of Distinct Logins> + <Total Number of Users in each database>)
Note When you determine the number of logins and users, count only active logins and users. If you have an administrative login account that is rarely used, you do not have to count the administrative login in the formula.

SQL Server re-reads the registry entry every time that you enable trace flag 4621. To interactively customize the quota, follow these steps:
  1. Modify the registry entry to the new value.
  2. Run the DBCC TRACEOFF(4621, -1) statement.
  3. Run the DBCC TRACEON(4621, -1) statement.
  4. Run the DBCC FREESYSTEMCACHE('TokenAndPermUserStore') statement.
Back to the top | Give Feedback

REFERENCES

For more information about how to obtain SQL Server 2005 SP3, click the following article number to view the article in the Microsoft Knowledge Base:
913089
(http://support.microsoft.com/kb/913089/ )
How to 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:
933564
(http://support.microsoft.com/kb/933564/ )
FIX: A gradual increase in memory consumption for the USERSTORE_TOKENPERM cache store occurs in SQL Server 2005
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
927396
(http://support.microsoft.com/kb/927396/ )
Queries take a longer time to finish running when the size of the TokenAndPermUserStore cache store grows in SQL Server 2005
For more information about query performance issues that are associated with a large security cache, visit the following Web site:
http://blogs.msdn.com/psssql/archive/2008/06/16/query-performance-issues-associated-with-a-large-sized-security-cache.aspx
(http://blogs.msdn.com/psssql/archive/2008/06/16/query-performance-issues-associated-with-a-large-sized-security-cache.aspx)
Back to the top | Give Feedback

Properties

Article ID: 959823 - Last Review: August 21, 2009 - Revision: 2.0
APPLIES TO
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems
  • Microsoft SQL Server 2005 Enterprise X64 Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Standard X64 Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems
Keywords: 
kbnomt kbrapidpub kbexpertiseadvanced kbhowto kbinfo KB959823
Back to the top | Give Feedback

Give Feedback

 
Back to the topBack to the top

Article Translations