Article ID: 959823 - View products that this article applies to.
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.
Performance issues with the current setting of the TokenAndPermUserStore cache storeThe 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:
New trace flag in SQL Server 2005 Service Pack 3Important 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:
322756Microsoft 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:
(http://support.microsoft.com/kb/322756/ )How to back up and restore the registry in Windows
How to determine the custom value for the quotaYou 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:
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:
913089For more information, click the following article number to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/913089/ )How to obtain the latest service pack for SQL Server 2005
933564For more information, click the following article number to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/933564/ )FIX: A gradual increase in memory consumption for the USERSTORE_TOKENPERM cache store occurs in SQL Server 2005
927396For more information about query performance issues that are associated with a large security cache, visit the following Web site:
(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
Article ID: 959823 - Last Review: August 21, 2009 - Revision: 2.0
Contact us for more help
Connect with Answer Desk for expert help.