主页 / dba / 问题 / 222734
Accepted
Randi Vertongen
Asked: 2018-11-17 05:19:25 +0800 CST

TokenAndPermUserStore Clear 会在短时间内降低 CPU 使用率

  • 772

介绍

简而言之，我的服务器上发生了很多临时查询，这些查询来自我无法控制且无法更改的应用程序（即使推送索引也很困难，而且它们使用了很多堆...）。

眼镜

操作系统 - Windows Server 2012 R2（主节点）SQL Server 2014 - 12.0.5546

Always On AG 与同硬件+Build的次同步节点。

记忆：

由于许可，我们只能将 24 个内核中的 12 个用于 sql server（我没有这样做）。很容易发现是哪 12 个内核 ;)。 CPU使用率

问题

现在关于我的问题。目前，我们每 30 分钟清除一次“TokenAndPermUserStore”。这甚至在它到达我手中之前就已经在服务器上发生了。我们用命令做到了这一点：

DBCC FREESYSTEMCACHE ('TokenAndPermUserStore')

我使用此查询来检查缓存：

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

清除后，这是缓存大小：

CurrentSizeOfTokenCache(mb)
1602

在某个时间点，例如清除后 15 分钟，这是缓存大小：

CurrentSizeOfTokenCache(mb)
1976

更新： 现在，当 CPU 使用再次稳定时（40% 使用（20% 监控），缓存远低于 CPU 使用率高时的最低点。

CurrentSizeOfTokenCache(mb)
1281

昨天的例子：

昨天这张图上掉线非常明显：（注意我们可以使用 24 个内核中的 12 个，50% 在监控软件中表示 100%，换句话说，cpu 使用率可能不会超过 50%，因为它专用于仅限 SQL 服务器）

在此处输入图像描述

需要注意的一件重要事情是，我们昨天在热门查询上添加了两个重要指标，因为 CPU 几乎持平，这在短时间内有所帮助，但 cpu 再次上升到同一水平，没有明显的查询应该受到冲击我们的系统这么难。

问题

现在，对于我的问题，今天，我尝试通过执行更频繁地清除缓存 

DBCC FREESYSTEMCACHE ('TokenAndPermUserStore')

手动几次。但似乎在大约 20 秒后，CPU 使用率又回来了。

你可以清楚地看到执行命令后的三个下降，但在下图中它恢复得相当快。

在此处输入图像描述

我应该安排更多的命令，我应该看看其他的变化吗？

我知道这个问题在 SQL Server 2005 中很普遍，但这是 SQL Server 2014。查询是 sp_executesql 类型的查询。

如果您需要更多信息或说明，请随时告诉我。

更新于 2018 年 5 月 12 日

查询计划： https ://www.brentozar.com/pastetheplan/?id=BkUKKVByV

--> 粘贴计划是为找到的三个计划创建相同的链接。我尝试为同一个查询添加在缓存中找到的所有三个 XML 计划，每个计划执行 10 次，并为每个计划获得相同的链接。

使用的查询

    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)

三个相同查询的结果：

在此处输入图像描述

我确实要注意，查询正在使用 SNAPSHOT ISOLATION ，通过在执行查询之前设置它，并使用提示 OPTION (KEEP PLAN, KEEPFIXED PLAN, LOOP JOIN)

查询 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)

查询 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)

看着很痛，我知道。

即使使用强制参数化也能编译

compiles/sec 与 batches/Sec 几乎 1 对 1 匹配，即使在启用强制参数化时也是如此。这就是隐藏 batches/sec 行的原因（它在 compiles/sec 行后面）。

在此处输入图像描述

性能统计：

在此处输入图像描述

cpu 大约 80% 和大约 40% 时的查询、CPU、I/O

在今天 1u05 PM - 1u25 PM 的时间范围内执行的查询聚合（80% Cpu 使用率）： 在此处输入图像描述

CPU使用率： 在此处输入图像描述

与今天 2u05 PM - 2u25PM 较低的 cpu 使用率有所不同（40% cpu 使用率） 在此处输入图像描述

CPU使用率：

在此处输入图像描述

第一个是我们在发现问题时添加索引并减少 CPU 使用率的那个。

带有支票和更多信息的额外查询：

   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 个回答

  1. Best Answer

    感谢大家花费时间和精力寻找解决方案。特别是@David Browne - 微软，因为他让我知道我们应该打补丁是正确的。

    我们与 dba、应用程序所有者和应用程序供应商的技术团队进行了会谈。

    在这次会议上发现，由于应用程序及其代码的性质，供应商的其他客户也存在同样的问题。

    类似于此。

    帮助他们的其他客户解决此问题的解决方案是升级到 SQL Server 2014 CU7 或 SP3，我们将尽快执行此操作（最好是 SP3），这应该会结束“tokenandpermuserstore”问题。

    • 2

  2. 你有没有运行任何东西来确定哪些查询贡献了最多的 cpu，也就是工作时间？也许像下面这样（时间以微秒为单位）？在您最初的帖子中，我看到您确定了几个查询 - 您是如何确定那些可能有问题的。 

    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

    您是否看到您有很多单次使用计划（通过 dmv's），或者您是否假设由于图表中显示的编译率。下面的查询不会考虑单次使用计划；但是，如果这没有成功，我可以向您发送一个成功的。

    • 0

相关问题