July 2007 - Posts

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,

CREATE TABLE [dbo].[TokenAndPermFlush](
[Flushdate] [datetime] NOT NULL,
[SizeMBBefore] [numeric](10, 2) NOT NULL,
[SizeMBAfter] [numeric](10, 2) NOT NULL,


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

    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


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

Posted by sqldbatips | 8 comment(s)
Filed under:

A couple of SQL tools are available for download this week. First off is the July 2007 release of SQL 2005 BPA (Best Practice Analyzer). New and updated BPA rules will now be released independent of SQL Service Packs (read more here). Second of all is the DMVStats tool, an application that can collect, analyze and report on SQL Server 2005 DMV performance data, written by members of the SQL Server Customer Advisory Team

Download SQL 2005 BPA (July 2007)

Download DMVStats



Posted by sqldbatips | with no comments
Filed under: ,