Data Dill's questions

让我在这篇文章的序言中说，我在跟踪中遗漏了一些事件，但我已经添加了它们，以便下次发生这种情况时添加它们。

最近，我们在我们的环境中看到了 HADR_SYNC_COMMIT 等待类型的奇怪激增（~40k tran/s）。今天的“事件”发生在凌晨4点58分：

在继续之前，我必须补充一点，我们正在对一个大型审计表进行在线索引维护（从 OLTP 表触发器大量记录到此审计表的意义上进行审计），并且索引重建本身被阻止约 22 秒。显然，这在这个特定实例中发挥了作用，但我不太确定它与 HADR_SYNC_COMMIT 有何关系。此外，我们在白天不进行索引维护时也看到过这种情况发生。

查看跟踪，这是我在主设备上看到的：

...以及辅助设备上的所有内容：

...最后回到主要：

2023 年 12 月 1 日凌晨 4:11 左右再次发生了类似的问题，我相信我明白发生了什么。不幸的是，我没有针对这种情况的扩展事件，但我确实有一些日志记录可以描绘出更清晰的画面。从 2023-12-01 04:10:18.5430090 开始，Ola 的索引维护记录了相关数据库上索引的开始时间。Ola 报告的完成时间为 2023-12-01 04:11:13.9431563，但我相信实际 REBUILD WITH ONLINE = ON 完成的时间要早​​得多。

在查看 DPA 时，我注意到 pagelatch_sh 和 pagelatch_ex 等待时间在凌晨 4:11:03-4:11:04 出现峰值：

紧接着这些等待，同一个查询开始看到 HADR_SYNC_COMMIT，并且这些相同的等待在凌晨 4:11:13-4:11:14 完全消失，这正是 Ola 报告索引完成的时间。我的假设是索引 REBUILD 是在凌晨 4:11:03 提交的（大约需要 45 秒的工作），这导致同一数据库中的不相关 INSERT 查询只是等待所有这些日志块在辅助数据库上硬化。一旦索引完成，剩余的日志块就会立即硬化，因为它们只是微小的插入。

背景：

为了简单起见，我们有一个 2 节点集群，其中集群中的每个节点都安装了 3 个 SQL 实例：default + 2 named。我知道实例堆叠不好，我们正在积极摆脱它。这些提到的节点中有 2/3 使用 SQL Server AG（默认和 1 个命名实例），而另一个使用 FCI 进行 DR（最后一个实例并不真正涉及问题，但无论如何添加它）。我们所有的侦听器都使用端口 1433，因为 Microsoft 多年前在迁移期间推荐了这种方法，因为添加非默认端口需要在连接时在侦听器名称中包含端口号。事后看来，我不确定为什么不能在创建时简单地将端口包含在 AG 侦听器中，因为它通过 CNAME 混淆了应用程序。我相信我们没有

问题：

在下面的示例中，当 SQL 的默认实例设置在非默认端口（例如 1400）上时，所有应用程序都按预期连接。但是，当使用默认端口时，所有 CNAMES 都意外地路由到 1433（猜测这是因为侦听器本身在端口 1433 上）。我要确定的是，当用户尝试连接到 1433，但它不存在时，浏览器是否识别出不存在端口 1433 的实例，因此它会执行某种辅助检查/解析逻辑来关联名称侦听器本身是否具有正确的 SQL 实例？

找到了另一个解释这种情况的微软文档，但它未能解释 SQL 如何能够将侦听器 VNN 与实例本身相关联。在我看来，WSFC 资源将该信息与客户端相关联，以确定哪个侦听器路由到哪个实例。但是，如果 1433 上有一个实例，SQL 将不正确地将所有内容路由到该实例，假设所有侦听器出于某种原因都设置在 1433 上。

“侦听器端口配置可用性组侦听器时，必须通过 SSMS 指定一个端口。您可以将默认端口配置为 1433，以简化客户端连接字符串。这意味着，如果您使用 1433，则不会不需要在应用程序的连接字符串中包含端口号。此外，由于每个可用性组侦听器都有一个单独的虚拟网络名称，因此在单个 WSFC 上配置的每个可用性组侦听器都可以配置为引用相同的默认端口 1433 .

如果您将默认端口 1433 用于可用性组侦听器 VNN，您仍然需要确保集群节点上没有其他服务正在使用此端口；否则会导致端口冲突。

如果 SQL Server 的一个实例已经通过实例侦听器侦听 TCP 端口 1433，并且侦听端口 1433 的计算机上没有其他服务（包括其他 SQL Server 实例），这不会导致端口冲突可用性组侦听器。这是因为可用性组侦听器可以在同一进程内共享同一 TCP 端口。但是，不能将多个 SQL Server 实例（并排）配置为侦听同一端口，因为其中一个实例将无法侦听连接。

您还可以指定一个非标准可用性组侦听器端口。但是，在连接到侦听器时，您还需要在应用程序连接字符串中明确使用目标端口。您还需要为此端口在防火墙上打开权限。

