patternsqlMinor
TokenAndPermUserStore Clear decreases CPU usage for a short period of time
Viewed 0 times
tokenandpermuserstoreperiodshortdecreasestimeusageforclearcpu
Problem
Intro
In short, there are a lot of ad hoc queries occuring on my server, from an application which I do not control and cannot change (Even pushing indexes is hard, and they use many heaps... ).
Specs
OS - Windows Server 2012 R2 (Primary Node)
SQL Server 2014 - 12.0.5546
Always On AG With the secondary synchronous node with the same hardware + Build.
We can only use 12 of the 24 cores for sql server due to licensing ( I did not do this). It's quite easy to spot which 12 cores ;).
The problem
Now as to my problem. At the moment, every 30 minutes we clear out the "TokenAndPermUserStore". This was happening on the server even before it came in my hands. We did this with the command:
I use this query to check the cache:
Right after the clear this is the cache size:
At a certain point in time, for example 15 Minutes after the clear this is the cache size:
Update:
Now, when CPU used is stable again (40% used (20% on monitoring), the cache is way below the lowest point it was when CPU usage was high.
An example of yesterday:
The drops are very present on this picture of yesterday:
(Note as we can use 12 of the 24 cores, 50 % Means 100% in the monitoring software, in other words the cpu usage will probably not exceed 50% because it is dedicated to sql server only)
One important thing to note is, we added two important indexes on the top queries yesterday, because of the CPU almost flat lining, which helped for a short period, but the cpu rose to the same level again, with no noticable queries that should hammer our system this hard.
The question
Now, to my question, today, i tried clearing the cache more frequently, by executing
In short, there are a lot of ad hoc queries occuring on my server, from an application which I do not control and cannot change (Even pushing indexes is hard, and they use many heaps... ).
Specs
OS - Windows Server 2012 R2 (Primary Node)
SQL Server 2014 - 12.0.5546
Always On AG With the secondary synchronous node with the same hardware + Build.
We can only use 12 of the 24 cores for sql server due to licensing ( I did not do this). It's quite easy to spot which 12 cores ;).
The problem
Now as to my problem. At the moment, every 30 minutes we clear out the "TokenAndPermUserStore". This was happening on the server even before it came in my hands. We did this with the command:
DBCC FREESYSTEMCACHE ('TokenAndPermUserStore')I use this query to check the cache:
SELECT SUM(pages_kb) / 1024 AS
"CurrentSizeOfTokenCache(mb)"
FROM sys.dm_os_memory_clerks
WHERE name = 'TokenAndPermUserStore'Right after the clear this is the cache size:
CurrentSizeOfTokenCache(mb)
1602At a certain point in time, for example 15 Minutes after the clear this is the cache size:
CurrentSizeOfTokenCache(mb)
1976Update:
Now, when CPU used is stable again (40% used (20% on monitoring), the cache is way below the lowest point it was when CPU usage was high.
CurrentSizeOfTokenCache(mb)
1281An example of yesterday:
The drops are very present on this picture of yesterday:
(Note as we can use 12 of the 24 cores, 50 % Means 100% in the monitoring software, in other words the cpu usage will probably not exceed 50% because it is dedicated to sql server only)
One important thing to note is, we added two important indexes on the top queries yesterday, because of the CPU almost flat lining, which helped for a short period, but the cpu rose to the same level again, with no noticable queries that should hammer our system this hard.
The question
Now, to my question, today, i tried clearing the cache more frequently, by executing
Solution
Than you all for your time and effort in finding a solution. Especially @David Browne - Microsoft since he was correct by letting me know that we should patch.
We had a meeting with the dba's, the application owners and the technical team of the application vendor.
In this meeting it came to light that the same issue is present for the other clients that the vendor has, due to the nature of the application and its code.
Similar to this.
The solution that has helped their other clients with this issue, is to upgrade to SQL Server 2014 CU7 or SP3, which we will be doing as soon as possible (SP3 preferably), which should put an end to the 'tokenandpermuserstore' issues.
We had a meeting with the dba's, the application owners and the technical team of the application vendor.
In this meeting it came to light that the same issue is present for the other clients that the vendor has, due to the nature of the application and its code.
Similar to this.
The solution that has helped their other clients with this issue, is to upgrade to SQL Server 2014 CU7 or SP3, which we will be doing as soon as possible (SP3 preferably), which should put an end to the 'tokenandpermuserstore' issues.
Context
StackExchange Database Administrators Q#222734, answer score: 2
Revisions (0)
No revisions yet.