USERSTORE_TOKENPERM is Huge

Question

USERSTORE_TOKENPERM is Huge

FarNorthDBA

SSCrazy

Points: 2075
More actions
August 29, 2013 at 11:37 am

#297283

I'm having problems with my USERSTORE_TOKENPERM cache becoming growing. When I came in this morning it was 2.4 gb and was actually larger than my buffer pool. I cleared it out and an hour later it is already back up to 1.8 gb. All the information I can find talks about sql server 2005 and it sounds like this was supposed to be fixed long ago. Below is the token class that is taking up the most space. Anyone have any ideas?
TokenCount Token Name Class SubClass
167214 TokenAccessResult655350

Viewing 13 posts - 1 through 12 (of 12 total)

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

nathan 7372 (8/29/2013)
I'm having problems with my USERSTORE_TOKENPERM cache becoming growing. When I came in this morning it was 2.4 gb and was actually larger than my buffer pool. I cleared it out and an hour later it is already back up to 1.8 gb. All the information I can find talks about sql server 2005 and it sounds like this was supposed to be fixed long ago. Below is the token class that is taking up the most space. Anyone have any ideas?
TokenCount Token Name Class SubClass
167214 TokenAccessResult655350

Lots of questions and details needed

1) what exact build of sql server?

2) how are you determining various memory sizes mentioned?

3) how are you "clearing it out"?

4) what windows version and is everything 32 or 64 bit (or a mis-mash)?

5) anything useful from sys.dm_os_memory_cache_entries (if you weren't using that already)?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

FarNorthDBA SSCrazy Points: 2075 More actions · Answer 2

1) The build is 11.0.3128

2) I'm getting the memory sizes from sys.dm_os_memory_clerks

3) I clear it out using DBCC FREESYSTEMCACHE (‘TokenAndPermUserStore’)

4) Windows Server 2008 R2 64 bit. Sql server is 64 bit. The application that is using the sql server is 32 bit

5) This is what is in sys.dm_os_memory_cache_entries for userstore_tokenperm. The code I used to get this is below. Most of these numbers are much higher than any of my other systems.

TokenCount Token Name Class SubClass

7 LoginToken 25 0

6585 SecContextToken 25 0

10760 TokenAccessResult0 26

97 TokenAccessResult1 2

17612 TokenAccessResult1 26

1 TokenAccessResult21 26

1 TokenAccessResult22 26

167214 TokenAccessResult655350

10760 TokenPerm 0 26

23057 TokenPerm 1 2

1 TokenPerm 21 26

1 TokenPerm 22 26

17360 UserToken 7 0

SELECT COUNT(*) as TokenCount, *

FROM

(SELECT

x.value('(//@name)[1]', 'varchar (100)') AS [Token Name],

x.value('(//@class)[1]', 'bigint') AS [Class],

x.value('(//@subclass)[1]', 'int') AS [SubClass]

FROM

(SELECT CAST (entry_data as xml)

FROM sys.dm_os_memory_cache_entries

WHERE type = 'USERSTORE_TOKENPERM')

AS R(x)

) a

GROUP BY [Token Name],[Class],[SubClass]

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

Yeah - pretty out of whack there.

A) You sound like you know what you are doing, so have you tried some of the 2005/2008 stuff (such as here: http://blogs.msdn.com/b/psssql/archive/2008/06/16/query-performance-issues-associated-with-a-large-sized-security-cache.aspx)?

B) does the 32 bit app blow out a massive number of connections? massive number of ad hoc queries?

C) Are procedure and/or query caches also bloated?

D) Have you checked post-SP1 CUs for fixes applicable to this problem? They are up to CU5 now for SP1.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

FarNorthDBA SSCrazy Points: 2075 More actions · Answer 4

I followed the directions in this article http://support.microsoft.com/default.aspx?scid=kb;EN-US;959823

Trace flags 4618, 4610, and 4621 seem to have no effect on the caches ability to grow. It continues to grow regardless of these trace flags.

I have only ever seen 2 connections from the app to the sql server at any one time. The app tends to just keep a persistent connection open.

All the other caches look fine and there are almost no adhoc queries run on the server.

I hadn't thought of looking at the CU but after going through all 5 of them none of them seemed to address my issue.

I am completely out of ideas and I wonder if it is time to make a call to microsoft.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

I actually meant to add E) Call Microsoft! It is definitely (past?) time! Best of luck with it. Please post back here if/when you get things figured out so we can all benefit and it will become searchable.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

FarNorthDBA SSCrazy Points: 2075 More actions · Answer 6

Nothing like a little necromancy. Here is the current status of this for anyone else searching on this problem. Using sp_setapprole and sp_unsetapprole will cause the userstore_tokenperm to grow and SQL will not reclaim the memory in Server 2012. Microsoft is aware of this but as of right now have no response. Below is a script that will reproduce the problem for anyone interested. Ignore the terrible capitalization.

CREATE APPLICATION ROLE [AppRole] WITH DEFAULT_SCHEMA = [dbo], PASSWORD = N'password'

declare @counter int = 1

While(@counter < 2000)

Begin

DECLARE @cookie varbinary(8000);

EXEC sp_setapprole @rolename = 'AppRole', @password = 'password', @fCreateCookie = true, @cookie = @cookie OUTPUT;

select user_name();

EXEC sp_unsetapprole @cookie;

--GO

SELECT USER_NAME();

SET @counter = @counter + 1

end

SQLRNNR SSC Guru Points: 281324 More actions · Answer 7

That's good news.

The token perm has been a constant problem in 2008, R2 and 2012 though it should be less common than it was in 2005.

it's good to know the root cause in this case.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

gaojia2004 Old Hand Points: 399 More actions · Answer 8

My build is SQL SERVER 2012 SP2(10.0.5058.0) and there are 128 GB or 256GB on my servers, the "TokenAndPermUserStore" used more than 17 GB memory, we meet the non-yield problem on a few servers, ONCE the non-yield error appeared on your server, the best way to fix it is "restart your server ASAP"

the query result on my server:

TokenCountToken NameClassSubClass

14LoginToken250

251888SecContextToken250

278162TokenAccessResult026

14584TokenAccessResult12

8057TokenAccessResult126

139284TokenAccessResult2126

139261TokenAccessResult2226

46TokenAccessResult2426

23066TokenAccessResult2526

3578TokenAccessResult655350

314955TokenPerm026

1121230TokenPerm12

139245TokenPerm2126

139245TokenPerm2226

4370TokenPerm2426

23066TokenPerm2526

375606UserToken70

thanks

FarNorthDBA SSCrazy Points: 2075 More actions · Answer 9

I've still been working with Microsoft off and on to try and get this permanently fixed. Their current stance is still that if you are having this issue set trace flag 4618. I will post an update here if I ever get them to acknowledge there is a problem with their memory manager around this kind of workload.