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’).

Symptoms

1. A large number of users running queries on the SQL instance and intermittently you find that the duration of queries increases intermittently
2. Most of the queries in the environment exhibit this pattern for random parameters and not any specific query/proc with any specific parameter
3. DBCC MEMORYSTATUS output for the SQL instance shows that the memory usage for SQL is gradually increasing over a period of time
4. High CPU during the problem period
5. DBCC FREEPROCCACHE or DBCC FREESYSTEMCACHE fixes the issue temporarily

Related Articles