Troubleshooting Performance issues with TokenAndPermUserStore in SQL2005 x64

I've recently seen quite a few performance issues related to the size of the TokenAndPermUserStore cache in x64 SQL2005 so wanted to share a simple technique to manage the size of this cache. The issues around this store are described in KB 92736 however certainly in our case, the symptoms highlighted in the KB article would never had led me to it. Our primary symptom was a massive increase in SOS_SCHEDULER_YIELD waits, our CPU utilisation was the same as normal however less work was getting done. When I finally looked at the size of the TokenAndPermUserStore cache (having recently read the KB article but not really associating it woth the problem) I found it to be over 200MB. Flushing it using DBCC FREESYSTEMCACHE ('TokenAndPermUserStore') immediately relieved the performance issues (you can see this very clearly in the wait graph below)

 

 

 

 

 

 

 

 

 

 

Whilst this issue is still being investigated, there are a number of workarounds in the KB article such as explicitly parameterizing queries, enabling forced parameterization using stored procedures etc which may or may not help. In the meantime, to avoid performance issues, I set up a SQL job that both tracks the size of the store and also flushes it when it reaches a threshold you define. This has shown no detrimental performance impact during the flush operations. One thing to note, as part of the PSS case we applied SQL Hotfix Build 3161 which supposedly contains a fix in this area and now under certain load conditions are not able to free the cache at all, this is something to be aware of and may require using one of the other workarounds. We never saw this issue on plain SP2. There's still a lot of work being done in this area and hopefully a real fix is imminent

First we need a couple of tables to track the size of the cache and record the flush activity

CREATE TABLE [dbo].[TokenAndPermUserStore](
    [loaddate] [datetime] NOT NULL,
   
[sizemb] [decimal](10, 2) NULL,
   
CONSTRAINT [PK_TokenAndPermUserStore] PRIMARY KEY CLUSTERED
   
(
       
[loaddate]
   
)
)
go

CREATE TABLE [dbo].[TokenAndPermFlush](
   
[Flushdate] [datetime] NOT NULL,
   
[SizeMBBefore] [numeric](10, 2) NOT NULL,
   
[SizeMBAfter] [numeric](10, 2) NOT NULL,
   
CONSTRAINT [PK_TokenAndPermFlush] PRIMARY KEY CLUSTERED
   
(
       
[Flushdate]
    )
)

go

Then we need to set up a job to run the following TSQL which will track the size and also flush if it breaches a threshold

declare @SizeMBBefore numeric(10,2)
declare @SizeMBAfter numeric(10,2)

-- set threshold here
declare @ThresholdMB numeric(10,2) ; set @ThresholdMB = 15.0

select @SizeMBBefore = SUM(single_pages_kb + multi_pages_kb)/1024.0
from sys.dm_os_memory_clerks
where [name] = 'TokenAndPermUserStore'

insert dbo.TokenAndPermUserStore(loaddate,sizemb)
select getdate(),@SizeMBBefore

if @SizeMBBefore >= @ThresholdMB
begin

    DBCC FREESYSTEMCACHE ('TokenAndPermUserStore')
    print 'Cleared TokenAndPermUserStore'

    select @SizeMBAfter = SUM(single_pages_kb + multi_pages_kb)/1024.0
    FROM sys.dm_os_memory_clerks
    WHERE name = 'TokenAndPermUserStore'

 

    insert dbo.TokenAndPermFlush select getdate(),@SizeMBBefore,@SizeMBAfter

end

delete dbo.TokenAndPermUserStore where loaddate < dateadd(dd,-14,getdate())
delete dbo.TokenAndPermFlush where Flushdate < dateadd(dd,-14,getdate())

Published 18 July 2007 14:58 by sqldbatips
Filed under:

Comments

19 July 2007 10:29 by GrumpyOldDBA

# re: Troubleshooting Performance issues with TokenAndPermUserStore in SQL2005 x64

This may not be just x64 and appears still present after rollup 3161

try this query to see what's in the cache

select name, count(*) from sys.dm_os_memory_cache_entries

group by name

order by count(*) desc

if TokenAndPermUserStore is the top then there's a problem I figure! I always like to know for myself what's where, just for ref I clocked over 800k for TokenAndPermUserStore - I don't have any noticible slow downs in performance - it's just generally troublesome <grin>

19 July 2007 20:37 by EXEC dbo.LongTermMemory__Archive

# Even more USERSTORE_TOKENPERM

Reading this and this post reminded me of our very bad experience with the USERSTORE_TOKENPERM cache.

19 July 2007 20:56 by WesleyB

# re: Troubleshooting Performance issues with TokenAndPermUserStore in SQL2005 x64

Hi,

What type of plans are exactly in the cache.  We had our cache go 'wild' because of the UpdateBatch behavior that uses the content length instead of the column length for varchar parameters.  This can be solved by using TF 144 which causes the (n)varchar parameters to be stored with their respective maximum length.

27 September 2007 16:30 by EXEC dbo.LongTermMemory__Archive

# Even more USERSTORE_TOKENPERM

Reading this and this post reminded me of our very bad experience with the USERSTORE_TOKENPERM cache

01 October 2007 18:09 by LSC

# re: Troubleshooting Performance issues with TokenAndPermUserStore in SQL2005 x64

fwiw, Build 3186 doesn't appear to fix this problem with the USERSTORE_TOKENPERM cache.  I've installed this latest rollup build and the cache problems persist (ie tps drops to the floor, cpu shoots for the ceiling).  Using TF 4618 seems to keep the cache size smaller, but the cpu overhead under high load makes it unusable for us.  'High load' for us isn't necessarily high tps, it's moderate-to-high load (~3K tps), but with a large range of parameterized queries.  Other servers with higher load (8-9K tps) but a more narrow range of queries seem to survive longer without a cache flush.  The first server with varied query plans/load has to be flushed every 4 hrs to stay afloat.  Anyone know of a fix (or better yet, a TF to turn the USERSTORE_TOKENPERM cache off and/or bypass it) coming in SP3?  

# SQL in the Wild &raquo; Blog Archive &raquo; Memory and SQL 2005 SP2

Pingback from  SQL in the Wild  &raquo; Blog Archive   &raquo; Memory and SQL 2005 SP2

# Problems with Memory consumption 93% SQL 2005 Enterprise32 bits | ButtonForums

Pingback from  Problems with Memory consumption 93% SQL 2005 Enterprise32 bits | ButtonForums

# Problems with Memory consumption 93% SQL 2005 Enterprise32 bits | ButtonForums

Pingback from  Problems with Memory consumption 93% SQL 2005 Enterprise32 bits | ButtonForums

Thanks for sharing your feedback! If your feedback doesn't appear right away, please be patient as it may take a few minutes to publish - or longer if the blogger is moderating comments.