There were performance issues identified on SQL Server 2005 builds because of the large size of the access cache store on certain servers with huge ad-hoc query workloads. I had blogged about this earlier along with information on how to customize the cache store size using trace flags and registry entries after a certain build of SQL Server 2005.
With SQL Server 2008, we provide access check cache bucket count and access check cache quota size customization through sp_configure. So, if you do need to change these values, you do not have to add trace flags or make registry changes. It is as simple as changing the MaxDop or Max Server Memory on your server.
However, be advised that it is not recommended to change these values unless and until CSS recommends you to do so.
Description of the "access check bucket count" and "access check cache quota" options that are available in the sp_configure stored procedure in SQL Server 2008
I had written about the TokenPerm cache store in a previous blog post
of mine. The trace flags mentioned in the KB Articles helped in throttling the cache to prevent the cache size from growing out-of-proportions. Recently, a new Trace Flag (-T4621
) was added SQL Server 2005 Service Pack 3
(KB959823) to customize the size of the Token Perm cache. Instead of hard coding the quota for Token Perm with the earlier trace flags, you can use the formula to specify the Registry value:
Quota = 1,024 * 2 * ( <Number of Distinct Logins> + <Total Number of Users in each database> )
Remember to turn off the other trace flags for Token Perm issues that you had activated on your systems previously. You cannot enable trace flag 4621 together with trace flag 4618. When trace flag 4621 and trace flag 4618 are enabled together, trace flag 4618 takes precedence. The reason this trace flag was introduced was to prevent detrimental effect to systems where constant flushing of the Token Perm cache would affect the performance rather than helping it.
On versions of SQL Server 2005, a known issue can degrade query performance on 32-bit and 64-bit systems with 2 gigabytes (GB) or more of memory. When you execute queries under the context of a login that is not a member of the sysadmin fixed server role, you might encounter performance degradation symptoms that arise from a large and growing Security Token cache. These issues can include performance degradation of queries, high CPU usage for the SQL Server process, and a sharp increase in worker threads and SQL user connections. Systems that have less than 2 GB of memory should not experience these issues because normal memory usage requirements keep the Security Token cache from growing too large.
If you are facing the symptoms below, then it is a good time to discuss applying SP3 with CU1 with your management or apply the workarounds mentioned in the articles below. The most common workaround is having a job running at periodic intervals and cleaning up the TOKENPERM cache using DBCC FREESYSTEMCACHE (‘TokenAndPermUserStore’).
- A large number of users running queries on the SQL instance and intermittently you find that the duration of queries increases intermittently
- Most of the queries in the environment exhibit this pattern for random parameters and not any specific query/proc with any specific parameter
- DBCC MEMORYSTATUS output for the SQL instance shows that the memory usage for SQL is gradually increasing over a period of time
- High CPU during the problem period
- DBCC FREEPROCCACHE or DBCC FREESYSTEMCACHE fixes the issue temporarily
KB article 927396: Queries take a longer time to finish running when the size of the TokenAndPermUserStore cache grows in SQL Server 2005
KB article 933564: FIX: A gradual increase in memory consumption for the USERSTORE_TOKENPERM cache store occurs in SQL Server 2005
KB article 937745: FIX: You may receive error messages when you try to log in to an instance of SQL Server 2005 and SQL Server handles many concurrent connections
KB Article 959823: How to customize the quota for the TokenAndPermUserStore cache store in SQL Server 2005 Service Pack 3