Martin Karouš's questions

我们在 Windows 2022 VM（8 个 vCPU 核心）上安装了 SQL Server 2022 Enterprise（16.0.4131.2），其中一个数据库上的查询存储存在问题。

下面是我在一个特定数据库上重现该问题所采取的步骤顺序以及我对此所做的观察：

1 –服务器处于空闲状态，仅有少量用户生成负载，且 CPU 利用率可忽略不计。

2 –查询存储已关闭，并且有问题的数据库为空，如下面的查询所示。

3 -当我将查询存储切换到 READ_WRITE 模式时，大约 10 - 15 分钟内一切都很正常。然而，在这段时间之后，服务器的 CPU 利用率突然上升到 15-20% 请参阅下面的活动监视器屏幕截图。

等待统计数据显示SOS_SCHEDULLER_YELD和PREEMPTIVE_OS_QUERYREGISTRY等待量大幅增加- 它们成为该时间段内最热门的服务器等待类型。

产生的 CPU 利用率似乎是由内部后台进程 (SPID < 51) 产生的，该进程与有问题的用户数据库上的 Service Broker 活动有关。数据库本身并未启用 Service Broker 功能。

4 –高 CPU 利用率、上述等待和上述 SPID 活动将持续出现，直到数据库上的查询存储被关闭。

如果我保持查询存储处于打开状态，则没有任何潜在“自我恢复”的迹象。一旦关闭，CPU 利用率就会下降，等待就会消失，SPID 会停止生成负载并进入“休眠”状态。将查询存储切换到 READ_ONLY 模式没有帮助。必须关闭查询存储才能解决问题。

当问题触发时，查询存储中没有任何大量数据 - 见下文（QS 关闭后进行的查询）。

其他相关观察

• 如果我打开（READ_WRITE）查询存储并让它运行一段时间，但时间不足以触发问题，然后将其切换到 READ_ONLY，则不会在上述 10-15 分钟的时间段内触发问题。但是，如果我稍后将查询存储切换到 READ_WRITE，则几乎会立即触发该问题。

• 当问题触发时，用户负载增加，可以看到在标准条件下需要几毫秒才能处理的数据库使用查询开始变慢，并且还显示不可忽略的 PREEMPTIVE_OS_QUERYREGISTRY 等待（根据下面显示的 sp_WhoIsActive）。这些查询肯定不会对 Windows 注册表进行查询。

• 我们在几个其他 SQL Server 实例上拥有相同的数据库（即相同的结构），具有相同的负载模式（甚至更大的数据和查询量），并且查询存储在那里运行良好。似乎查询存储中只有这个特定的数据库出现了问题。

• 重要的是，该数据库的查询存储最初已满，并且触发了“基于大小的清理过程”，并且 SQL Server（可能）在此期间重新启动。我怀疑该数据库中的 QS 中存在与此相关的问题... 我也尝试了sys.sp_query_store_consistency_check过程，但没有成功。

• 一旦通过关闭 QS 触发并“暂停”该问题，那么当再次打开 QS（未事先进行清理）时，该问题似乎几乎立即再次被触发。

在这种情况下，查询存储对于数据库来说完全不可用。有什么办法可以解决这个问题吗？

更新日期 2024-08-01

根据 Paul White 评论的提示，我对 CE_FEEDBACK 数据库范围选项进行了一些实验，它似乎是真正的触发器。行为如下所述：

我以前尝试的未清除的查询存储（仅几百个捕获查询）作为起点。

• 当我设置 CE_FEEDBACK = OFF 并将 QS 转为 READ_WRITE 时，该问题不会触发。
• 当我设置 CE_FEEDBACK = ON 并将 QS 转为 READ_WRITE 时，问题几乎立即触发。
• 当我设置 CE_FEEDBACK = OFF 并将 QS 设置为 READ_WRITE 时，问题不会触发。然后（在运行 QS 的情况下）我将 CE_FEEDBACK = ON。问题再次立即触发。有趣的是，当我再次设置 CE_FEEDBACK = OFF 时，这不足以阻止该问题。必须关闭 QS 才能阻止它。

