August 29, 2013 at 11:37 am
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
August 29, 2013 at 2:29 pm
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
August 29, 2013 at 3:12 pm
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]
August 29, 2013 at 3:41 pm
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
August 29, 2013 at 5:16 pm
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.
August 29, 2013 at 7:48 pm
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
March 10, 2014 at 4:06 pm
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
March 10, 2014 at 4:18 pm
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
February 5, 2015 at 2:29 am
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
February 5, 2015 at 9:37 am
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.
February 2, 2017 at 2:21 am
Any update on this?
February 2, 2017 at 6:55 am
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
February 3, 2017 at 4:22 am
No problem
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply