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:
- 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 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:
- 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:
- Open SQL Server Configuration Manager.
- Click SQL Server 2005 Services, and then double-click SQL Server (<InstanceName>).
- In the SQL Server (<InstanceName>) Properties dialog box, click the Advanced tab.
- On the Advanced tab, add the following text at the end of the existing string in the Startup Parameters box: -T4621
- Click OK.
- Add the TokenPermQuota registry entry to customize the quota. To do this, follow these steps:
- Click Start, click
Run, type Regedt32, and then click
- 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>\MSSQLServerIf 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>\MSSQLServerNote <MSSQL.X> is the instance of SQL Server 2005 for which the trace flag is enabled.
- 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.
- Close Registry Editor.
- Click Start, click
How to determine the custom value for the quotaYou can determine the custom value for the quota by using the following formula:
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:
<Total Number of Users in each database>)
SQL Server re-reads the registry entry every time that you enable trace flag 4621. To interactively customize the quota, follow these steps:
- Modify the registry entry to the new value.
- Run the DBCC TRACEOFF(4621, -1) statement.
- Run the DBCC TRACEON(4621, -1) statement.
- Run the DBCC FREESYSTEMCACHE('TokenAndPermUserStore') statement.