Joe Obbish's questions

我们有一个可用性组，由 42 个数据库组成，其中一个为同步副本，另一个为异步副本。同步副本能够跟上主副本（根据定义），但异步副本中的大多数数据库在工作日开始时会冻结其进度，直到工作日结束前都不会取得进展。以下图表显示了数据库的 AG 滞后，由last_commit_timesys.dm_hadr_database_replica_states计算得出

对于大多数数据库来说，该last_commit_time时间大约在上午 9:00 左右，直到工作日结束。以下引述来自故障排除：异步提交可用性组副本可能带来的数据丢失

以下部分介绍了异步提交辅助副本可能出现高数据丢失风险的常见原因，假设您的服务器实例中没有与可用性组无关的系统性性能问题。

1. 网络延迟高或网络吞吐量低导致主副本上日志累积

2. 磁盘 I/O 瓶颈减慢了辅助副本上的日志强化

大约一半的数据库使用Microsoft Azure 中的 SQL Server 数据文件功能。这些数据库的 I/O 性能非常差，以至于我有时会在错误日志中看到 15 秒的 I/O 警告。让我感到困惑的是，大多数延迟最严重的数据库都不在 Azure 存储上。我能看到的唯一模式是，具有最佳 I/O 性能的数据库似乎具有最大的延迟。

如果可用性组中有多个数据库，那么一个 I/O 性能较差的数据库是否可能导致另一个 I/O 性能良好的数据库滞后？

如果需要的话，请提供一些有关我的情况的额外技术细节：

• SQL Server 版本是 Microsoft SQL Server 2019 (RTM-CU30)。

• 托管异步副本的数据中心距离主数据中心 1800 英里。两个数据中心都在美国。

• 从异步副本到主副本的 Ping 时间为 40-50 毫秒。我多次被告知，我们没有达到某种整体网络吞吐量限制。netttcp 测试显示带宽容量为 25-30Mbit/秒。

• 根据hadr_database_flow_control_action扩展事件，工作日内未取得进展的数据库实际上 100% 的时间都在等待流量控制。滞后较少的数据库无需花时间等待流量控制。

• 根据hadr_transport_flow_control_action扩展事件，传输层上没有任何等待流量控制的情况。Sends to Transport/sec性能计数器似乎证实了这一点。

• 跟踪标志 12310 无论如何都已启用并且没有明显的效果。

• 对于存储在 Azure 之外的异步数据库，写入延迟在 1-10 毫秒之间。对于存储在 Azure 中的数据库，写入延迟高达 400 毫秒。

• 使用Bytes Sent to Replica/secperfmon 计数器，异步副本能够保持到上午 8:30 左右。然后它就落后了，只有在几个小时后出现一些巨大的进展后才能赶上。

• perfmon计数器Log Bytes Flushed/sec（一天中部分时间的数据丢失）似乎与我们看到的发送字节数相符。最终用户活动在下午 4:30 左右开始下降，此时异步副本在追赶方面取得了更多进展。

• 我于 2025 年 2 月 6 日生成了一份 AG 延迟报告，瓶颈在于主服务器上的发送和流量控制。我不知道如何跟进这些结果：

• 我知道将使用 Microsoft Azure 中的 SQL Server 数据文件功能的数据库放入异步 AG 可能是一种不寻常的设置。我们有效地将数据发送到 1800 英里外只是为了将其发送到云端。微软关于该功能的文档表明这可能没有必要：

高可用性和灾难恢复优势：使用 Microsoft Azure 中的 SQL Server 数据文件功能可能会简化高可用性和灾难恢复解决方案。例如，如果 Microsoft Azure 中的虚拟机或 SQL Server 实例崩溃，您只需重新建立与 blob 的链接即可在新的 SQL Server 实例中重新创建数据库。

我有一个启动和停止扩展事件会话的进程，以便将数据从事件文件传输到 SQL 表中。在 SQL Server 2022 RTM 服务器上，我看到此进程写入日志文件中的许多行：

我希望 XE 会话的启动和停止不写入日志文件，因为我的日志文件现在充满了这些条目。Microsoft SQL Server 2019 (RTM-CU22-GDR) 实例上不会发生此行为。

如何防止 SQL Server 将 XE 会话的启动和停止记录到服务器日志文件中？

我们最近通过该程序设置了一个新的托管实例，您可以免费试用它。当尝试运行Microsoft 文档中的以下代码时：

EXECUTE sp_execute_external_script
  @language =N'Python',
  @script=N'import sys; print("\n".join(sys.path))'

该代码在 300 秒后失败，并出现以下错误：

消息 39012，第 16 级，状态 14，第 0 行

无法与请求 ID：A1D8A9DA-DBB1-4EDE-B589-3AAFD4241D18 的“Python”脚本的运行时进行通信。请检查“Python”运行时的要求。

来自外部脚本的 STDERR 消息：

SQLSatellite Run() 失败。错误代码：0x8007271d。

SqlSatelliteCall 错误：SQLSatellite Run() 失败。错误代码：0x8007271d。

来自外部脚本的 STDOUT 消息：

SqlSatelliteCall 函数失败。请参阅控制台输出以获取更多信息。

回溯（最近一次调用最后一次）：

文件“D:\WFRoot\Ext\Python.9.4.8.3\lib\site-packages\revoscalepy\computecontext\RxInSqlServer.py”，第 605 行，在 rx_sql_satellite_call 中

rx_native_call("SqlSatelliteCall", 参数)

文件“D:\WFRoot\Ext\Python.9.4.8.3\lib\site-packages\revoscalepy\RxSerialized.py”，第 375 行，在 rx_native_call 中

ret = px_call(函数名, 参数)

运行时错误：revoscalepy 函数失败。

我该如何解决这个错误？

我在一个表中有一个 VARCHAR(MAX) 列，它可以以多种不同的格式存储数据，包括 GUID 格式。在另一个表中加入 UNIQUEIDENTIFIER 列时，我尝试了以下代码：

TRY_CAST(b.val AS uniqueidentifier) = a.guid_col

我收到以下错误：

消息 8152，级别 16，状态 10，第 73 行

字符串或二进制数据将被截断。

TRY_CAST的文档确实说在某些情况下会抛出错误：

但是，如果您请求明确不允许的转换，则 TRY_CAST 会失败并出现错误。

但是，SQL Server 允许从 VARCHAR 到 UNIQUEIDENTIFIER 的转换：

我能够找到一个引发错误的示例值。该值很长，所以我在其中包含了一个fiddle。有用的评论者提供了一个更简单的问题重现：

DECLARE @s VARCHAR(MAX) = REPLICATE(CAST('A' AS VARCHAR(MAX)), 8001);

SELECT TRY_CAST(@s AS UNIQUEIDENTIFIER);

