I recently encountered this issue in the wild. It's worth implementing a tool to record the size of your TokenAndPermUserStore before you run into problems of your own.
Problem
There is a bug (KB:927396) with SQL Server 2005 related to TokenAndPermUserStore cache. The bug appears to exist in all versions and under all service packs for SQL 2005. Additionally the bug has different manifestations which may require different hotfixes. The users in DBA forums do not believe that the hotfixes fully address issue. There is at least one hotfix which requires contacting Microsoft support to obtain. At this time I cannot confirm that we need that hotfix.
Systems exhibiting the issue do not always show the same symptoms. However, the commonality I found in documented experiences is that all of these servers have large amounts of RAM, have heavy transaction volumes and at some point in time become unable to accept new connections or run queries.
The TokenandPermUserStore cache is used by SQL Server to store security related information. The items stored in this cache include: LoginTocken, SecContext Token, TokenAccessResult, TokenPerm and UserToken. On RECHOUVSQL01 the majority of the tokens are of class 65535.
Microsoft Customer Service and Support (CSS) offer an article with technical details on the issue.
Symptoms
Queries that typically run faster take a longer time to finish running. | |
CPU usage of SQL Server process is relatively higher. CPU usage could come down after remaining high for a period of time. | |
Connections from your applications keep increasing (specifically in connection pool environments) | |
You encounter connection or query timeouts | |
When you experience decreased performance when you run an ad hoc query, you view the query from the sys.dm_exec_requests or sys.dm_os_waiting_tasks dynamic management view. However, the query does not appear to be waiting for any resource. | |
The size of the TokenAndPermUserStore cache store grows at a steady rate. | |
The size of the TokenAndPermUserStore cache store is in the order of several hundred megabytes (MB). | |
In some cases, execution of the DBCC FREEPROCCACHE command provides temporary relief. |
References and Documentation
Microsoft KB Explaining Symptoms (927396)Query for freeing TokenAndPermUserStore when it reaches 100 MB
Query Performance issues associated with a large sized security cache (very detailed)
Related Hotfixes:
Service Pack 3
KBA: 959823 - How to customize the quota for the TokenAndPermUserStore cache store in SQL Server 2005 Service Pack 3Service Pack 2
Cumulative Updates: In order to get all of these fixes, you can install the Cumulative update package 3 for SQL Server 2005 Service Pack 2. This will take you to build 9.00.3186.00. You might also install a later Cumulative Update package and that will include all of these fixes as well.SQL Server 2005 SP2 build [9.00.3042.00]
- TokenAccessResult security cache entries for ad hoc queries are cached when a specific ad hoc query is executed for the second time
- KBA: 927396: Queries take a longer time to finish running when the size of the TokenAndPermUserStore cache grows in SQL Server 2005
SQL Server 2005 post SP2 hotfix build [9.00.3153.00]
- Fix to prevent duplication of TokenAccessResult entries due to change in the protection timestamp used.
- KBA: 933564: A gradual increase in memory consumption for the USERSTORE_TOKENPERM cache store occurs in SQL Server 2005
SQL Server 2005 post SP2 hotfix build [9.00.3171.00]
- Fix to prevent memory allocation contention for TokenAccessResult entries
- KBA: 937745: 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
SQL Server 2005 post SP2 hotfix build [09.00.3179.00]
- Fix to prevent Memory consumption increase by the TokenandPermUserStore even if the number of entries does not increase
- KBA: 939871: Not yet published to support.microsoft.com
No comments:
Post a Comment