I wrote about TokenandPermUserStore issue a while back. I revisited the issue recently on 2 production servers to see if it was still an issue on a fairly recent SQL build.
The two servers in question run the same application, but with a vast difference in usage.
Server 1 – Itanium, 12 processors, 48 GB memory, build 3042 (SP2) running on Windows Server 2003 DataCenter SP1
Server 2 – x64, 16 processors, 64 GB memory, build 3215 (SP2 CU5) running on Windows Server 2003 DataCenter SP2
On both servers there’s a job that checks the size of the token cache and flushes it if it’s above 750 MB. The size was set because when I first set up the jobs, I noticed that the CMEMTHREAD waits were getting noticeable if the cache got above 1 GB.
On both servers, I set up a job that would log the size of the cache to a table. On server 1, that ran every 15 min. On server 2 it ran every 5 min. The results were a little frightening.
Server 1
On server 1, during working hours, it takes about 2 and a half hours for the token cache to reach 750 MB. I’ve seen noticeable CMEMTHREAD waits (> 100ms as shown by sys.dm_exec_requests) above 1 GB
Server 2
On server 2, the token cache reaches around 750 MB within 45 minutes. I don’t work on that server myself, but apparently the waits become noticeable after around 2.5 GB
From what I know, there are three requirements to encounter this problem:
- Lots of memory (16+ GB)
- Multiple different logins/usernames
- Lots of dynamic/ad-hoc SQL
The problem was supposed to be fixed in SP2, then fixed again in a patch included in the 4th cumulative update, but as can be seen, it’s still a problem even on CU5.
If anyone knows if there’s a resolution to this in a later CU or in SQL Server 2008, I’d be very happy to hear about it.