为什么在尝试将某些 VARCHAR 值转换为 UNIQUEIDENTIFIER 时 TRY_CAST 抛出错误而不是返回 NULL 值？

我的 SQL Server 版本是 SQL Server 2019 (RTM-CU18)。以下重现代码需要创建一个内存文件组。对于后续的任何人，请记住内存中的文件组一旦创建就不能从数据库中删除。

我有一个简单的内存表，我在其中插入 1 - 1200 之间的整数：

DROP TABLE IF EXISTS [dbo].[InMem];

CREATE TABLE [dbo].[InMem] (
    i [int] NOT NULL,
    CONSTRAINT [PK_InMem]  PRIMARY KEY NONCLUSTERED (i ASC)
) WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY );

INSERT INTO [dbo].[InMem]
SELECT TOP (1200) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

我还有以下本机编译的存储过程：

GO

CREATE OR ALTER PROCEDURE p1
WITH NATIVE_COMPILATION, SCHEMABINDING 
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    SELECT c1.i, c2.i, c3.i
    FROM dbo.[InMem] c1
    CROSS JOIN dbo.[InMem] c2
    CROSS JOIN dbo.[InMem] c3
    WHERE c1.i + c2.i + c3.i = 3600;
END;

GO  

该过程在执行时返回一行。在我的机器上大约需要 32 秒才能完成。在执行时，我无法观察到内存使用方面的任何异常行为。

我可以创建一个类似的表类型：

CREATE TYPE [dbo].[InMemType] AS TABLE(
i [int] NOT NULL,
INDEX [ix_WordBitMap] NONCLUSTERED (i ASC)
) WITH ( MEMORY_OPTIMIZED = ON );

以及相同的存储过程，但改用表类型：

GO

CREATE OR ALTER PROCEDURE p2 (@t dbo.[InMemType] READONLY)
WITH NATIVE_COMPILATION, SCHEMABINDING 
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    SELECT c1.i, c2.i, c3.i
    FROM @t c1
    CROSS JOIN @t c2
    CROSS JOIN @t c3
    WHERE c1.i + c2.i + c3.i = 3600;
END;

GO

新存储过程在大约一分钟后抛出错误：

消息 701，级别 17，状态 154，过程 p2，第 6 行 [批处理起始行 57] 资源池“默认”中的系统内存不足，无法运行此查询。

sys.dm_os_memory_clerks当程序执行时，我可以通过查询dmv看到 MEMORYCLERK_XTP 内存管理员使用的内存量增加到数据库的大约 2800 MB 。根据sys.dm_db_xtp_memory_consumersDMV，几乎所有的内存使用似乎都来自“64K 页面池”消费者：

作为参考，这里是我如何执行新存储过程的。它使用与表相同的 1200 行：

DECLARE @t dbo.[InMemType];

INSERT INTO @t (i)
SELECT i
from [dbo].[InMem];

EXEC p2 @t;

生成的查询计划是一个没有阻塞运算符的简单嵌套循环计划。根据请求，这是第二个存储过程的估计查询计划。

我不明白为什么当我使用表值参数时，这样的查询的内存使用量会增长到超过 2 GB。我已经阅读了各种文档和内存中 OLTP 白皮书，但找不到任何关于此行为的参考。

使用 ETW 跟踪，我可以看到第一个过程将其大部分 cpu 时间用于调用hkengine!HkCursorHeapGetNext，而第二个过程将其大部分 cpu 时间用于调用hkengine!HkCursorRangeGetNext. 我还可以获得这两个程序的 C 源代码。第一个程序在这里，第二个程序有内存问题，在这里。但是，我不知道如何阅读 C 代码，所以我不知道如何进一步调查。

为什么在对表值参数执行嵌套循环时，一个简单的本机编译存储过程会使用超过 2 GB 的内存？当我在存储过程之外运行查询时，也会出现此问题。

SQL Server 2016 添加了AT TIME ZONE运算符。从文档中：

AT TIME ZONE 实现依赖于 Windows 机制来跨时区转换日期时间值。

AT TIME ZONEmscorlib.ni!TimeZoneInfo.ConvertTime根据针对简单查询的 ETW 跟踪调用该方法。Jonathan Kehayias 有一篇博文，他从System.TimeZoneInfo课堂上提取了所有时区规则。我只能在输出中找到 2004 年 1 月 1 日或以后生效的规则：

Rob Farley 在一篇博文中提到，2000 年的时区规则更改似乎没有得到遵守AT TIME ZONE：

它通过使用包含所有信息的 Windows 注册表工作，但遗憾的是，当回顾过去时，它并不完美。澳大利亚在 2008 年更改了日期，美国在 2005 年更改了日期——这两个国家在一年中的大部分时间都在节约日光。AT TIME ZONE 明白这一点。但似乎并没有意识到，在 2000 年的澳大利亚，由于悉尼奥运会，澳大利亚在大约两个月前开始实行夏令时。

我觉得有大量间接证据表明，AT TIME ZONE操作员可能会为早于 2004 年的日期返回不准确的结果。但是，我找不到任何AT TIME ZONE使用System.TimeZoneInfo该类的文档，AT TIME ZONE对于较早的日期可能不准确，或者System.TimeZoneInfo该类对于较早的日期可能不准确。

AT TIME ZONE是否存在导致 2004 年之前返回不准确结果的 SQL Server 产品限制？

我有一个具有该CURSOR VARYING类型的输出参数的存储过程。我想验证调用存储过程的代码是否可以使用输出游标。这似乎CURSOR_STATUS是使用正确的功能，但将它应用到我的输出光标时，我得到了意想不到的结果。该函数在创建它的存储过程中返回值 -3，但在存储过程之外按预期工作。请看下面的代码：

CREATE OR ALTER PROCEDURE dbo.OutputCursorTest  
(@Cursor_OUT CURSOR VARYING OUTPUT)
AS  
BEGIN
    SET NOCOUNT ON;

    SET @Cursor_OUT = CURSOR FORWARD_ONLY STATIC FOR
    SELECT [high]
    from master..spt_values

    OPEN @Cursor_OUT;

    SELECT CURSOR_STATUS('variable', '@Cursor_OUT'); -- this seems to always return -3

    -- possible workaround
    /*
    DECLARE @Cur_Copy CURSOR;
    SET @Cur_Copy =  @Cursor_OUT;
    SELECT CURSOR_STATUS('variable', '@Cur_Copy');
    DEALLOCATE @Cur_Copy;
    */

    RETURN;
END;

GO     

DECLARE @Cur CURSOR;
EXEC dbo.OutputCursorTest @Cursor_OUT = @Cur OUTPUT;
SELECT CURSOR_STATUS('variable', '@Cur'); -- this returns 1 as expected

