access check cache Server Configuration Options

Applies to: yesSQL Server (all supported versions)

When database objects are accessed by SQL Server, the access check is cached in an internal structure called the access check result cache.

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.

The default values of 0 indicates that SQL Server is managing these options. Starting with SQL Server 2016 (13.x), the default values translate to the following internal configurations:

  • For access check cache bucket count, the value 0 sets a default value of 256 buckets.
  • For access check cache quota, the value 0 sets a default value of 1,024 entries.

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

Important

Microsoft recommends only changing these options when directed by Microsoft Customer Support Services. If you have 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 2,048.

See Also

Server Configuration Options (SQL Server)
sp_configure (Transact-SQL)