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

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.

In Microsoft SQL Server 2008, 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.

The "access check cache bucket count" option controls the number of hash buckets that are used for the access check result cache. The following table summarizes the default values for the "access check cache bucket count" setting.

Note The default value of 0 indicates that SQL Server is managing the "access check cache bucket count" option.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 following table summarizes the default values for the "access check cache quota" setting.

Note The default value of 0 indicates that SQL Server is managing the "access check cache quota" option.Note When you change the "access check cache bucket count" and "access check cache quota" options, 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. 

For more information about how to customize the quota , click the following article number to view the article in the Microsoft Knowledge Base: For more information about performance issues with large security caches see the following information: http://blogs.msdn.com/psssql/archive/2008/06/16/query-performance-issues-associated-with-a-large-sized-security-cache.aspx (http://blogs.msdn.com/psssql/archive/2008/06/16/query-performance-issues-associated-with-a-large-sized-security-cache.aspx)