TokenAndPermUserStore Cache Store Growing in SS2008

Question

Post reply

TokenAndPermUserStore Cache Store Growing in SS2008

sql v-line

SSC Veteran

Points: 271
More actions
March 23, 2010 at 2:27 am

#231111

Hi. all
My Environment :
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)
16 CPU, 32 GB, SQL Max Server Memory : 27 GB
ref: http://support.microsoft.com/default.aspx/kb/927396
I've been collecting the size of my store every hour the last few days.
SELECT getdate(), SUM(single_pages_kb + multi_pages_kb)/1024.0 AS
"CurrentSizeOfTokenCache(kb)"
FROM sys.dm_os_memory_clerks
WHERE name = 'TokenAndPermUserStore'
-- > 454.164062 MB
Keep growing 100 MB / day.
DBCC FREESYSTEMCACHE('TokenAndPermUserStore')
doesn't make any difference at all.
Till TokenAndPermUserStore Size Keep growing.
Anyone can explain why the DBCC FREESYSTEMCACHE didn't work in SQL Server 2008 ?
Thanks for everyone's contribution.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

Just posting to confirm that

DBCC FREESYSTEMCACHE('TokenAndPermUserStore')

...works correctly in SQL Server 2008 Enterprise version 10.0.2757

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SQLBOT SSCrazy Eights Points: 8014 More actions · Answer 2

I know you're not necessarily asking this, but your issue is the result of too much ad-hoc SQL and the caching of those permissions.

Microsoft will tell you to reduce the amount of Ad-hoc SQL on your system.

In reality this can't be helped on some vended systems and there are only a couple of options left.

Turning on forced parameterization is one of them, setting trace 4621 (SQL 2005 SP3+) is another

Fortunately, in SQL 2008, they built trace 4621 into the sp_configure options

http://support.microsoft.com/kb/955644

Good luck.

Craig Outcalt

Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

sql v-line SSC Veteran Points: 271 More actions · Answer 3

sql v-line

SSC Veteran

Points: 271

March 23, 2010 at 8:38 pm

#1138671

hmm

sql v-line SSC Veteran Points: 271 More actions · Answer 4

Paul White NZ (3/23/2010)
Just posting to confirm that
DBCC FREESYSTEMCACHE('TokenAndPermUserStore')
...works correctly in SQL Server 2008 Enterprise version 10.0.2757

hi, Paul White NZ

thanks for the replies.

[Cumulative update package 6 for SQL Server 2008 Service Pack 1]

http://support.microsoft.com/?scid=kb;en-us;977443&x=10&y=1

I can't find VSTS bug number or KB article number about "DBCC FREESYSTEMCACHE"

Could you give me VSTS bugs number or KB article ?

Thanks for your help.