8

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.

Memory:

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 ;). Cpu usage

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)
1602

At a certain point in time, for example 15 Minutes after the clear this is the cache size:

CurrentSizeOfTokenCache(mb)
1976

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.

CurrentSizeOfTokenCache(mb)
1281

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)

enter image description here

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 

DBCC FREESYSTEMCACHE ('TokenAndPermUserStore')

a few times manually. But it seemed, that after around 20 seconds, the cpu usage came back with a vengeance.

You can clearly see the three dips after executing the command, but it coming back rather fast in the picture below.

enter image description here

Should I schedule the command more, should i look at other changes?

I know that this issue was prevalent in SQL Server 2005, but this is SQL Server 2014. The queries are sp_executesql type queries.

If you need more information, or clarification, don't hesitate to let me know.

Update as of 05/12/2018

Query plan: https://www.brentozar.com/pastetheplan/?id=BkUKKVByV

--> Paste the plan is creating the same link for the three plans found. I tried adding all three XML plans found in the cache for the same query, each with 10 executions, and got the same link for each one.

Query used

    SELECT 
  text, execution_count,
dm_exec_query_stats.creation_time, dm_exec_query_plan.query_plan
FROM sys.dm_exec_query_stats 
CROSS APPLY sys.dm_exec_sql_text(dm_exec_query_stats.plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)

results for three of the same queries:

enter image description here

I do want to note, the queries are using SNAPSHOT ISOLATION , by setting it before executing the query, and using the hints OPTION (KEEP PLAN, KEEPFIXED PLAN, LOOP JOIN)

Query 1

(@SV1 nvarchar(8),@SV2 nvarchar(8),@SV3 nvarchar(8),@SV4 nvarchar(8),@SV5 nvarchar(8),@SV6 nvarchar(8),@SV7 nvarchar(8),@SV8 nvarchar(8),@SV9 nvarchar(8),@SV10 nvarchar(8),@SV11 nvarchar(8),@SV12 nvarchar(8),@SV13 nvarchar(8),@SV14 nvarchar(8),@SV15 nvarchar(8),@SV16 nvarchar(8),@SV17 nvarchar(8),@SV18 nvarchar(8),@SV19 nvarchar(8))  IF @@TRANCOUNT = 0 SET TRANSACTION ISOLATION LEVEL SNAPSHOT  SELECT AA.[SourceCode],AA.[DOUBLEMEDICATIONSVALIDATED],AA.[BSTNUM],AA.[MUTKOD],AA.[VERVALLEN],AA.[BACKUPID],AA.[LAATSTE],AA.[ExterneCode],AA.[PRKODE],AA.[NMMEMO],AA.[NMETIK],AA.[NMNM40],AA.[NMNAAM],AA.[PRNMNR],AA.[PRKBST],AA.[GPKODE],AA.[DRMLGEN],AA.[Anticoagulant],AA.[HPKSubstancesDiff],AA.[HPKCIsDiff],AA.[HPKUndesiredGroupsDiff]  FROM [dbo].[ZINDEX_050] AA  WHERE EXISTS (SELECT NULL  FROM (SELECT TOP 100 PERCENT  A.[BSTNUM],A.[MUTKOD],A.[VERVALLEN],A.[BACKUPID],A.[DMPRKA],A.[DMPRKB],A.[DMCODE],A.[DMGRDCODE]  FROM [dbo].[ZINDEX_671] A  WHERE ((A.[VERVALLEN] = 0 OR A.[VERVALLEN] IS NULL) AND ((A.[DMPRKA] = @SV1 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV4) OR  (A.[DMPRKA] = @SV5 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV6 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV7) OR  (A.[DMPRKA] = @SV8 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV9 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV10) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV11) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV12) OR  (A.[DMPRKA] = @SV13 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV14) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV15) OR  (A.[DMPRKA] = @SV16 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV17 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV18 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV19)))  ) A  WHERE AA.[PRKODE] = A.[DMPRKA])  OPTION (KEEP PLAN, KEEPFIXED PLAN, LOOP JOIN)    SELECT AB.[SourceCode],AB.[DOUBLEMEDICATIONSVALIDATED],AB.[BSTNUM],AB.[MUTKOD],AB.[VERVALLEN],AB.[BACKUPID],AB.[LAATSTE],AB.[ExterneCode],AB.[PRKODE],AB.[NMMEMO],AB.[NMETIK],AB.[NMNM40],AB.[NMNAAM],AB.[PRNMNR],AB.[PRKBST],AB.[GPKODE],AB.[DRMLGEN],AB.[Anticoagulant],AB.[HPKSubstancesDiff],AB.[HPKCIsDiff],AB.[HPKUndesiredGroupsDiff]  FROM [dbo].[ZINDEX_050] AB  WHERE EXISTS (SELECT NULL  FROM (SELECT TOP 100 PERCENT  A.[BSTNUM],A.[MUTKOD],A.[VERVALLEN],A.[BACKUPID],A.[DMPRKA],A.[DMPRKB],A.[DMCODE],A.[DMGRDCODE]  FROM [dbo].[ZINDEX_671] A  WHERE ((A.[VERVALLEN] = 0 OR A.[VERVALLEN] IS NULL) AND ((A.[DMPRKA] = @SV1 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV4) OR  (A.[DMPRKA] = @SV5 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV6 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV7) OR  (A.[DMPRKA] = @SV8 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV9 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV10) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV11) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV12) OR  (A.[DMPRKA] = @SV13 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV14) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV15) OR  (A.[DMPRKA] = @SV16 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV17 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV18 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV19)))  ) A  WHERE AB.[PRKODE] = A.[DMPRKB])  OPTION (KEEP PLAN, KEEPFIXED PLAN, LOOP JOIN)    SELECT A.[BSTNUM],A.[MUTKOD],A.[VERVALLEN],A.[BACKUPID],A.[DMPRKA],A.[DMPRKB],A.[DMCODE],A.[DMGRDCODE]  FROM [dbo].[ZINDEX_671] A  WHERE ((A.[VERVALLEN] = 0 OR A.[VERVALLEN] IS NULL) AND ((A.[DMPRKA] = @SV1 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV4) OR  (A.[DMPRKA] = @SV5 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV6 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV7) OR  (A.[DMPRKA] = @SV8 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV9 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV10) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV11) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV12) OR  (A.[DMPRKA] = @SV13 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV14) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV15) OR  (A.[DMPRKA] = @SV16 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV17 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV18 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV19)))  ORDER BY A.[DMPRKA] ASC,A.[DMPRKB] ASC  OPTION (KEEP PLAN, KEEPFIXED PLAN, LOOP JOIN)

