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

Article translations Article translations
Article ID: 955644 - View products that this article applies to.
Expand all | Collapse all

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.

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.

MORE INFORMATION

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.
Collapse this tableExpand this table
Server architectureDefault number of buckets
x86256
x64 and IA-642048
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.
Collapse this tableExpand this table
Server architectureDefault number of entries
x861024
x64 and IA-648192
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. 

MORE INFORMATION

For more information about how to customize the quota , click the following article number to view the 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 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

Properties

Article ID: 955644 - Last Review: September 17, 2011 - Revision: 4.0
APPLIES TO
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Workgroup
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Express
Keywords: 
sql2008relnotedatabaseengine sql2008relnote kbhowto kbtshoot KB955644

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com