Description of the "access check cache bucket count" and "access check cache quota" options that are available in the sp_configure stored procedure

Koskee seuraavia: SQL Server 2008 StandardSQL Server 2008 EnterpriseSQL Server 2008 Workgroup

INTRODUCTION


When database objects are accessed by Microsoft SQL Server, the access check is cached in an internal structure that is called the "access check result cache." In rare circumstances, you may experience performance issues if the access check result cache size is too large or too small.

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.

You can use the sp_configure stored procedure together with the "access check cache bucket count" and "access check cache quota" options to control the size of the access check result cache. This article describes the "access check cache bucket count" and "access check cache quota" options that are available in the sp_configure stored procedure.

Note You should change the default values only when a Microsoft Customer Support Services technician advises you to do this.

More information


The following conditions apply to this situation:

  • 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.

Note The default value of 0 indicates that SQL Server is managing the "access check cache bucket count" and "access check cache quota" option, and the following default values are set.

Access Check Cache Bucket Count: SQL Server 2008 to SQL Server 2014
 
Server architecture Default number of buckets
x86 256
x64 and IA-64 2048

 

Access Check Cache Bucket Count: SQL Server 2016 and later
 
Server architecture Default number of entries
x64 256

 

Access Check Cache Quota: SQL Server 2008 to SQL Server 2014
 
Server architecture Default number of buckets
x86 1024
x64 and IA-64 28192048

 

Access Check Cache Quota: SQL Server 2016 and later
 
Server architecture Default number of entries
x64 1024


Note 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 2048

References


For more information about how to customize the quota, see the following article in the Microsoft Knowledge Base:
 
959823 How to customize the quota for the TokenAndPermUserStore cache store in SQL Server 2005 Service Pack 3
 

For more information about performance issues that affect large security caches, see the following CSS SQL Server Engineers Blog article:

Query Performance issues associated with a large sized security cache