USERSTORE_TOKENPERM is Huge

  • 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

  • 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

  • 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]

  • 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

  • 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.

  • 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

  • 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

  • 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

  • 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

  • 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.

  • Any update on this?

  • Anchelin, please don't post on 3 year old posts. If you have a similar question that hasn't been answered here please post a new thread. Thanks!

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

  • No problem

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

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