On versions of SQL Server before 9.0.3171.0, 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.
- 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