如果重要的话，我在 SQL Server 2019 CU14 上。为什么CURSOR_STATUS在存储过程中返回值-3（“具有指定名称的游标不存在。”）？

我正在针对 SQL Server 2019 CU14 进行测试。我有一个纯行模式查询，可以从复杂视图中选择前 50 行。完整的查询在 MAXDOP 1 处需要 25426 毫秒的 CPU 时间，在 MAXDOP 2 处需要 19068 毫秒的 CPU 时间。并行查询总体上使用更少的 CPU 时间并不感到惊讶。并行查询适用于位图运算符，并且查询计划在一些方面有所不同。但是，我对前 N 个排序的操作员时间的巨大差异感到惊讶。

在串行计划中，根据算子执行统计，前 N 个排序报告了大约 10 秒的 CPU 时间：

MAXDOP 2 计划为相同的前 N ​​排序报告大约 1.6 秒的 CPU 时间：

我不明白为什么两个不同的查询计划之间会报告如此大的差异。父运算符中的计算标量非常简单，无法解释运算符时间的差异。这是他们的样子：

[Expr1055] = Scalar Operator(CASE WHEN COLUMN_1 IS NULL THEN (0) ELSE datediff(day,COLUMN_1,getdate()) END),
[Expr1074] = Scalar Operator(CASE WHEN [Expr1074] IS NULL THEN (0) ELSE [Expr1074] END)

在计划的不同部分还有其他计算标量。如果有人想查看它们，我上传了串行计划和并行计划的匿名实际计划。

当我将没有 TOP 的完整查询结果加载到临时表中并在临时表上执行 TOP 50 排序时，并行和串行计划都需要大约 1200 毫秒的 CPU 时间来执行排序。因此，在完整查询中报告的并行排序操作员时间对我来说是合理的。串行查询的十秒没有。

为什么串行前 N 排序报告的 CPU 时间比并行排序高得多？它的效率真​​的低很多吗？或者这可能是操作执行统计的错误？

第一次在服务器上运行sp_whoisactive时，我遇到了一些意想不到的事情：

两个会话已经运行了 13 天，但它们似乎都相互阻塞。看看sys.dm_tran_locks：

阻塞进程阈值设置的配置值为 10 秒。正在通过死锁监视器在服务器上成功解决其他死锁。

参数信息@get_locks：

<Database name="DB1">
  <Locks>
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
  </Locks>
  <Objects>
    <Object name="TBL1" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
        <Lock resource_type="PAGE" page_type="*" index_name="PK__TBL1__3214EC27326C5B6A" request_mode="U" request_status="GRANT" request_count="1" />
        <Lock resource_type="PAGE" page_type="*" index_name="PK__TBL1__3214EC27326C5B6A" request_mode="U" request_status="WAIT" request_count="1" />
      </Locks>
    </Object>
  </Objects>
</Database>


<Database name="DB1">
    <Locks>
        <Lock request_mode="S" request_status="GRANT" request_count="1" />
    </Locks>
    <Objects>
        <Object name="TBL2" schema_name="dbo">
            <Locks>
                <Lock resource_type="OBJECT" request_mode="Sch-S" request_status="GRANT" request_count="2" />
            </Locks>
        </Object>
        <Object name="TBL1" schema_name="dbo">
            <Locks>
                <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="5" />
                <Lock resource_type="PAGE" page_type="*" index_name="PK__TBL1__3214EC27326C5B6A" request_mode="U" request_status="GRANT" request_count="33701" />
                <Lock resource_type="PAGE" page_type="*" index_name="PK__TBL1__3214EC27326C5B6A" request_mode="U" request_status="WAIT" request_count="1" />
            </Locks>
        </Object>
    </Objects>
</Database>

我偶尔会看到“未检测到的死锁”一词，但我对它们没有任何直接经验。我的问题是：

1. 这可能是未检测到的死锁的示例吗？如果没有死锁监视器线程的干预，我看不出这种情况是如何解决的，但由于某种原因，这种情况还没有发生。
2. 除了更新到最新的 CU 并希望问题不再发生之外，还有什么可做的吗？服务器目前在 2017 CU10 上，我知道这有点过时了。

我们的部分应用程序代码将数据类型转换为BINARY用于散列的目的。例如，我们将BIGINT值转换为BINARY(8). 该文档警告说，SQL Server 版本之间的转换可能会发生变化：

不保证任何数据类型和二进制数据类型之间的转换在 SQL Server 版本之间是相同的。

作为一种防御，每当我们开始支持新版本的 SQL Server 时，我们都会尝试运行测试来验证我们所有的转换是否仍然有效。对于类似的东西BIGINT，我们检查转换值的长度以获取允许的最小值和最大值BIGINT。这有希望意味着我们会知道，例如，SQL Server 2019 是否开始要求BINARY(9)适应BIGINT.

我们如何进行这种类型的分析FLOAT(53)呢？现在我们认为所有可能的值都映射到适合 a 的唯一值BINARY(8)，但我不知道如何验证这一点。这可能不像只检查数据类型所需的字节数那么简单。例如，TIME数据类型需要 5 个字节用于存储，但必须转换为 aBINARY(6)以避免错误。也许这无关紧要，但也让我紧张BINARY无法转换回FLOAT. 我承认我可能想多了这个问题，所以我欢迎框架挑战作为答案。

如何编写代码来验证所有可能的输入FLOAT(53)不会溢出 aBINARY(8)并且两个不同FLOAT(53)的值不会转换为相同的BINARY(8)值？

的文档说明sys.dm_exec_query_stats以下内容：

如果服务器上当前正在执行工作负载，则 sys.dm_exec_query_stats 的初始查询可能会产生不准确的结果。通过重新运行查询可以确定更准确的结果。

我有时会在活动工作负载期间查询该 DMV，并希望获得准确的结果。我不知道如何在实践中应用上述警告。我是否应该始终查询 DMV 两次并使用第二个结果集，因为这样会更准确？这感觉有点牵强。我是否需要了解 DMV 可能不准确的方式，以便我可以将其纳入我的分析中？如果是这样，可能会出现什么样的不准确：缺少行、过时的值、不一致的行或其他什么？

在活动工作负载期间使用时的最佳做法是什么sys.dm_exec_query_stats？

我有一个通过 C# 代码实现的 CLR 标量 UDF。我注意到，与String数据类型相比，将数据类型用于输入参数可显着提高性能SqlString。在Stairway to SQLCLR Level 5: Development (Using .NET within SQL Server)中，Solomon Rutzky提到以下原因更喜欢字符串的 SQL 数据类型：

本机公共语言运行时 (CLR) 数据类型和 SQL Server 数据类型之间的主要区别是前者不允许 NULL 值，而后者提供完整的 NULL 语义。