工作量类型

由于该问题似乎也与工作负载类型有关​​，我将尝试在这里描述它，因为模式非常简单。它几乎 99% 都是临时的，具有以下查询模式：

• 实验期间执行的查询中约有 85% 是与表变量的简单连接。传递给变量的行数从 +/- 10 到 +/- 500 不等。源数据库表在查询之间有所不同，但执行计划模式相同，如下所示。

• 其余工作负载是遵循以下模式的查询。这部分的问题在于，由于参数的高度可变性（参数数量经常变化），因此能够在几天内用“唯一”查询填充 2GB 查询存储，但当前情况并非如此

我可以想象，很可能工作量的第一部分对于 CE_FEEDBACK 来说就是有问题的部分。

有时，msdb.dbo.sysjobhistory中的run_duration列会为某个作业返回负值，参见下面的示例。

查询时作业已成功完成。您知道可能是什么原因吗？

我们正在讨论 SQL 2019 (15.0.4322.2)。到目前为止，这种情况在过去几天内只发生过 2 到 3 次，并且只执行了一个以 5m 频率执行的作业。几个月来，该作业一直没有出现此问题。没有其他作业表现出这种行为，但很难说这是否是一种规则……

这是用户创建的作业。它只会对长期运行的作业的监控造成一点小麻烦（我已经处理了它引起的异常）。我只是想知道是什么导致了这种行为。

我们在具有 8 个 vCPU 的 Windows 2022 VM 上安装了 SQL Server 2022 CU13 Enterprise。即使服务器上没有用户生成的负载（这意味着目前没有用户连接，即使 SQL Server 代理已停止以消除所有非系统负载），sqlserver.exe 进程也显示 CPU 利用率高达 10-15% 之间。在空闲时间，主要的等待类型是 SOS_SCHEDULER_YELD。

此服务器活动还会阻止 SQL Server 服务停止。尝试停止服务会导致无休止的等待，并且需要终止 Windows 服务进程以使其“停止”。重新启动后，利用率在一段时间内完全正常（空闲服务器的 CPU 利用率为 */- 0），但在用户负载运行一段时间后（最多几小时或几天），问题再次出现。这意味着利用率高于生成用户负载时的应有水平，并且再次，在负载停止后（没有用户连接），利用率仍然很高，如上所述...

错误日志、XE 健康会话、Windows 事件日志中没有可疑消息……

以下是上述空闲期间服务器上的活动监视器视图：

经过一番调查，我很可能确定了导致这种利用率的 SPID。它似乎是一个系统 SPID（调查时为 SPID 37），并且在 10 秒内消耗了大约 10 秒的 CPU 时间（参见下面查询 4 ​​的输出）。SPID 的 database_id 似乎在 master（database_id = 1）和其中一个用户数据库（database_id = 10）之间变化。只有数据库 id 1 和 10 似乎参与了此 SPID 活动。SPID 还在 tempdb 中生成不断增加的分配（参见下面查询 3 的输出）。

以下是在拍摄上面的活动监视器图片的同时 sys.dm_exec_request 的输出（有问题的 spid 是 37）：

以下是 sp_WhoIsActive 同时的输出：

以下是 10 秒内 SPID 所消耗的 CPU 时间的计算：

据我了解，这似乎是一项 Service Broker 活动。但是，主数据库和用户数据库中均未启用 Service Broker。我们不会主动在数据库中使用 Service Broker。我不知道此代理活动可能是什么，也不知道它可以与哪些系统活动相关联。服务器上或该用户数据库中均未使用任何“不寻常”的功能，只有“基本通用功能集”，唯一的“不寻常功能”是启用了 TDE。

关于如何进一步调查此问题的根本原因（此经纪人活动的目的何在）以及如何摆脱它，您有什么想法吗？

我们在 Windows Server 2022（具有 32GB RAM 的虚拟机）上运行 MS SQL Server 2022（标准版 16.0.4095.4）的实例，并遇到 sqlservr.exe 进程消耗的“修改内存”问题。