Query 2

(@SV1 nvarchar(8),@SV2 nvarchar(8),@SV3 nvarchar(8),@SV4 nvarchar(8),@SV5 nvarchar(8),@SV6 nvarchar(8),@SV7 nvarchar(8),@SV8 nvarchar(8),@SV9 nvarchar(8),@SV10 nvarchar(8),@SV11 nvarchar(8),@SV12 nvarchar(8),@SV13 nvarchar(8),@SV14 nvarchar(8),@SV15 nvarchar(8),@SV16 nvarchar(8),@SV17 nvarchar(8),@SV18 nvarchar(8),@SV19 nvarchar(8))  IF @@TRANCOUNT = 0 SET TRANSACTION ISOLATION LEVEL SNAPSHOT  SELECT AA.[SourceCode],AA.[DOUBLEMEDICATIONSVALIDATED],AA.[BSTNUM],AA.[MUTKOD],AA.[VERVALLEN],AA.[BACKUPID],AA.[LAATSTE],AA.[ExterneCode],AA.[PRKODE],AA.[NMMEMO],AA.[NMETIK],AA.[NMNM40],AA.[NMNAAM],AA.[PRNMNR],AA.[PRKBST],AA.[GPKODE],AA.[DRMLGEN],AA.[Anticoagulant],AA.[HPKSubstancesDiff],AA.[HPKCIsDiff],AA.[HPKUndesiredGroupsDiff]  FROM [dbo].[ZINDEX_050] AA  WHERE EXISTS (SELECT NULL  FROM (SELECT TOP 100 PERCENT  A.[BSTNUM],A.[MUTKOD],A.[VERVALLEN],A.[BACKUPID],A.[DMPRKA],A.[DMPRKB],A.[DMCODE],A.[DMGRDCODE]  FROM [dbo].[ZINDEX_671] A  WHERE ((A.[VERVALLEN] = 0 OR A.[VERVALLEN] IS NULL) AND ((A.[DMPRKA] = @SV1 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV4) OR  (A.[DMPRKA] = @SV5 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV6 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV7) OR  (A.[DMPRKA] = @SV8 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV9 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV10) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV11) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV12) OR  (A.[DMPRKA] = @SV13 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV14) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV15) OR  (A.[DMPRKA] = @SV16 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV17 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV18 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV19)))  ) A  WHERE AA.[PRKODE] = A.[DMPRKA])  OPTION (KEEP PLAN, KEEPFIXED PLAN, LOOP JOIN)    SELECT AB.[SourceCode],AB.[DOUBLEMEDICATIONSVALIDATED],AB.[BSTNUM],AB.[MUTKOD],AB.[VERVALLEN],AB.[BACKUPID],AB.[LAATSTE],AB.[ExterneCode],AB.[PRKODE],AB.[NMMEMO],AB.[NMETIK],AB.[NMNM40],AB.[NMNAAM],AB.[PRNMNR],AB.[PRKBST],AB.[GPKODE],AB.[DRMLGEN],AB.[Anticoagulant],AB.[HPKSubstancesDiff],AB.[HPKCIsDiff],AB.[HPKUndesiredGroupsDiff]  FROM [dbo].[ZINDEX_050] AB  WHERE EXISTS (SELECT NULL  FROM (SELECT TOP 100 PERCENT  A.[BSTNUM],A.[MUTKOD],A.[VERVALLEN],A.[BACKUPID],A.[DMPRKA],A.[DMPRKB],A.[DMCODE],A.[DMGRDCODE]  FROM [dbo].[ZINDEX_671] A  WHERE ((A.[VERVALLEN] = 0 OR A.[VERVALLEN] IS NULL) AND ((A.[DMPRKA] = @SV1 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV4) OR  (A.[DMPRKA] = @SV5 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV6 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV7) OR  (A.[DMPRKA] = @SV8 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV9 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV10) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV11) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV12) OR  (A.[DMPRKA] = @SV13 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV14) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV15) OR  (A.[DMPRKA] = @SV16 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV17 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV18 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV19)))  ) A  WHERE AB.[PRKODE] = A.[DMPRKB])  OPTION (KEEP PLAN, KEEPFIXED PLAN, LOOP JOIN)    SELECT A.[BSTNUM],A.[MUTKOD],A.[VERVALLEN],A.[BACKUPID],A.[DMPRKA],A.[DMPRKB],A.[DMCODE],A.[DMGRDCODE]  FROM [dbo].[ZINDEX_671] A  WHERE ((A.[VERVALLEN] = 0 OR A.[VERVALLEN] IS NULL) AND ((A.[DMPRKA] = @SV1 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV4) OR  (A.[DMPRKA] = @SV5 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV6 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV7) OR  (A.[DMPRKA] = @SV8 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV9 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV10) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV11) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV12) OR  (A.[DMPRKA] = @SV13 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV14) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV15) OR  (A.[DMPRKA] = @SV16 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV17 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV18 AND A.[DMPRKB] = @SV2) OR  (A.[DMPRKA] = @SV3 AND A.[DMPRKB] = @SV19)))  ORDER BY A.[DMPRKA] ASC,A.[DMPRKB] ASC  OPTION (KEEP PLAN, KEEPFIXED PLAN, LOOP JOIN)

