Início / dba / Perguntas / 222734
Accepted
Randi Vertongen
Asked: 2018-11-17 05:19:25 +0800 CST

TokenAndPermUserStore Clear diminui o uso da CPU por um curto período de tempo

  • 772

Introdução

Em suma, há muitas consultas ad hoc ocorrendo no meu servidor, de um aplicativo que eu não controlo e não posso alterar (mesmo enviar índices é difícil e eles usam muitos heaps... ).

Especificações

SO - Windows Server 2012 R2 (nó primário) SQL Server 2014 - 12.0.5546

Always On AG Com o nó síncrono secundário com o mesmo hardware + Build.

Memória:

Só podemos usar 12 dos 24 núcleos para sql server devido ao licenciamento (não fiz isso). É muito fácil identificar quais 12 núcleos;). Utilização do CPU

O problema

Agora quanto ao meu problema. No momento, a cada 30 minutos limpamos o "TokenAndPermUserStore". Isso estava acontecendo no servidor antes mesmo de chegar em minhas mãos. Fizemos isso com o comando:

DBCC FREESYSTEMCACHE ('TokenAndPermUserStore')

Eu uso esta consulta para verificar o cache:

SELECT SUM(pages_kb) / 1024  AS 
   "CurrentSizeOfTokenCache(mb)" 
   FROM sys.dm_os_memory_clerks 
   WHERE name = 'TokenAndPermUserStore'

Logo após a limpeza, este é o tamanho do cache:

CurrentSizeOfTokenCache(mb)
1602

Em um determinado momento, por exemplo, 15 minutos após a limpeza, este é o tamanho do cache:

CurrentSizeOfTokenCache(mb)
1976

Atualização: Agora, quando a CPU usada está estável novamente (40% usada (20% no monitoramento), o cache está muito abaixo do ponto mais baixo que estava quando o uso da CPU era alto.

CurrentSizeOfTokenCache(mb)
1281

Um exemplo de ontem:

Os drops estão bem presentes nessa foto de ontem: (Observe que podemos usar 12 dos 24 núcleos, 50% significa 100% no software de monitoramento, ou seja, o uso da CPU provavelmente não ultrapassará 50% pois é dedicado a apenas servidor sql)

insira a descrição da imagem aqui

Uma coisa importante a notar é que adicionamos dois índices importantes nas principais consultas ontem, por causa da CPU quase plana, o que ajudou por um curto período, mas a CPU subiu para o mesmo nível novamente, sem consultas perceptíveis que devem martelar nosso sistema tão difícil.

A questão

Agora, para minha pergunta, hoje, tentei limpar o cache com mais frequência, executando 

DBCC FREESYSTEMCACHE ('TokenAndPermUserStore')

algumas vezes manualmente. Mas parecia que, após cerca de 20 segundos, o uso da CPU voltou com força total.

Você pode ver claramente as três quedas depois de executar o comando, mas voltando bastante rápido na imagem abaixo.

insira a descrição da imagem aqui

Devo agendar mais o comando, devo olhar para outras alterações?

Eu sei que esse problema era predominante no SQL Server 2005, mas este é o SQL Server 2014. As consultas são do tipo sp_executesql.

Se precisar de mais informações ou esclarecimentos, não hesite em me avisar.

Atualização em 12/05/2018

Plano de consulta: 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:

insira a descrição da imagem aqui

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

insira a descrição da imagem aqui

Perfmon stats:

insira a descrição da imagem aqui

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

Os agregados de consultas executadas no período de 13h05 às 13h25 de hoje (80% de uso da CPU): insira a descrição da imagem aqui

Utilização do CPU: insira a descrição da imagem aqui

Há uma diferença quando o uso da CPU é menor 14h05 - 14h25 de hoje (40% de uso da CPU) insira a descrição da imagem aqui

Utilização do CPU:

insira a descrição da imagem aqui

O primeiro é aquele em que adicionamos um índice quando vimos problemas e reduzimos o uso da CPU.

Consultas extras com cheques e mais informações:

   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
sql-server sql-server-2014

2 respostas

  1. Best Answer

    Do que todos vocês pelo seu tempo e esforço em encontrar uma solução. Especialmente @David Browne - Microsoft, pois ele estava correto, informando-me que deveríamos corrigir.

    Tivemos uma reunião com os dba's, os proprietários do aplicativo e a equipe técnica do fornecedor do aplicativo.

    Nesta reunião ficou claro que o mesmo problema está presente para os demais clientes que o fornecedor possui, devido à natureza da aplicação e seu código.

    Semelhante a este.

    A solução que ajudou seus outros clientes com esse problema é atualizar para o SQL Server 2014 CU7 ou SP3, o que faremos o mais rápido possível (SP3 de preferência), o que deve acabar com os problemas de 'tokenandpermuserstore'.

    • 2

  2. Você executou alguma coisa para identificar quais consultas estão contribuindo com mais tempo de trabalho da CPU? Talvez algo como abaixo (o tempo está em microssegundos)? Em sua postagem inicial, vi que você identificou várias consultas - como você identificou que elas eram potencialmente problemáticas. 

    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

    Você vê que tem muitos planos de uso único (via dmv's) ou está assumindo isso devido às taxas de compilação exibidas no gráfico. A consulta abaixo não levará em conta os planos de uso único; no entanto, se isso não der certo, posso enviar um que dê.

    • 0

relate perguntas