I recently came across a Sql Server performance problem. It was caused by TokenAndPermUserStore taking too much of cache.

Server specifications:

Windows 2003 R2 SP2 Enterprise b4-bit, Sql Server 2005 Enterprise 64-bit SP2, 2 Quad-Core Dell server with 32 gig of memory

Symptoms that I have experienced:

1. Over time, the application got timeout messages. Task Manager on the server itself didn’t show any CPU, Memory, or IO pressure. It would take me forever to connect to the instance, while I am on the box directly, and open a query window. This happened despite the fact that this is a overpowered 64-bit machine with 8 processors, 32 gig of memory that only serves a handful of small databases;

2. If you issue statement below and inspect its value over time, you will see the value keeps increasing:

Select single_pages_kb + multi_pages_kb from sys.dm_os_memory_clerks where type = ‘userstore_tokenperm’

The reason it took so much space, I think, is because there are tons of temp table creation in user stored procedures. You could bypass this issue if you give the app login sysadmin, but we all know what a brilliant idea that is.

My solution: I put in a job that flushes the Security Token cache daily. I didn’t observe any negative effects, and the performance problem went away. This is the statement I used:

dbcc freesystemcache (‘TokenAndPermUserStore’)
go

Microsoft claims that there are hot fixes for this, but the above DBCC command fixed my woes, so I didn’t bother requesting it.