It hurts to look at, i know.

Compiles even with forced parameterization

The compiles/sec matches batches/Sec almost 1 on 1, even when enabling forced parameterization. Which is why the batches/sec line is hidden (it is behind the compiles/sec line).

enter image description here

Perfmon stats:

enter image description here

Queries, CPU , I/O when cpu is around 80% and when it is around 40%

The aggreggates of queries executed in the timeframe 1u05 PM - 1u25 PM today (80% Cpu usage) : enter image description here

Cpu usage: enter image description here

There is a difference with when cpu usage is lower 2u05 PM - 2u25PM today (40% cpu usage) enter image description here

CPU Usage:

enter image description here

The first one is the one where we added an index when we saw issues, and made the cpu usage less.

Extra queries with checks and more info:

   select count(*) as amount_of_USERSTORE_TOKENPERM from sys.dm_os_memory_clerks
   where type = 'USERSTORE_TOKENPERM'

amount_of_USERSTORE_TOKENPERM
15190



     select count(*)  as amount_of_connections from sys.dm_exec_connections 
amount_of_connections
10004


       select  value_in_use from sys.configurations
       where name like '%access check cache bucket count%'

value_in_use
0
          select value_in_use from sys.configurations
       where name like '%access check cache quota%'
value_in_use
0
Improve this question
17
  • 4
    Have you tried enabling forced parameterization on the databases involved? There are drawbacks - for example, it's harder to use filtered indexes - but when it's a good fit, you instantly start getting plan reuse, which means less compilations per second, and CPU drops. (It also seems to reduce the TokenAndPermUserStore cache, too.)
    – Brent Ozar
    Commented Nov 16, 2018 at 13:27
  • 2
    Have you determined what exactly is causing the high CPU usage? You've only said "no noticeable queries" but what is it then? I wonder how much of the TokenAndPermUserStore is correlation vs. causation.
    – Aaron Bertrand
    Commented Nov 16, 2018 at 13:45
  • 4
    @sp_BlitzErik you can't license fewer than the number of cores on a server (or vCores on a VM). See download.microsoft.com/download/7/8/C/…
    – David Browne - Microsoft
    Commented Nov 16, 2018 at 14:39
  • 2
    That server has 12 cores, hyperthreaded to 24. So it requires 12 core licenses. There's no licensing reason to set CPU affinity, also if setting CPU affinity they should run SQL on 0,2,4,... to avoid scheduling two tasks on one physical core.
    – David Browne - Microsoft
    Commented Nov 16, 2018 at 15:02
  • 2
    If you turn off a socket in the BIOS it doesn't show up in Windows and you can't access half the memory.
    – David Browne - Microsoft
    Commented Nov 16, 2018 at 15:17