您可以使用名称和端口（如果不是 1433）连接到侦听器。该端口可以是侦听器端口，也可以是配置为侦听的基础 SQL Server 端口。”- https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability -group-listener-overview?view=sql-server-ver16

我正在尝试确定这是报告错误的执行计划还是按预期工作的实际功能。

在我的执行计划中，当使用 MAXDOP 1 时，我看到整个表正在被扫描（https://www.brentozar.com/pastetheplan/?id=HkPFquLdc - 计划对象 11 的最底部显示读取了大约 250 万行整个表）。

但是，当我让引擎在没有提示的情况下选择它自己的计划时，它会并行（https://www.brentozar.com/pastetheplan/?id=r12p5OUdc）并进行位图/哈希匹配，并且相同的 Object11 只显示〜尽管进行了索引扫描并且在不在聚集索引中的其他列上有一堆谓词，但仍读取了 534k 行。

我希望 SQL 必须读取表中的每一行来评估每个谓词，但也许并行计划中对象 11 上的 PROBE IN（您在粘贴计划中看不到这一点）能够“过滤掉页面" 因为探头在 PK/CX 上。

我创建了一个扩展事件会话，它监视 module_start 事件类型并根据 object_name 进行过滤：equal_i_sql_unicode_string]([object_name])

本次会议的目的是在调用过滤器列表中的 proc 时简单地记录基本信息，以便我可以以 99.9% 的保证在生产中是否仍然调用 proc 来回答开发人员的问题。这个想法是 24/7 运行约 1 个月（是的，它不考虑每年运行的东西，但它就是这样）。

我遇到的问题是开发人员给我的 procs 列表大约有 90 个左右，EE 会话的过滤器列表限制为 3,000 个字符。为了提高我们可以跟踪 procs 的速率，我想出的唯一想法是拥有 2 个独立的 EE 会话，它们是相同的，只是过滤器谓词不同。

我不是在问“这会对 CPU 产生多大的影响”，而是他们或多或少地担心使用不同的过滤谓词运行 2 个相同的 EE 会话？对我来说奇怪的是，当“更多过滤 == 更好的性能”时，Microsoft 会将过滤器列表限制为 3,000 个字符，因为 EE 内置到引擎中的方式非常优化，不像跟踪更像代理而不是“触发器”基于一个事件”。

是否可以安全地假设无论运行 1 个会话对性能的影响是什么，我都可以将其乘以 2，还是我没有考虑他们进一步的担忧？

我想我可能会根据我的研究知道答案，但我正在寻找关于引擎如何/为什么按照它的方式编译计划的确认

传入参数：@ID int ,@OtherID INT

SELECT  b.Column1
        ,b.Column2
        ,b.Column3
        ,b.Column4
        ,b.Column5
        ,c.Column1
        ,b.Column1
        ,e.Column1
FROM    Table1 AS b 
        inner join Table2 AS t
        on b.ID = t.ID
        left join [LINKED SERVER].[DB].dbo.Table3 as c
        on b.ID = c.ID
        left join Table4 AS e
        on b.ID= e.ID
where   (b.ID = @ID or @ID= 0)
        And b.ID = @OtherID
        And b.ID IS NOT NULL
        and e.ID = 1

现在我已经确定索引扫描的原因是因为这行：where (b.ID = @ID or @ID= 0). 更具体地说，@ID = 0。为了进一步澄清，该 ID 字段的 0 不作为基础表中的值存在，这只是开发人员所做的事情，以允许用户通过传入来拉回所有结果0 到参数，然后检查该参数是否为 0，因此结果会拉回更多行（通常，您只会返回 1-3 个结果）。

现在，非常奇怪的是，如果我添加OPTION RECOMPILE，引擎当然能够以开销（编译时间）为代价创建一个更好的计划：

我想知道这怎么可能。根据我在网上阅读的内容，通过 using OPTION RECOMPILE，引擎将用传递给参数的实际值替换该值，并且可以很容易地看到 @ID 1234 不等于 0。但是，如果您不使用OPTION RECOMPILE引擎将获取记录的总数，即 120,000，然后将其除以不同可能性的总数，即 107,000。这会返回大约 1.1 个估计行，我通过查看具有索引扫描的计划的估计属性来确认这一点，但是如果估计正确，为什么引擎会继续索引扫描呢？为了确定，我什至更新了统计数据。

最近我在我们的生产环境中部署了一个更改，该更改更改了现有的存储过程，基本上所有的添加都是插入到一个表中，我创建了调用该存储过程的用户传入的参数。

示例：来自财务的用户在他的前端应用程序中传递了 15/20 个可能的参数。这些确切的参数首先插入到我的表中，其中每一列都是一个参数。如果用户没有为给定参数输入任何内容（因为它们是可选的），则单元格将为空（非 NULL）。

所以再次，我需要汇总并找出实际使用的最常见的参数组合是什么（我不关心实际值，只关心参数是否被使用）。

为了澄清我的表..每一行都是一个存储的过程调用，每一列都是一个参数。

我的桌子：