Edit

access check cache Server Configuration Options

  • Article

Applies to: SQL Server

When database objects are accessed by SQL Server, the access check is cached in an internal structure called the access check result cache. On an instance of SQL Server that has a high rate of ad hoc query execution, you may notice many access check token entries that have a class of 65535 in the sys.dm_os_memory_cache_entries view. Access check token 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 permissions on t1, t2, and t3. These cumulative permission check results are embedded into an access check token entry and are inserted into the access check cache store with an ID of 65535. If the same user reuses or executes this query multiple times, SQL Server reuses the access check token cache entry one time.

To optimize the use of this cache, you should consider using various query parameterization techniques, or convert frequent query patterns to use stored procedures.

The access check cache bucket count option controls the number of hash buckets that are used for the access check result cache.

The access check cache quota option controls the number of entries that are stored in the access check result cache. When the maximum number of entries is reached, the oldest entries are removed from the access check result cache.

Remarks

The default values of 0 indicate that SQL Server is managing these options. The default values translate to the following internal configurations.

SQL Server 2016 (13.x) and later

Server architecture Default number of entries
Access check cache quota x64 1,024
Access check cache bucket count x64 256

SQL Server 2008 (10.0.x) to SQL Server 2014 (12.x)

  Server architecture Default number of entries
Access check cache quota x86 1,024
x64 and IA-64 28,192,048
Access check cache bucket count x86 256
x64 and IA-64 2,048

In rare circumstances, performance may be improved by changing these options. For example, you might want to reduce the size of the access check result cache if too much memory is used. Or, increase the size of the access check result cache if you experience high CPU usage when permissions are recalculated.

We recommend only changing these options when directed by Microsoft Customer Support Services. If you want to change the access check cache bucket count and access check cache quota values, use a ratio of 1:4. For example, if you change the access check cache bucket count value to 512, you should change the access check cache quota value to 2048.

See also