2 Answers 2

Reset to default
2

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.

Improve this answer
3
  • Did you mean SQL Server 2014 SP2 CU7? We're seeing some weird behavior with the USERSTORE_TOKENPERM cache (168k records, plan cache clearing frequently) and I am hoping this could be a fix.
    – Jacob H
    Commented Dec 19, 2019 at 0:26
  • Sorry to keep commenting but I think you're the only other person on the planet seeing this issue. It appears to be coming from one of our 3rd party apps that uses a "interesting" approach to authentication with impersonation, and also makes a ton of connections for each user. Just as you say, clearing the token cache regularly stabilizes the plan cache temporarily. We're on 2014 SP2, and the vendor hasn't specifically approved SP3 yet. I am hoping 2014 SP2 CU7 has the fix.
    – Jacob H
    Commented Dec 20, 2019 at 13:15
  • @JacobH No problem, our issue was a combination of two. The first one patching the instance, the second one was a BIOS update that happened on the machine that had to be removed. Both these things have been told to me by others and I did not do these changes myself unfortunately as I was on a short term project with the client at the time.
    – Randi Vertongen
    Commented Dec 20, 2019 at 13:21
0

Have you run anything to identify which queries are contributing the most cpu aka worker time? Maybe something like below (time is in microseconds)? In you initial post I saw you identified several queries - how did you identify those were potentially problematic. 

select top 100 SUBSTRING(b.text,statement_start_offset / 2+1 ,   
  ( (CASE WHEN statement_end_offset = -1   
     THEN (LEN(CONVERT(nvarchar(max),b.text)) * 2)   
     ELSE statement_end_offset END)  - statement_start_offset) / 2+1), 
     convert(xml, c.query_plan), a.execution_count, 
a.total_worker_time, a.total_elapsed_time 
from sys.dm_exec_query_stats a 
cross apply sys.dm_exec_sql_text(a.sql_handle) b 
cross apply sys.dm_exec_text_query_plan(a.plan_handle, 
a.statement_start_offset, a.statement_end_offset) c
order by a.total_worker_time desc

Do you see that you have a lot of single use plans (via dmv's) or are you assuming that due to the compilation rates you had displayed in the chart. The query below won't take into account single use plans; however, if this doesn't pan out I can send you one that does.

Improve this answer
2
  • Single use plans are high, not assumed. Checked with a query, it was about 50% of total plans. Still, it does not explain why i have three plans for exactly the same query, the XML matches entirely, but the plans did not get reused. I think that reading from the cache as of now won't be any help, since the CPU Flatlining is only happening at specific moments. As of now it has been a couple of weeks.
    – Randi Vertongen
    Commented Dec 6, 2018 at 21:52
  • Have you looked at sys.dm_exec_plan_attributes? That might show you why you have 3 plans for the same query. If you look at the is_cache_key column that will tell you if the attribute is considered when determining if a cached plan could be leveraged or if a new one needs to be created.
    – Sqlgrease
    Commented Dec 7, 2018 at 15:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.