...

可以通过 N[VAR]CHAR 的 SqlChars、[VAR]BINARY 的 SqlBytes 和 XML 的 SqlXml.CreateReader() 来实现流值...

...

使用 SqlString（不是字符串，甚至不是 SqlChars）时，您可以访问 CompareInfo、CultureInfo、LCID 和 SqlCompareOptions 属性...

我知道我的输入永远不会为 NULL，我不需要流式传输值，我也永远不会检查排序规则属性。我的情况可能是一个例外，最好使用String而不是SqlString？如果我真的采用这种方法，有什么我应该特别注意的吗？

如果重要的话，我使用的是 SQL Server 的默认排序规则。这是我的源代码的一部分，s1作为输入参数：

fixed (char* chptr = s1)
{
    char* cp = (char*)current;

    for (int i = 0; i < s1.Length; i++)
    {
        cp[i] = chptr[i];
    }
}

我的部分工作负载使用了一个CLR 函数，该函数实现了诡异的哈希算法来比较行以查看是否有任何列值发生了变化。CLR 函数将二进制字符串作为输入，因此我需要一种将行转换为二进制字符串的快速方法。我希望在整个工作负载期间散列大约 100 亿行，所以我希望这段代码尽可能快。

我有大约 300 个不同模式的表。出于这个问题的目的，请假设一个包含 32 个可空INT列的简单表结构。我在这个问题的底部提供了示例数据以及对结果进行基准测试的方法。

如果所有列值都相同，则必须将行转换为相同的二进制字符串。如果任何列值不同，则必须将行转换为不同的二进制字符串。例如，像下面这样简单的代码将不起作用：

CAST(COL1 AS BINARY(4)) + CAST(COL2 AS BINARY(4)) + ..

它不能正确处理零。如果COL1第 1COL2行为 NULL，第 2 行为 NULL，则这两行都将转换为 NULL 字符串。我相信正确处理 NULL 是正确转换整行最难的部分。INT 列的所有允许值都是可能的。

提出一些问题：

• 如果重要的话，大部分时间（90% 以上）列不会为 NULL。
• 我必须使用CLR。
• 我必须放置这么多行。我不能坚持哈希。
• 我相信由于 CLR 功能的存在，我不能使用批处理模式进行转换。

将 32 个可空INT列转换为BINARY(X)字符串VARBINARY(X)的最快方法是什么？

承诺的示例数据和代码：

-- create sample data
DROP TABLE IF EXISTS dbo.TABLE_OF_32_INTS;

CREATE TABLE dbo.TABLE_OF_32_INTS (
    COL1 INT NULL,
    COL2 INT NULL,
    COL3 INT NULL,
    COL4 INT NULL,
    COL5 INT NULL,
    COL6 INT NULL,
    COL7 INT NULL,
    COL8 INT NULL,
    COL9 INT NULL,
    COL10 INT NULL,
    COL11 INT NULL,
    COL12 INT NULL,
    COL13 INT NULL,
    COL14 INT NULL,
    COL15 INT NULL,
    COL16 INT NULL,
    COL17 INT NULL,
    COL18 INT NULL,
    COL19 INT NULL,
    COL20 INT NULL,
    COL21 INT NULL,
    COL22 INT NULL,
    COL23 INT NULL,
    COL24 INT NULL,
    COL25 INT NULL,
    COL26 INT NULL,
    COL27 INT NULL,
    COL28 INT NULL,
    COL29 INT NULL,
    COL30 INT NULL,
    COL31 INT NULL,
    COL32 INT NULL
);

INSERT INTO dbo.TABLE_OF_32_INTS WITH (TABLOCK)
SELECT 0, 123, 12345, 1234567, 123456789
, 0, 123, 12345, 1234567, 123456789
, 0, 123, 12345, 1234567, 123456789
, 0, 123, 12345, 1234567, 123456789
, 0, 123, 12345, 1234567, 123456789
, 0, 123, 12345, 1234567, 123456789
, NULL, -876545321
FROM
(
    SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) q
OPTION (MAXDOP 1);


GO


-- procedure to test performance
CREATE OR ALTER PROCEDURE #p AS 
BEGIN

SET NOCOUNT ON;

DECLARE
@counter INT = 0,
@dummy VARBINARY(8000);

WHILE @counter < 10
BEGIN
    SELECT @dummy = -- this code is clearly incomplete as it does not handle NULLs
        CAST(COL1 AS BINARY(4)) + 
        CAST(COL2 AS BINARY(4)) + 
        CAST(COL3 AS BINARY(4)) + 
        CAST(COL4 AS BINARY(4)) + 
        CAST(COL5 AS BINARY(4)) + 
        CAST(COL6 AS BINARY(4)) + 
        CAST(COL7 AS BINARY(4)) + 
        CAST(COL8 AS BINARY(4)) + 
        CAST(COL9 AS BINARY(4)) + 
        CAST(COL10 AS BINARY(4)) + 
        CAST(COL11 AS BINARY(4)) + 
        CAST(COL12 AS BINARY(4)) + 
        CAST(COL13 AS BINARY(4)) + 
        CAST(COL14 AS BINARY(4)) + 
        CAST(COL15 AS BINARY(4)) + 
        CAST(COL16 AS BINARY(4)) + 
        CAST(COL17 AS BINARY(4)) + 
        CAST(COL18 AS BINARY(4)) + 
        CAST(COL19 AS BINARY(4)) + 
        CAST(COL20 AS BINARY(4)) + 
        CAST(COL21 AS BINARY(4)) + 
        CAST(COL22 AS BINARY(4)) + 
        CAST(COL23 AS BINARY(4)) + 
        CAST(COL24 AS BINARY(4)) + 
        CAST(COL25 AS BINARY(4)) + 
        CAST(COL26 AS BINARY(4)) + 
        CAST(COL27 AS BINARY(4)) + 
        CAST(COL28 AS BINARY(4)) + 
        CAST(COL29 AS BINARY(4)) + 
        CAST(COL30 AS BINARY(4)) + 
        CAST(COL31 AS BINARY(4)) + 
        CAST(COL32 AS BINARY(4))
    FROM dbo.TABLE_OF_32_INTS
    OPTION (MAXDOP 1);

    SET @counter = @counter + 1;
END;

SELECT cpu_time
FROM sys.dm_exec_requests
WHERE session_id = @@SPID;

END;

GO

-- run procedure
EXEC #p;

（我仍然会在这个二进制结果上使用诡异的哈希。工作负载使用哈希连接，哈希值用于其中一个哈希构建。我不希望哈希构建中有一个长二进制值，因为它需要太多记忆。）

以下 T-SQL 在我的机器上使用 SSMS v17.9 大约需要 25 秒：

