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
Pingback: Token Perm issues on SQL Server 2008 « TroubleshootingSQL