More TokenAndPermUserStore

I was interested in Jasper’s post

 

http://sqlblogcasts.com/blogs/sqldbatips/archive/2007/07/18/troubleshooting-performance-issues-with-tokenandpermuserstore-in-sql2005.aspx?CommentPosted=true#commentmessage

 

The following may also be of interest to readers.

 

http://support.microsoft.com/default.aspx/kb/927396

http://blogs.msdn.com/sqlprogrammability/archive/2007/01/23/4-0-useful-queries-on-dmv-s-to-understand-plan-cache-behavior.aspx

 

I’m currently working on optimizing a SQL 2005 application, it would appear the main issues are probably within the app rather than sql server, but that’s another story!

As part of the optimization we’ve deployed a number of plan guides and as part of that process I’ve been clearing the cache :-

 

DBCC FREESYSTEMCACHE ('TokenAndPermUserStore')

go

 

However of late we’re waiting on some changes before continuing – here’s the results I got from my server ( names changed to protect the innocent ) with this query:

Be careful how you run this on a production server.

( my cache size at the time was 450mb btw )

I’d probably be inclined to track all the counts over time as on my system this is extremely volatile, my proc cache varies between 800mb and 2.1 gb

 

select name, count(*) from sys.dm_os_memory_cache_entries

group by name

order by count(*) desc ;

 


 

name

count

Bound Trees

108

Broker dormant rowsets

17

Database4

77

Extended Stored Procedures

18

Database5

23

master

139

model

26

msdb

200

mssqlsystemresource

112

Object Plans

147

ObjPerm - master

16

ObjPerm - mssqlsystemresource

1

ObjPerm - tempdb

1

ObjPerm – Database1

49

ObjPerm – Database2

1843

SchemaMgr Store

123

Service broker mapping table

1

Service Broker Null Remote Service Binding Cache

1

Service broker routing cache

3

SOS_StackFramesStore

1

SQL Plans

7871

sxcCacheStore

52

SystemRowsetStore

354

tempdb

63

Temporary Tables & Table Variables

12

TokenAndPermUserStore

837993

Database1

1392

Database3

35

Database2

4208

 

You can also use this query, provided by Microsoft  “EMEA Customer Service & Support - SMS&P” , thank you.

 

 select * from sys.dm_os_memory_cache_entries

      where cast(entry_data as xml).value('(//@class)[1]', 'bigint') = 65535;

 

As a footnote I’m using 32 bit sql on 64bit o/s with rollup 3161 applied.

 

Published 19 July 2007 11:06 by GrumpyOldDBA
Filed under: ,

Comments

# re: More TokenAndPermUserStore

19 July 2007 17:04 by ChrisAVWood

Using your Select name, count(*) from sys.dm_os_memory_cache_entries query I can see TokenAndPermUserStore is the highest entry. We are running build 3159 on 32 bit.

Is this what can be expected in SQL2005? I can see that the 933564 fix is included in build 3159. So should I be worried about this build-up? Is it an indication of too many adhoc queries?

Thanks

Chris

# re: More TokenAndPermUserStore

19 July 2007 20:04 by GrumpyOldDBA

There's a size threshold, I think Jasper set his at 15Mb. I have an incident raised with microsoft - as quite rightly my contact considered this fixed in 3161, the "bug" is reported as fixed. I'll add to the post or post afresh if and when i get an answer - we're looking at this from an application point of view, it does have a number of ad-hoc queries, I intend to write up how I/we ( microsoft ) are dealing with this with a view to the application, but I suspect it will take a while - I expect to publish around 4 more parts to my index blog which are all part of the same application tuning project.

# Even more USERSTORE_TOKENPERM

19 July 2007 20:37 by EXEC dbo.LongTermMemory__Archive

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

# re: More TokenAndPermUserStore

19 July 2007 22:56 by GrumpyOldDBA

It seems thta maybe this is a problem still with us. I don't have to create test scripts - I have an application which does it for me!! I promise if I get to the bottom of resolving this I will post it. It's possible using plan guides to force parameterisation of ad-hoc queries which are not, may be the solution.

# correction!!

20 July 2007 10:32 by GrumpyOldDBA

oops - my prod system is all 32 bit, it's my test which is 32 bit on 64bit, sorry! ( Thanks Iain )

# re: More TokenAndPermUserStore

22 July 2007 17:50 by WesleyB

It would be great if you resolve this and even better if you keep us informed about the possible solution of course.

Have you checked tf 144 already?  Because forcing parameterization didn't help either in our case.

http://sqlug.be/blogs/wesleyb/archive/2006/09/06/474.aspx

# re: More TokenAndPermUserStore

23 July 2007 15:13 by GrumpyOldDBA

Microsoft say apply rollup 3175 as there are fixes specific to the TokenAndPermUserStore in this rollup. There's no further comments so I'll need to do this and monitor some more.

# re: More TokenAndPermUserStore

24 July 2007 09:06 by GrumpyOldDBA

I've been collecting the size of my store every hour the last few days and I've discovered that DBCC FREESYSTEMCACHE ('TokenAndPermUserStore')

doesn't actually make any difference at all, in my case. That means http://support.microsoft.com/default.aspx/kb/927396  is technically incorrect.

# re: More TokenAndPermUserStore

26 July 2007 19:04 by WesleyB

We have been conducting some tests too and even with build 3175 we are seeing that the freesystemcache can not keep up with the rate it is growing.  It is not causing SQL Server to crash like it did before however.  I'm very interested in your case since we are migrating our most important server to 2005 in September and hitting the userstore issue a second time would be a real disaster.

# re: More TokenAndPermUserStore

26 July 2007 20:53 by GrumpyOldDBA

I personally feel that much of my issues are down to the application, it all takes time however! I've run some pssdiag traces for microsoft so we'll see what they have to say. I will say they're being helpful but they're not really answering specific questions < grin > I may bend Tony's ear tomorrow < groan >

# re: More TokenAndPermUserStore

01 August 2007 12:36 by WesleyB

You might want to check this out :-)

http://sqlug.be/blogs/wesleyb/archive/2007/08/01/tokenandpermuserstore-continued.aspx

# An update on the TokenAndPermUserStore “problem”

24 September 2007 13:56 by Grumpy Old DBA

http://sqlblogcasts.com/blogs/grumpyolddba/archive/2007/07/19/more-tokenandpermuserstore.aspx As the

# Even more USERSTORE_TOKENPERM

27 September 2007 16:30 by EXEC dbo.LongTermMemory__Archive

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

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

02 May 2008 15:47 by SQL in the Wild » Blog Archive » Memory and SQL 2005 SP2

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