DECLARE @outer_loop INT = 0,
@big_string_for_u VARCHAR(8000);

SET NOCOUNT ON;

WHILE @outer_loop < 50000000
BEGIN
    SET @big_string_for_u = 'ZZZZZZZZZZ';
    SET @outer_loop = @outer_loop + 1;
END;

ASYNC_NETWORK_IO根据sys.dm_exec_session_wait_stats和，它累积了 532 毫秒的等待时间sys.dm_os_wait_stats。总等待时间随着循环迭代次数的增加而增加。使用wait_completed扩展事件，我可以看到等待大约每 43 毫秒发生一次，但有一些例外：

此外，我可以获取在ASYNC_NETWORK_IO等待之前发生的调用堆栈：

sqldk.dll!SOS_DispatcherBase::GetTrack+0x7f6c
sqldk.dll!SOS_Scheduler::PromotePendingTask+0x204
sqldk.dll!SOS_Task::PostWait+0x5f
sqldk.dll!SOS_Scheduler::Suspend+0xb15
sqllang.dll!CSECCNGProvider::GetBCryptHandleFromAlgID+0xf6af
sqllang.dll!CSECCNGProvider::GetBCryptHandleFromAlgID+0xf44c
sqllang.dll!SNIPacketRelease+0xd63
sqllang.dll!SNIPacketRelease+0x2097
sqllang.dll!SNIPacketRelease+0x1f99
sqllang.dll!SNIPacketRelease+0x18fe
sqllang.dll!CAutoExecuteAsContext::Restore+0x52d
sqllang.dll!CSQLSource::Execute+0x151b
sqllang.dll!CSQLSource::Execute+0xe13
sqllang.dll!CSQLSource::Execute+0x474
sqllang.dll!SNIPacketRelease+0x165d
sqllang.dll!CValOdsRow::CValOdsRow+0xa92
sqllang.dll!CValOdsRow::CValOdsRow+0x883
sqldk.dll!ClockHand::Statistic::RecordClockHandStats+0x15d
sqldk.dll!ClockHand::Statistic::RecordClockHandStats+0x638
sqldk.dll!ClockHand::Statistic::RecordClockHandStats+0x2ad
sqldk.dll!SystemThread::MakeMiniSOSThread+0xdf8
sqldk.dll!SystemThread::MakeMiniSOSThread+0xf00
sqldk.dll!SystemThread::MakeMiniSOSThread+0x667
sqldk.dll!SystemThread::MakeMiniSOSThread+0xbb9

最后，我注意到 SSMS 在循环期间使用了惊人数量的 CPU（平均大约半个核心）。我无法弄清楚 SSMS 在那段时间里在做什么。

为什么一个简单的循环ASYNC_NETWORK_IO在通过 SSMS 执行时会导致等待？我似乎从该查询执行中从客户端获得的唯一输出是“命令已成功完成”。信息。

考虑以下查询，该查询仅在源表中尚未在目标表中时才从源表中插入行：

INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK)
SELECT maybe_new_rows.ID
FROM dbo.A_HEAP_OF_MOSTLY_NEW_ROWS maybe_new_rows
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR halloween
    WHERE maybe_new_rows.ID = halloween.ID
)
OPTION (MAXDOP 1, QUERYTRACEON 7470);

一种可能的计划形状包括合并连接和急切线轴。急切的线轴操作员在场以解决万圣节问题：

在我的机器上，上面的代码在大约 6900 毫秒内执行。用于创建表格的复制代码包含在问题的底部。如果我对性能不满意，我可能会尝试将要插入的行加载到临时表中，而不是依赖于急切的假脱机。这是一种可能的实现：

DROP TABLE IF EXISTS #CONSULTANT_RECOMMENDED_TEMP_TABLE;
CREATE TABLE #CONSULTANT_RECOMMENDED_TEMP_TABLE (
    ID BIGINT,
    PRIMARY KEY (ID)
);

INSERT INTO #CONSULTANT_RECOMMENDED_TEMP_TABLE WITH (TABLOCK)
SELECT maybe_new_rows.ID
FROM dbo.A_HEAP_OF_MOSTLY_NEW_ROWS maybe_new_rows
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR halloween
    WHERE maybe_new_rows.ID = halloween.ID
)
OPTION (MAXDOP 1, QUERYTRACEON 7470);

INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK)
SELECT new_rows.ID
FROM #CONSULTANT_RECOMMENDED_TEMP_TABLE new_rows
OPTION (MAXDOP 1);

新代码在大约 4400 毫秒内执行。我可以获得实际计划并使用 Actual Time Statistics™ 来检查在操作员级别上花费的时间。请注意，要求实际计划会为这些查询增加大量开销，因此总数将与先前的结果不匹配。

╔═════════════╦═════════════╦══════════════╗
║  operator   ║ first query ║ second query ║
╠═════════════╬═════════════╬══════════════╣
║ big scan    ║ 1771        ║ 1744         ║
║ little scan ║ 163         ║ 166          ║
║ sort        ║ 531         ║ 530          ║
║ merge join  ║ 709         ║ 669          ║
║ spool       ║ 3202        ║ N/A          ║
║ temp insert ║ N/A         ║ 422          ║
║ temp scan   ║ N/A         ║ 187          ║
║ insert      ║ 3122        ║ 1545         ║
╚═════════════╩═════════════╩══════════════╝

与使用临时表的计划相比，带有急切假脱机的查询计划似乎在插入和假脱机运算符上花费了更多的时间。

为什么使用临时表的计划更有效率？无论如何，急切的线轴不是主要只是一个内部临时表吗？我相信我正在寻找专注于内部的答案。我能够看到调用堆栈有何不同，但无法弄清楚大局。

如果有人想知道，我在 SQL Server 2017 CU 11 上。这是填充上述查询中使用的表的代码：

DROP TABLE IF EXISTS dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR;

CREATE TABLE dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR (
ID BIGINT NOT NULL,
PRIMARY KEY (ID)
);

INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK)
SELECT TOP (20000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
CROSS JOIN master..spt_values t3
OPTION (MAXDOP 1);


DROP TABLE IF EXISTS dbo.A_HEAP_OF_MOSTLY_NEW_ROWS;

CREATE TABLE dbo.A_HEAP_OF_MOSTLY_NEW_ROWS (
ID BIGINT NOT NULL
);

INSERT INTO dbo.A_HEAP_OF_MOSTLY_NEW_ROWS WITH (TABLOCK)
SELECT TOP (1900000) 19999999 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

作为 ETL 流程的一部分，我们将暂存的行与报告数据库进行比较，以确定自上次加载数据以来是否有任何列实际发生了变化。

比较基于表的唯一键和所有其他列的某种散列。我们目前使用HASHBYTES该SHA2_256算法，发现如果许多并发工作线程都在调用它，则它无法在大型服务器上扩展HASHBYTES。

在 96 核服务器上进行测试时，以每秒哈希值衡量的吞吐量不会增加超过 16 个并发线程。我通过将并发MAXDOP 8查询的数量从 1 更改为 12 来进行测试。测试MAXDOP 1显示了相同的可伸缩性瓶颈。

作为一种解决方法，我想尝试 SQL CLR 解决方案。这是我试图说明要求的尝试：

• 该函数必须能够参与并行查询
• 函数必须是确定性的
• 该函数必须输入一个NVARCHAR或VARBINARY字符串（所有相关列连接在一起）
• 字符串的典型输入大小为 100 - 20000 个字符。20000 不是最大值
• 哈希冲突的机会应该大致等于或优于 MD5 算法。CHECKSUM对我们不起作用，因为有太多的碰撞。
• 该函数必须在大型服务器上很好地扩展（每个线程的吞吐量不应随着线程数量的增加而显着降低）

对于 Application Reasons™，假设我无法保存报表的哈希值。这是一个不支持触发器或计算列的 CCI（还有其他我不想讨论的问题）。

HASHBYTES使用 SQL CLR 函数进行模拟的可扩展方式是什么？我的目标可以表示为在大型服务器上每秒获得尽可能多的哈希值，因此性能也很重要。我对CLR很糟糕，所以我不知道如何做到这一点。如果它激励任何人回答，我计划尽快为这个问题添加赏金。下面是一个示例查询，它非常粗略地说明了用例：

DROP TABLE IF EXISTS #CHANGED_IDS;

SELECT stg.ID INTO #CHANGED_IDS
FROM (
    SELECT ID,
    CAST( HASHBYTES ('SHA2_256', 
        CAST(FK1 AS NVARCHAR(19)) + 
        CAST(FK2 AS NVARCHAR(19)) + 
        CAST(FK3 AS NVARCHAR(19)) + 
        CAST(FK4 AS NVARCHAR(19)) + 
        CAST(FK5 AS NVARCHAR(19)) + 
        CAST(FK6 AS NVARCHAR(19)) + 
        CAST(FK7 AS NVARCHAR(19)) + 
        CAST(FK8 AS NVARCHAR(19)) + 
        CAST(FK9 AS NVARCHAR(19)) + 
        CAST(FK10 AS NVARCHAR(19)) + 
        CAST(FK11 AS NVARCHAR(19)) + 
        CAST(FK12 AS NVARCHAR(19)) + 
        CAST(FK13 AS NVARCHAR(19)) + 
        CAST(FK14 AS NVARCHAR(19)) + 
        CAST(FK15 AS NVARCHAR(19)) + 
        CAST(STR1 AS NVARCHAR(500)) +
        CAST(STR2 AS NVARCHAR(500)) +
        CAST(STR3 AS NVARCHAR(500)) +
        CAST(STR4 AS NVARCHAR(500)) +
        CAST(STR5 AS NVARCHAR(500)) +
        CAST(COMP1 AS NVARCHAR(1)) + 
        CAST(COMP2 AS NVARCHAR(1)) + 
        CAST(COMP3 AS NVARCHAR(1)) + 
        CAST(COMP4 AS NVARCHAR(1)) + 
        CAST(COMP5 AS NVARCHAR(1)))
     AS BINARY(32)) HASH1
    FROM HB_TBL WITH (TABLOCK)
) stg
INNER JOIN (
    SELECT ID,
    CAST(HASHBYTES ('SHA2_256', 
        CAST(FK1 AS NVARCHAR(19)) + 
        CAST(FK2 AS NVARCHAR(19)) + 
        CAST(FK3 AS NVARCHAR(19)) + 
        CAST(FK4 AS NVARCHAR(19)) + 
        CAST(FK5 AS NVARCHAR(19)) + 
        CAST(FK6 AS NVARCHAR(19)) + 
        CAST(FK7 AS NVARCHAR(19)) + 
        CAST(FK8 AS NVARCHAR(19)) + 
        CAST(FK9 AS NVARCHAR(19)) + 
        CAST(FK10 AS NVARCHAR(19)) + 
        CAST(FK11 AS NVARCHAR(19)) + 
        CAST(FK12 AS NVARCHAR(19)) + 
        CAST(FK13 AS NVARCHAR(19)) + 
        CAST(FK14 AS NVARCHAR(19)) + 
        CAST(FK15 AS NVARCHAR(19)) + 
        CAST(STR1 AS NVARCHAR(500)) +
        CAST(STR2 AS NVARCHAR(500)) +
        CAST(STR3 AS NVARCHAR(500)) +
        CAST(STR4 AS NVARCHAR(500)) +
        CAST(STR5 AS NVARCHAR(500)) +
        CAST(COMP1 AS NVARCHAR(1)) + 
        CAST(COMP2 AS NVARCHAR(1)) + 
        CAST(COMP3 AS NVARCHAR(1)) + 
        CAST(COMP4 AS NVARCHAR(1)) + 
        CAST(COMP5 AS NVARCHAR(1)) )
 AS BINARY(32)) HASH1
    FROM HB_TBL_2 WITH (TABLOCK)
) rpt ON rpt.ID = stg.ID
WHERE rpt.HASH1 <> stg.HASH1
OPTION (MAXDOP 8);

为了简化一些事情，我可能会使用以下类似的东西进行基准测试。我将HASHBYTES在星期一发布结果：

CREATE TABLE dbo.HASH_ME (
    ID BIGINT NOT NULL,
    FK1 BIGINT NOT NULL,
    FK2 BIGINT NOT NULL,
    FK3 BIGINT NOT NULL,
    FK4 BIGINT NOT NULL,
    FK5 BIGINT NOT NULL,
    FK6 BIGINT NOT NULL,
    FK7 BIGINT NOT NULL,
    FK8 BIGINT NOT NULL,
    FK9 BIGINT NOT NULL,
    FK10 BIGINT NOT NULL,
    FK11 BIGINT NOT NULL,
    FK12 BIGINT NOT NULL,
    FK13 BIGINT NOT NULL,
    FK14 BIGINT NOT NULL,
    FK15 BIGINT NOT NULL,
    STR1 NVARCHAR(500) NOT NULL,
    STR2 NVARCHAR(500) NOT NULL,
    STR3 NVARCHAR(500) NOT NULL,
    STR4 NVARCHAR(500) NOT NULL,
    STR5 NVARCHAR(2000) NOT NULL,
    COMP1 TINYINT NOT NULL,
    COMP2 TINYINT NOT NULL,
    COMP3 TINYINT NOT NULL,
    COMP4 TINYINT NOT NULL,
    COMP5 TINYINT NOT NULL
);

INSERT INTO dbo.HASH_ME WITH (TABLOCK)
SELECT RN,
RN % 1000000, RN % 1000000, RN % 1000000, RN % 1000000, RN % 1000000,
RN % 1000000, RN % 1000000, RN % 1000000, RN % 1000000, RN % 1000000,
RN % 1000000, RN % 1000000, RN % 1000000, RN % 1000000, RN % 1000000,
REPLICATE(CHAR(65 + RN % 10 ), 30)
,REPLICATE(CHAR(65 + RN % 10 ), 30)
,REPLICATE(CHAR(65 + RN % 10 ), 30)
,REPLICATE(CHAR(65 + RN % 10 ), 30)
,REPLICATE(CHAR(65 + RN % 10 ), 1000),
0,1,0,1,0
FROM (
    SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) q
OPTION (MAXDOP 1);

SELECT MAX(HASHBYTES('SHA2_256',
CAST(N'' AS NVARCHAR(MAX)) + N'|' +
CAST(FK1 AS NVARCHAR(19)) + N'|' +
CAST(FK2 AS NVARCHAR(19)) + N'|' +
CAST(FK3 AS NVARCHAR(19)) + N'|' +
CAST(FK4 AS NVARCHAR(19)) + N'|' +
CAST(FK5 AS NVARCHAR(19)) + N'|' +
CAST(FK6 AS NVARCHAR(19)) + N'|' +
CAST(FK7 AS NVARCHAR(19)) + N'|' +
CAST(FK8 AS NVARCHAR(19)) + N'|' +
CAST(FK9 AS NVARCHAR(19)) + N'|' +
CAST(FK10 AS NVARCHAR(19)) + N'|' +
CAST(FK11 AS NVARCHAR(19)) + N'|' +
CAST(FK12 AS NVARCHAR(19)) + N'|' +
CAST(FK13 AS NVARCHAR(19)) + N'|' +
CAST(FK14 AS NVARCHAR(19)) + N'|' +
CAST(FK15 AS NVARCHAR(19)) + N'|' +
CAST(STR1 AS NVARCHAR(500)) + N'|' +
CAST(STR2 AS NVARCHAR(500)) + N'|' +
CAST(STR3 AS NVARCHAR(500)) + N'|' +
CAST(STR4 AS NVARCHAR(500)) + N'|' +
CAST(STR5 AS NVARCHAR(2000)) + N'|' +
CAST(COMP1 AS NVARCHAR(1)) + N'|' +
CAST(COMP2 AS NVARCHAR(1)) + N'|' +
CAST(COMP3 AS NVARCHAR(1)) + N'|' +
CAST(COMP4 AS NVARCHAR(1)) + N'|' +
CAST(COMP5 AS NVARCHAR(1)) )
)
FROM dbo.HASH_ME
OPTION (MAXDOP 1);

我能够重现一个我认为是意外的查询性能问题。我正在寻找一个专注于内部的答案。

在我的机器上，以下查询执行聚集索引扫描，占用大约 6.8 秒的 CPU 时间：

SELECT ID1, ID2
FROM two_col_key_test WITH (FORCESCAN)
WHERE ID1 NOT IN
(
N'1', N'2',N'3', N'4', N'5',
N'6', N'7', N'8', N'9', N'10',
N'11', N'12',N'13', N'14', N'15',
N'16', N'17', N'18', N'19', N'20'
)
AND (ID1 = N'FILLER TEXT' AND ID2 >= N'' OR (ID1 > N'FILLER TEXT'))
ORDER BY ID1, ID2 OFFSET 12000000 ROWS FETCH FIRST 1 ROW ONLY
OPTION (MAXDOP 1);

以下查询执行聚集索引查找（唯一的区别是删除FORCESCAN提示）但需要大约 18.2 秒的 CPU 时间：

SELECT ID1, ID2
FROM two_col_key_test
WHERE ID1 NOT IN
(
N'1', N'2',N'3', N'4', N'5',
N'6', N'7', N'8', N'9', N'10',
N'11', N'12',N'13', N'14', N'15',
N'16', N'17', N'18', N'19', N'20'
)
AND (ID1 = N'FILLER TEXT' AND ID2 >= N'' OR (ID1 > N'FILLER TEXT'))
ORDER BY ID1, ID2 OFFSET 12000000 ROWS FETCH FIRST 1 ROW ONLY
OPTION (MAXDOP 1);

查询计划非常相似。对于这两个查询，从聚集索引中读取了 120000001 行：

我在 SQL Server 2017 CU 10 上。这是创建和填充two_col_key_test表的代码：

drop table if exists dbo.two_col_key_test;

CREATE TABLE dbo.two_col_key_test (
    ID1 NVARCHAR(50) NOT NULL,
    ID2 NVARCHAR(50) NOT NULL,
    FILLER NVARCHAR(50),
    PRIMARY KEY (ID1, ID2)
);

DROP TABLE IF EXISTS #t;

SELECT TOP (4000) 0 ID INTO #t
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);