SQL Server内存配置如下：

总体内存消耗如下所示：

当我查看什么消耗了修改后的内存时，我可以看到它是 SQL Server：

内存的“修改”部分不断增长，并且似乎永远不会下降（它已经连续几天保持在较高水平）。SQL Server 性能统计信息目前并未表明存在任何性能问题。

此外，DBCC MEMORYSTATUS 的输出没有给我任何关于这个“修改的内存”部分可能是什么的线索。据我了解输出（见下文），DBCC MEMORYSTATUS 报告的内存消耗符合“最大服务器内存”中设置的限制。

您是否知道什么可能导致内存消耗远高于“最大服务器内存”中设置的值，或者我如何进一步调查？

我们拥有的任何其他实例上都不会出现此问题 - “修改的内存”只是内存消耗的一小部分。此实例与其他实例之间的唯一区别在于，此框中有几个基本可用性组设置。那里不允许任何其他“可疑功能”（没有列存储索引，没有内存中 OLPT ...）。

我只是出于好奇而尝试减少“最大服务器内存”值。唯一的影响是，由此释放的“正在使用的内存”在一段时间内缓慢但肯定地切换到了“修改的内存”。我正在考虑增加虚拟机内存，但我担心这可能会导致更大的“修改内存”消耗......

我们正在为我们的一位客户运行 SQL Server 2019 CU12。前段时间我们开始遇到有线死锁，即单个进程在访问表变量时会自行死锁。

死锁报告示例

    <deadlock>
 <victim-list>
  <victimProcess id="process2ae9f9f7468" />
 </victim-list>
 <process-list>
  <process id="process2ae9f9f7468" taskpriority="0" logused="0" waitresource="OBJECT: 2:-1194094756:0 " waittime="110" ownerId="6978622122" transactionname="GetInitializedIMA" lasttranstarted="2021-09-15T21:46:44.243" XDES="0x2b4d9477be8" lockMode="Sch-S" schedulerid="3" kpid="10868" status="suspended" spid="102" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2021-09-15T21:46:44.113" lastbatchcompleted="2021-09-15T21:46:44.113" lastattention="2021-09-15T21:46:15.777" clientapp=".Net SqlClient Data Provider" hostname="removed" hostpid="15900" loginname="removed" isolationlevel="read committed (2)" xactid="6978622078" currentdb="15" currentdbname="MigrationSubjects" lockTimeout="4294967295" clientoption1="673187936" clientoption2="128056">
   <executionStack>
    <frame procname="unknown" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">unknown</frame>
   </executionStack>
   <inputbuf>
     (@Ids [SubjectRegistry.Consolidation.IdTable] READONLY)
     DELETE [reg].[HistoricalCompanyInfo] FROM [reg].[HistoricalCompanyInfo] t
     INNER JOIN @Ids ids ON ids.Id = t.HistoricalCompanyInfoId
   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <objectlock lockPartition="0" objid="-1194094756" subresource="FULL" dbid="2" objectname="tempdb.dbo.#B8D38F5C" id="lock2ac0942fa00" mode="Sch-M" associatedObjectId="-1194094756">
   <owner-list>
    <owner id="process2ae9f9f7468" mode="Sch-M" />
    <owner id="process2ae9f9f7468" mode="Sch-S" requestType="wait" />
   </owner-list>
   <waiter-list>
    <waiter id="process2ae9f9f7468" mode="Sch-S" requestType="wait" />
   </waiter-list>
  </objectlock>
 </resource-list>
</deadlock>

表类型定义如下：

CREATE TYPE [dbo].[SubjectRegistry.Consolidation.IdTable] AS TABLE(
    [Id] [bigint] NOT NULL, PRIMARY KEY CLUSTERED ([Id] ASC) WITH (IGNORE_DUP_KEY = OFF)
)
GO

知道什么会导致这些奇怪的死锁以及如何绕过它们吗？