INSERT INTO dbo.two_col_key_test WITH (TABLOCK)
SELECT N'FILLER TEXT' + CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) > 8000000 THEN N' 2' ELSE N'' END
, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, NULL
FROM #t t1
CROSS JOIN #t t2;

我希望得到一个不仅仅是调用堆栈报告的答案。例如，我可以看到，sqlmin!TCValSSInRowExprFilter<231,0,0>::GetDataX与快速查询相比，慢速查询需要更多的 CPU 周期：

我不想停在那里，而是想了解那是什么以及为什么两个查询之间存在如此大的差异。

为什么这两个查询的 CPU 时间差异很大？

当我注意到我的一些插入花费的时间比预期的要长时，我正在制作一个涉及 CCI 的演示。要重现的表定义：

DROP TABLE IF EXISTS dbo.STG_1048576;
CREATE TABLE dbo.STG_1048576 (ID BIGINT NOT NULL);
INSERT INTO dbo.STG_1048576
SELECT TOP (1048576) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

DROP TABLE IF EXISTS dbo.CCI_BIGINT;
CREATE TABLE dbo.CCI_BIGINT (ID BIGINT NOT NULL, INDEX CCI CLUSTERED COLUMNSTORE);

对于测试，我从暂存表中插入所有 1048576 行。这足以完全填充一个压缩的行组，只要它由于某种原因没有被修剪。

如果我插入所有 mod 17000 的整数，它需要不到一秒钟的时间：

TRUNCATE TABLE dbo.CCI_BIGINT;

INSERT INTO dbo.CCI_BIGINT WITH (TABLOCK)
SELECT ID % 17000
FROM dbo.STG_1048576
OPTION (MAXDOP 1);

SQL Server 执行时间：CPU 时间 = 359 毫秒，运行时间 = 364 毫秒。

但是，如果我插入相同的整数 mod 16000，它有时会花费 30 多秒：

TRUNCATE TABLE dbo.CCI_BIGINT;

INSERT INTO dbo.CCI_BIGINT WITH (TABLOCK)
SELECT ID % 16000
FROM dbo.STG_1048576
OPTION (MAXDOP 1);

SQL Server 执行时间：CPU 时间 = 32062 毫秒，运行时间 = 32511 毫秒。

这是已在多台机器上完成的可重复测试。随着 mod 值的变化，经过的时间似乎有一个清晰的模式：

MOD_NUM TIME_IN_MS
1000    2036
2000    3857
3000    5463
4000    6930
5000    8414
6000    10270
7000    12350
8000    13936
9000    17470
10000   19946
11000   21373
12000   24950
13000   28677
14000   31030
15000   34040
16000   37000
17000   563
18000   583
19000   576
20000   584

如果您想自己运行测试，请随时修改我在此处编写的测试代码。

我在 sys.dm_os_wait_stats 中找不到 mod 16000 插入的任何有趣内容：

╔════════════════════════════════════╦══════════════╗
║             wait_type              ║ diff_wait_ms ║
╠════════════════════════════════════╬══════════════╣
║ XE_DISPATCHER_WAIT                 ║       164406 ║
║ QDS_PERSIST_TASK_MAIN_LOOP_SLEEP   ║       120002 ║
║ LAZYWRITER_SLEEP                   ║        97718 ║
║ LOGMGR_QUEUE                       ║        97298 ║
║ DIRTY_PAGE_POLL                    ║        97254 ║
║ HADR_FILESTREAM_IOMGR_IOCOMPLETION ║        97111 ║
║ SQLTRACE_INCREMENTAL_FLUSH_SLEEP   ║        96008 ║
║ REQUEST_FOR_DEADLOCK_SEARCH        ║        95001 ║
║ XE_TIMER_EVENT                     ║        94689 ║
║ SLEEP_TASK                         ║        48308 ║
║ BROKER_TO_FLUSH                    ║        48264 ║
║ CHECKPOINT_QUEUE                   ║        35589 ║
║ SOS_SCHEDULER_YIELD                ║           13 ║
╚════════════════════════════════════╩══════════════╝

为什么插入 forID % 16000比插入 for 花费的时间长得多ID % 17000？

我问这个问题是为了更好地了解优化器的行为并了解索引假脱机的限制。假设我将 1 到 10000 的整数放入一个堆中：

CREATE TABLE X_10000 (ID INT NOT NULL);
truncate table X_10000;

INSERT INTO X_10000 WITH (TABLOCK)
SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

并强制嵌套循环加入MAXDOP 1：

SELECT *
FROM X_10000 a
INNER JOIN X_10000 b ON a.ID = b.ID
OPTION (LOOP JOIN, MAXDOP 1);

这是对 SQL Server 采取的相当不友好的操作。当两个表都没有任何相关索引时，嵌套循环连接通常不是一个好的选择。这是计划：

该查询在我的机器上需要 13 秒，从表假脱机中提取了 100000000 行。但是，我不明白为什么查询必须很慢。查询优化器能够通过索引假脱机动态创建索引。这个查询似乎是索引假脱机的完美候选者。

以下查询返回与第一个查询相同的结果，具有索引假脱机，并在不到一秒的时间内完成：

SELECT *
FROM X_10000 a
CROSS APPLY (SELECT TOP (9223372036854775807) b.ID FROM X_10000 b WHERE a.ID = b.ID) ca
OPTION (LOOP JOIN, MAXDOP 1);

此查询也有一个索引假脱机并在不到一秒的时间内完成：

SELECT *
FROM X_10000 a
INNER JOIN X_10000 b ON a.ID >= b.ID AND a.ID <= b.ID
OPTION (LOOP JOIN, MAXDOP 1);

为什么原始查询没有索引假脱机？是否有任何一组记录或未记录的提示或跟踪标志会给它一个索引假脱机？我确实找到了这个相关的问题，但它并没有完全回答我的问题，而且我无法让神秘的跟踪标志适用于这个查询。

这有点偏离了真正的问题。如果提供上下文有帮助，则生成此数据可能有助于测试字符串处理方式的性能，生成需要在游标中对其应用某些操作的字符串，或生成敏感数据的唯一匿名名称替换。我只是对在 SQL Server 中生成数据的有效方法感兴趣，请不要问我为什么需要生成这些数据。

我将尝试从一个有点正式的定义开始。如果一个字符串仅由 A - Z 的大写字母组成，则该字符串包含在该系列中。该系列的第一项是“A”。该系列由所有有效字符串组成，这些字符串首先按长度排序，然后按典型字母顺序排序。如果字符串位于名为 的列中的表中STRING_COL，则可以在 T-SQL 中将顺序定义为ORDER BY LEN(STRING_COL) ASC, STRING_COL ASC。

要给出不太正式的定义，请查看 excel 中按字母顺序排列的列标题。该系列是相同的模式。考虑如何将整数转换为 26 进制数：

1 -> A, 2 -> B, 3 -> C, ... , 25 -> Y, 26 -> Z, 27 -> AA, 28 -> AB, ...

这个类比并不十分完美，因为“A”的行为与以十进制表示的 0 不同。下面是一个选定值的表格，希望能使它更清楚：

╔════════════╦════════╗
║ ROW_NUMBER ║ STRING ║
╠════════════╬════════╣
║          1 ║ A      ║
║          2 ║ B      ║
║         25 ║ Y      ║
║         26 ║ Z      ║
║         27 ║ AA     ║
║         28 ║ AB     ║
║         51 ║ AY     ║
║         52 ║ AZ     ║
║         53 ║ BA     ║
║         54 ║ BB     ║
║      18278 ║ ZZZ    ║
║      18279 ║ AAAA   ║
║     475253 ║ ZZZY   ║
║     475254 ║ ZZZZ   ║
║     475255 ║ AAAAA  ║
║  100000000 ║ HJUNYV ║
╚════════════╩════════╝

目标是编写一个SELECT查询，按上面定义的顺序返回前 100000000 个字符串。我通过在 SSMS 中运行查询并丢弃结果集而不是将其保存到表中来进行测试：

理想情况下，查询将相当有效。在这里，我将高效定义为串行查询的 cpu 时间和并行查询的运行时间。您可以使用您喜欢的任何未记录的技巧。依赖未定义或非保证的行为也可以，但如果您在回答中指出这一点，我们将不胜感激。

有哪些有效生成上述数据集的方法？Martin Smith指出，由于处理这么多行的开销，CLR 存储过程可能不是一个好的方法。