dba

问题：为什么SUM返回的是 4 位小数（如预期），但Avg返回的是 6 位小数？

CREATE TABLE #t(Col DECIMAL(19,4))
INSERT #t VALUES (123456.1200),(654321.3400)
SELECT SUM(Col), AVG(Col) FROM #t

备注：请注意，问题不是我们如何显示AverageAmount4 位小数，而是为什么AverageAmount返回 6 位小数。这是一个错误还是背后有一些合理的原因？我在 Windows 11 上使用最新版本的 SQL Server 2022

设置

-- Create a heap table of numbers from 1 to 100
SELECT TOP (100)
    i = IDENTITY(int, 1, 1)
INTO #T
FROM master.dbo.spt_values;

-- Add a clustered primary key
ALTER TABLE #T
    ADD PRIMARY KEY CLUSTERED (i);

测试查询

按主键顺序显示 9% 的行：

SELECT TOP (9e) PERCENT 
    i 
FROM #T 
ORDER BY 
    i ASC;

DROP TABLE #T;

db<>fiddle 演示

结果

执行计划

问题

当聚集索引提供确切的顺序时，为什么 SQL Server 会对该列进行排序？

更多行

如果我增加表中的行数，我会得到一个 Eager Spool 而不是 Sort，并且按顺序扫描索引：

-- Create a heap table of numbers from 1 to 1,000
SELECT TOP (1000)
    i = IDENTITY(int, 1, 1)
INTO #T
FROM master.dbo.spt_values;

-- Add a clustered primary key
ALTER TABLE #T
    ADD PRIMARY KEY CLUSTERED (i);

-- 0.9% now
SELECT TOP (9e-1) PERCENT 
    i 
FROM #T 
ORDER BY 
    i ASC;

DROP TABLE #T;

以下面的代码为例：

pragma foreign_keys = ON;

create table people(people_id integer primary key, name text not null);
insert into people (name) values ("Mom"), ("Jack the Ripper");

create table family_member(people_id integer primary key references people(people_id));

insert into family_member values ((select people_id from people where name = "Mom"));
insert into family_member values ((select people_id from people where name = "Dad"));  -- silent error here

select name from family_member inner join people using (people_id);
-- Jack the Ripper is part of the family

这在 sqlite 中怎么会是合法的？甚至没有发出警告。这是出于好的理由，还是出于遗留原因，文档中是否有我尚未找到的内容？对我来说，这令人担忧，我相信没有其他 SQL DB 会这样做。

BITSQL Server 使用数据类型而不是有什么意义BOOLEAN吗？这只是一种调用相同事物的奇怪方式，还是存在一些微妙的概念差异？

关于数据类型的文档BIT提及：

位数据类型可用于存储布尔值。

这似乎表明，它们实际上并不是一回事。

我知道[MS 称之为布尔值的东西]和[MS 称之为位的东西]是不同的，但从程序员的角度来看，bit这是 T-SQL 语言提供的柏拉图式概念的最佳近似值boolean。我很难相信这只是偶然事件而不是故意的。仅接受 0 和 1 的数字数据类型似乎不适用于其他用途。

我观察（并重现）了 SQL Server 2022 中的以下场景。

使用中的模式

• 代码通过 sp_executesql 执行（不涉及存储过程）
• 第一个查询将数据选择到临时表中
• 然后，DDL 语句会在临时表上创建聚集索引。临时表绝对不可缓存——首先，这不是一个模块（存储过程或函数），而且我们在临时表填充后创建索引。因此，我不希望缓存的临时对象上留下的统计信息在这里被涉及。
• 查询从临时表中选择数据。此查询每次都会获得完全优化（不是 TRIVIAL 计划）

该批处理可以针对小型和大型数据集运行，因此临时表中可以包含 1 行或数千行。

此行为通常发生在可读辅助数据库上。没有可写查询存储，也没有自动计划强制因素。

我已经验证我也可以针对主副本重现该行为。（自动计划更正被告知忽略查询，并且我确认重现时没有在主副本上强制执行计划。）

复制脚本

• 安装脚本- 我在 SQL Server 2022 CU15 上运行了此脚本。这会关闭查询存储并使用兼容级别 130。
• 重现查询- 我一直通过 SQL Query Stress 运行该查询，因此我可以轻松地在一个或多个线程上同时运行该查询
• 计划生成编号和临时表- 一个非常简单的查询，用于观察系统查询统计中的 plan_generation_num（“重新编译后可用于区分计划实例的序列号。”）和当前的临时表列表

通常会发生什么——以及我期望的行为

通常，在查询执行之间更改临时表中的大量行会自动导致重新编译，并且我会看到从临时表中选择数据的查询具有与临时表中的行匹配的行估计值。

当它按预期工作时，性能良好。

使用重现查询：如果我清除计划缓存，然后在 SQL 查询压力中的单个线程上运行重现查询 40 次迭代，plan_generation_number 最终为 82。当使用 sp_WhoIsActive 对查询计划进行采样时，查询临时表的行估计值与临时表中的行数匹配，正如预期的那样。

有时会发生的情况——在我看来像是一个错误

在极少数情况下，我看到正在使用的计划中，临时表的估计计划只有 1 行，但临时表中实际上有大量行。很多行已更改，但它没有自动重新编译：

这会导致性能非常缓慢，因为低估计计划决定使用没有预取的嵌套循环，这使其成为 CPU 消耗器。

使用重现查询：如果我清除计划缓存，然后在 SQL Query Stress 中的 2 个线程上运行重现查询 20 次迭代，则 plan_generation_number 最终会小于 82——它因运行而异，但可能是 72 或 59，表示重新编译次数较少。在运行时，我还可以使用 sp_WhoIsActive 对估计的行数为单个但临时表中的行数更多的情况进行采样。屏幕截图：

我只能在多个并发会话上运行重现代码时重现此问题

我无法在 SQL Server 中使用单个会话重现此行为。我能重现此行为的唯一方法是设置一个代码块：

• 执行至少 1 次 sp_executesql 语句的迭代，该语句在临时表中有 1 行
• 然后执行 sp_executesql 语句的 1 次迭代，该语句在临时表中包含更多行

如果我在单个会话中运行此程序，则无法重现问题。但如果我在四五个会话中同时运行此程序，则偶尔会出现“未按应有的方式重新编译”的问题。（注意：使用 SQL Query Stress，我只需 2 个会话/迭代即可重现此问题。）

我觉得这像是一个 bug，我很好奇是否有人见过它。不过，使用临时表重新编译和统计行为非常复杂，因此我可能忽略了它如何与不可缓存的临时表一起工作的一些细微差别。

PS：我确实认为可缓存的临时表通常更好。我只是想弄清楚为什么这种行为会在此时在不可缓存的临时表场景中发生。

解决方法

在查询中添加后option (recompile)，我无法再重现查询临时表的 1 行计划的重用。这已经足够了，但我很困惑为什么这是必要的。

我们有一台 SQL Server 2019 CU18，我们发现 querystore 存在一个奇怪的问题。通常，每小时日志备份的平均大小为 40MB，但一旦我们启用 querystore，日志备份的平均大小就会变成 2.5GB。

根据 querystore，每小时执行 140,000 个查询。这大约相当于每秒执行 40 次。

这是我们的查询存储的配置：

ALTER DATABASE [db_name]
SET QUERY_STORE = ON
    (
        OPERATION_MODE = READ_WRITE
        ,CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 45)
        ,DATA_FLUSH_INTERVAL_SECONDS = 900
        ,MAX_STORAGE_SIZE_MB = 2048
        ,INTERVAL_LENGTH_MINUTES = 30
        ,SIZE_BASED_CLEANUP_MODE = AUTO
        ,QUERY_CAPTURE_MODE = AUTO
);

当我打开这么大的 logbackup 文件时，fn_dump_dblog我发现同一秒内发生了多个事务。这些事务都名为“SwapPage”。

分配单元指向plan_persist_runtime_stats。

在Paul White的评论之后，我设置了一个扩展事件来捕获query_store_index_rebuild_started和query_store_index_rebuild_finished。令我惊讶的是，Querystore 正在进行索引重建。以下是此跟踪的结果：

看起来索引重建在 874MB 左右开始，Querystore 的最大大小设置为 2048。

我还将事件的堆栈跟踪包含query_store_index_rebuild_started在扩展事件中。

sqllang!XeSqlPkg::CollectClientHostnameActionInvoke sqllang!XeSqlPkg::CollectDatabaseIdActionInvoke sqllang!XeSqlPkg::CollectDatabaseNameActionInvoke sqllang!XeSqlPkg
::CollectNtUsernameActionInvoke sqllang!XeSqlPkg::CollectSessionIdActionInvoke sqllang!XeSqlPkg::CollectTSqlStack<XE_ActionForwarder> sqllang!XeSqlPkg::CollectTSqlStackActionInvoke qds!XeQdsPkg::query_store_index_rebuild_started::Publish
qds!CDBQDS::ReclaimFreePages
qds!CDBQDS::DoSizeRetention
qds！CDBQDS::ProcessQdsBackgroundTask
qds！CQDSManager::AcquireGenericQdsDbAndProcess<<lambda_e51628d7833f66b5a045fa5bf2d27953>>
qds！CDBQDS::ProcessQdsBackgroundTask
sqldk！SOS_Task::Param::Execute
sqldk！SOS_Scheduler::RunTask
sqldk！SOS_Scheduler::ProcessTasks
sqldk！SchedulerManager::WorkerEntryPoint
sqldk！SystemThreadDispatcher::ProcessWorker
sqldk！SchedulerManager::ThreadEntryPoint
KERNEL32+0x17AC4
ntdll+0x5A8C1

我曾希望找到触发索引重建的原因，但没有那么幸运。

在Zikato的一些指导下，我在跟踪中添加了一些额外的查询存储相关事件。这表明，只有query_store_size_retention_cleanup_started发生事件时才会触发索引重建。

无需重建：

重建： 每次运行清理时都会删除 0KB，但显然需要重建。让我感到困惑的是清理事件的出现。我以为只有当 querystore 达到最大存储大小的 90% 时才会触发此事件。 增加 querystore 的最大大小没有任何区别。

有人遇到过同样的问题吗？有人能解释一下发生了什么吗？实例上的其他数据库没有这个问题。

我需要使用启用排序的STRING_SPLIT ：

我们已将所有服务器升级到 SQL Server 2022 并将兼容级别更改为 160，但以下代码不起作用：

SELECT * FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ', 1);

错误是：

消息 8748，级别 16，状态 1，第 1 行 STRING_SPLIT 的 enable_ordinal 参数仅支持常量值（不支持变量或列）。

生产环境是：

Microsoft SQL Server 2022 (RTM-CU11) (KB5032679) - 16.0.4105.2 (X64) 
    Nov 14 2023 18:33:19 
    Copyright (C) 2022 Microsoft Corporation
    Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: )

SSMS 的版本是：

我们已经在最新的 CU 上测试了代码：

我唯一找到的就是这个答案，它指出：

问题在于，SSMS 的 IntelliSense/工具提示没有基于版本的条件逻辑，并且代码领先于引擎。目前，该功能仅在 Azure SQL 数据库、托管实例和 Synapse 中可用。

不过，我不确定问题出在哪里——文档、引擎、SSMS 还是我做错了什么。

注意：Azure Data Studio 上也存在同样的问题。

（这更多的是一个文档问题，而不是行为问题。它已按照那里的建议从Stack overflow迁移过来。）

在研究另一个需要按原始元素顺序提取 XML 节点的问题的答案时，我遇到了几个答案（此处、此处和此处），它们使用了形式为的表达式ROW_NUMBER() OVER (ORDER BY xml.node)，并断言或暗示分配的行号值将按 XML 文档顺序分配。

但是，我找不到任何定义 行为的地方ORDER BY xml.node。尽管子句中似乎允许这样做OVER()，但文档并未具体提及 XML 节点。

例如，给定：

DECLARE @xml XML = '<root>
<node>One</node>
<node>Two</node>
<node>Three</node>
<node>Four</node>
</root>'

SELECT
    ROW_NUMBER() OVER(ORDER BY xml.node) AS rn,
    xml.node.value('./text()[1]', 'varchar(255)') AS value
FROM @xml.nodes('*/node') xml(node)
ORDER BY
    ROW_NUMBER() OVER(ORDER BY xml.node)

返回结果如下：

rn | value
----------
1  | One
2  | Two
3  | Three
4  | Four

问题：文档中是否有任何地方保证这些结果？这是否被接受为有保证但未记录的行为？或者这是否是另一种情况，ORDER BY (SELECT NULL)对于看似预先排序的小型源数据集似乎有效，但最终在扩大规模时可能会失败？我之所以问这个问题，是因为我宁愿不推荐使用其行为和可靠性不受文档支持的技术。

有趣的是，尽管 XML 节点可以在 windowed 中使用ORDER BY，但在普通的 中却不允许SELECT ... ORDER BY。在普通的 select order-by 子句中使用时，ORDER BY xml.node会产生以下错误：

消息 493 级别 16 状态 1 第 7 行

从 nodes() 方法返回的列“node”不能直接使用。它只能与四种 XML 数据类型方法（exist()、nodes()、query() 和 value()）之一一起使用，或者在 IS NULL 和 IS NOT NULL 检查中使用。

上述错误消息没有列出窗口函数OVER(ORDER BY ...)作为允许的用途。

参见这个 db<>fiddle。

我一直认为CONCAT函数实际上是+（字符串连接）的包装，并带有一些额外的检查，以使我们的生活更轻松。

我还没有找到任何关于这些功能是如何实现的内部细节。至于性能，当数据在循环中连接时，调用似乎会产生开销CONCAT（这似乎很正常，因为有额外的 NULL 句柄）。

几天前，一位开发人员修改了一些字符串连接代码（从+到 ，CONCAT)因为不喜欢语法并告诉我它变得更快。

为了检查情况，我使用了以下代码：

DECLARE @V1 NVARCHAR(MAX)
       ,@V2 NVARCHAR(MAX)
       ,@V3 NVARCHAR(MAX);

DECLARE @R NVARCHAR(MAX);

SELECT  @V1 = REPLICATE(CAST('V1' AS NVARCHAR(MAX)), 50000000)
       ,@V2 = REPLICATE(CAST('V2' AS NVARCHAR(MAX)), 50000000)
       ,@V3 = REPLICATE(CAST('V3' AS NVARCHAR(MAX)), 50000000);

这是变体一：

SELECT @R = CAST('' AS NVARCHAR(MAX)) + '{some small text}' + ISNULL(@V1, '{}') + ISNULL(@V2, '{}') + ISNULL(@V3, '{}'); 
SELECT LEN(@R); -- 1200000017

这是变体二：

SELECT @R = CONCAT('{some small text}',ISNULL(@V1, '{}'), ISNULL(@V2, '{}'), ISNULL(@V3, '{}'))
SELECT LEN(@R); -- 1200000017

对于较小的字符串，没有差异。在某些时候，CONCAT变体会变得更快：

我想知道有人可以分享一些内部结构或解释其行为，因为似乎可能存在一条规则，最好使用CONCAT.

版本：

Microsoft SQL Server 2022 (RTM-CU8) (KB5029666) - 16.0.4075.1 (X64) 2023 年 8 月 23 日 14:04:50 版权所有 (C) 2022 Windows Server 2019 Standard 10.0（内部版本 17763）上的 Microsoft Corporation 标准版（64 位） :)（管理程序）

确切的脚本如下所示：

DECLARE @V1 NVARCHAR(MAX)
       ,@V2 NVARCHAR(MAX)
       ,@V3 NVARCHAR(MAX);

DECLARE @R NVARCHAR(MAX);

SELECT  @V1 = REPLICATE(CAST('V1' AS NVARCHAR(MAX)), 50000000)
       ,@V2 = REPLICATE(CAST('V2' AS NVARCHAR(MAX)), 50000000)
       ,@V3 = REPLICATE(CAST('V3' AS NVARCHAR(MAX)), 50000000);



--SELECT @R = CAST('' AS NVARCHAR(MAX)) + '{some small text}' + ISNULL(@V1, '{}') + ISNULL(@V2, '{}') + ISNULL(@V3, '{}'); -- 00:00:45 -- 00:01:22 -- 00:01:20
--SELECT LEN(@R); -- 300000017

SELECT @R = CONCAT('{some small text}',ISNULL(@V1, '{}'), ISNULL(@V2, '{}'), ISNULL(@V3, '{}')) -- 00:00:11 -- 00:00:16 -- 00:00:10
SELECT LEN(@R); -- 300000017

我正在更改 REPLICATE 函数的最后一个参数，以便为连接生成更大的字符串。然后，我将每个变体执行三次。

谁能解释下面的情况，其中两个看似相等的值没有减少DISTINCT？

上面的查询是SELECT DISTINCT name FROM master.sys.dm_os_spinlock_stats where name = 'SBS_UCS_DISPATCH';

等效方法SELECT name FROM master.sys.dm_os_spinlock_stats where name = 'SBS_UCS_DISPATCH' GROUP BY name;也执行相同的操作，并且添加HAVING COUNT(1) > 1不会产生行。

@@VERSION是Microsoft SQL Server 2019 (RTM-CU13) (KB5005679) - 15.0.4178.1 (X64) 2021 年 9 月 23 日 16:47:49 版权所有 (C) 2019 Microsoft Corporation 企业版：Windows Server 上基于核心的许可（64 位） 2016 标准 10.0（内部版本 14393：）

比方说

您有一个接受日期时间数组的存储过程，这些数组被加载到临时表中，并用于过滤表中的日期时间列。

• 可以插入任意数量的值作为开始日期和结束日期。
• 日期范围有时可能会重叠，但这不是我经常依赖的情况。
• 也可以提供带有时间的日期。

编写查询来执行过滤的最有效方法是什么？

设置

USE StackOverflow2013;

CREATE TABLE
    #d
(
    dfrom datetime,
    dto datetime,
    PRIMARY KEY (dfrom, dto)
)
INSERT
    #d
(
    dfrom,
    dto
)
SELECT
    dfrom = '2013-11-20',
    dto =   '2013-12-05'
UNION ALL
SELECT
    dfrom = '2013-11-27',
    dto =   '2013-12-12'; 

CREATE INDEX
    p
ON dbo.Posts
    (CreationDate)
WITH
    (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

询问

我能得到的最好的就是EXISTS像这样使用：

SELECT
    c = COUNT_BIG(*)
FROM dbo.Posts AS p
WHERE EXISTS
(
    SELECT
        1/0
    FROM #d AS d
    WHERE p.CreationDate BETWEEN d.dfrom
                             AND d.dto
);

这导致了一个看起来相当悲伤的执行计划：

嵌套循环是唯一可用的连接运算符，因为我们没有相等谓词。

我正在寻找的是产生不同类型连接的替代语法。

谢谢！

有什么方法可以从触发器内部判断，当它被触发时，它是由于另一个特定触发器中发生的 DML 操作而被触发的？

有没有机会在函数中公开有关调用堆栈的任何信息EVENTDATA()？还是其他功能？我宁愿不必粉碎 XML。?

我的理想目标是从第二个触发器的范围内获取执行导致第二个触发器触发的 DML 的原始触发器的名称。但我也对类似的识别来源的方式持开放态度。

我可以完全控制所讨论的两个触发器的代码。

我正在研究SQL Server 2019。

我有一张表dbo.AllDates，其中包含从1990到2050的所有日期。我有另一个表dbo.ActualExchangeRates，其中我有在给定来源中找到汇率的日期的某些货币的实际汇率。

我正在尝试编写查询以获取2010和2020之间所有日期的所有货币。如果找到速率，则写入速率，否则写入NULL。

鉴于这种情况和下面给出的代码，有人可以帮我理解为什么SELECT查询没有生成任何结果，甚至看不到估计的执行计划吗？

CREATE TABLE dbo.AllDates(Date date)
CREATE TABLE dbo.ActualExchangeRates(Date date, Currency char(3), Rate real)

--Query 1: Not generating any results or estimated plan
SELECT      d.Date, m.Currency, c.Rate
FROM        dbo.AllDates d
INNER JOIN  (
    select
      currency,
      '20100101' as mindate,
      '20201231' as maxdate
    from dbo.ActualExchangeRates
    group by currency
) as m on d.date between m.mindate and m.maxdate
LEFT JOIN   dbo.ActualExchangeRates C ON C.Currency = m.Currency and c.Date = d.Date;

查询针对空表运行 9 分钟后出现以下错误：

消息 701，级别 17，状态 123，第 5 行
资源池“默认”中的系统内存不足，无法运行此查询。

似乎这取决于 SQL Server 在出现错误之前有多少内存可用。对我来说，鉴于表中没有数据，这看起来像是 SQL 引擎中的错误。

现在，我知道上面的查询可以用许多不同的方式编写，其他方式也可以生成结果，但我的问题是，即使两个表都是空的，为什么 SQL Server 在该查询上永远挂起？

我在 Azure SQL 实例 (12.0.2000.8) 中有一个表，当我查询它时发现了这种奇怪的行为。

该列被定义为整数，但它不响应“WHERE”过滤器，也转换为 varchar 返回奇怪的结果。它只发生在一张表中（到目前为止）。

这里发生了什么？是错误吗？难道我做错了什么？我应该删除表并重新创建它吗？

更多信息：当我查询表时出现问题。如果我使用不同的机器和客户端，我会遇到同样的问题（它也发生在 JDBC 上）。

排序规则是 SQL_Latin1_General_CP1_CI_AS

谢谢！

情况

当使用包含一组已定义列的 SELECT 语句查询数据库时，大约 21 秒内会收到结果。

, *如果在定义的列集列表末尾有一个额外的星号 ( )，则查询将在 2 秒内返回结果。

查询执行计划

执行计划有很大不同。

您可以使用 PasteThePlan 中的链接找到好的实际查询执行计划和坏的实际查询执行计划。

列列表中包含 , * 的语句（末尾）

            SELECT    -- DISTINCT -- 27.04.2020
                'SchuelerKlasse' AS EcoQuery,
                VX_PERSON.PER_MAN_ID, VX_PERSON.PER_ID, VX_PERSON.PER_NAME, VX_PERSON.PER_VORNAME, VX_PERSON.PER_LB_PER_ID, 
                VX_PERSON.PER_GESCHLECHT, VX_PERSON.PER_GEBURTSDATUM, VX_PERSON.PER_TELP, VX_PERSON.PER_MAILP, VX_PERSON.PER_NATP, VX_PERSON.PER_VERSICHERTENNUMMER, VX_PERSON.PER_LAND,
                VX_ADRESSE.ADR_STRASSE, VX_ADRESSE.ADR_PLZ, VX_ADRESSE.ADR_ORT,
                VX_KLASSE.KL_CODE, VX_KLASSE.KL_BEZEICHNUNG,
                VX_KLASSEABSCHNITTSCHUELER.KAS_ANMELDE_STATUS, 
                VX_KLASSEABSCHNITTSCHUELER.KAS_ANMELDETYP, VX_KLASSEABSCHNITTSCHUELER.KAS_ABSCHNITTSNR,
                VX_KLASSE_ZEITRAUM.KLZ_IS_ABSCHLUSSKLASSE, VX_KLASSE_ZEITRAUM.KLZ_ZR_NR,
                VX_ZEITRAUM.ZR_BEGINN, VX_ZEITRAUM.ZR_ENDE
                ,'' AS FA_CODE
                ,'' AS FA_BEZ_STP, '' AS FA_BEZ_STP_LANG
                , '' AS EcoOrig_FA_CODE, '' AS EcoOrig_FA_BEZ_STP, '' AS EcoOrig_FA_BEZ_STP_LANG
                , VX_ANGEBOT.ANG_BEGINN
            
 ,* 

            FROM 
                ECOLST.VX_KLASSE_ZEITRAUM, 
                ECOLST.VX_PERSON, 
                ECOLST.VX_KLASSE, 
                ECOLST.VX_KLASSEABSCHNITTSCHUELER, 
                ECOLST.VX_ZEITRAUM, 
                ECOLST.VX_ADRESSE 
                , ECOSYS.T_KLASSE
                , ECOLST.VX_ANGEBOT

            WHERE  
                    VX_KLASSE_ZEITRAUM.klz_kl_id = VX_KLASSE.kl_id 
                AND VX_KLASSE_ZEITRAUM.klz_zr_id = VX_ZEITRAUM.zr_id 
                AND VX_KLASSEABSCHNITTSCHUELER.kas_ang_id = VX_KLASSE.kl_ang_id 
                AND VX_KLASSEABSCHNITTSCHUELER.kas_zr_id = VX_ZEITRAUM.zr_id 
                AND VX_KLASSEABSCHNITTSCHUELER.kas_per_id = VX_PERSON.per_id 
                AND VX_KLASSEABSCHNITTSCHUELER.kas_kl_id = VX_KLASSE.kl_id 
                AND VX_KLASSEABSCHNITTSCHUELER.KAS_ANMELDE_STATUS LIKE 'De%'  -- LIKE 'Definitiv%'
                AND VX_PERSON.per_id = VX_ADRESSE.adr_per_id 
                AND VX_PERSON.per_man_id = VX_KLASSE.kl_man_id
                AND VX_KLASSE.KL_ANG_ID = VX_ANGEBOT.ANG_ID
                AND VX_KLASSE.KL_MAN_ID = 15 
                AND VX_KLASSE.KL_ID = T_KLASSE.KL_ID
                AND T_KLASSE.KL_STATUS_ID = 491   -- d.h. TS_CODE.CODE_UP_BEZEICHNUNG = 'AKTIV'
            

                AND VX_KLASSE.KL_KLASSENTYP_ID IN (742,743,1235,1926,2075,2076,2078,2079,2080,2081,2086,2103,2118,2119,2122,2152,2252,2308,2416)
        

                AND VX_PERSON.PER_NP = 1   -- Natürliche Person
                AND LEN(LTRIM(RTRIM(VX_PERSON.PER_VORNAME))) > 0        -- TRIM() kann erst ab SQL Server 2017 verwendet werden
                AND LEN(LTRIM(RTRIM(VX_PERSON.PER_NAME))) > 0           -- TRIM() kann erst ab SQL Server 2017 verwendet werden
        
 AND VX_ZEITRAUM.zr_beginn <= CONVERT(DATETIME, '20.05.2023', 104) 
 AND VX_ZEITRAUM.zr_ende   >= CONVERT(DATETIME, '14.02.2023', 104) 
 AND VX_PERSON.per_man_id IN ( 15 ) 

                --AND VX_Person.PER_ID IN  (233777,233779)
        

问题

*一般建议在定义列列表时不要使用，但在我的例子中，, *在末尾添加到列列表，可以显着加快查询速度。（从 21 秒减少到 2 秒）

实际执行计划中没有缺失索引建议。

, *我认为这与在语句中使用时返回的特定列有关，这些列可能包含在查询优化器认为有用的索引中，但我不确定如何查明这些列。

1. 为了说服 SQL Server 运行不包含, *列列表的执行不佳的语句，使用与包含列列表中的附加项的性能语句类似的计划，我必须创建哪些索引？, *

2. 我是否必须分析良好执行计划中使用的所有索引并创建缩减索引（省略某些列）以便查询优化器考虑对语句使用类似的良好, *执行计划而不需要额外的？

根据建议尝试解决方案

1. OPTION (MIN_GRANT_PERCENT = 10, MAX_GRANT_PERCENT = 15)

   应用上述解决方案仅提供了大约 1 小时的临时性能提升。之后查询恢复到错误的执行计划。我不知道为什么...

2. 数据库兼容级别

   ,*使用以下命令将数据库的兼容级别向下更改为 110 (SQL Server 2012)，导致上述查询的性能不断提高，而无需在列列表中添加。

   USE [master]
   GO
   ALTER DATABASE [ECOWEBBSP] SET COMPATIBILITY_LEVEL = 110
   GO
   

   兼容级别为 110 的查询执行计划表明，查询优化器在检索数据时选择了一种完全不同的方法，并且在分配正确的内存量 (110 MB) 方面没有任何问题。

跟进问题

将兼容级别设置为 110 是我唯一的选择吗？

额外反馈

Erik 的回答中提到的Ominous Function是由视图中的fi_kla_is_abschlussklasse列触发的。基础表在检索数据时调用标量值函数。该函数本身返回 0 或 1，具体取决于学生是在毕业班 (1) 还是不在毕业班 (0)。VX_KLASSE_ZEITRAUM.KLZ_IS_ABSCHLUSSKLASSEECOLST.VX_KLASSE_ZEITRAUM

但是，在兼容级别设置为 110 (SQL Server 2012) 的情况下运行时，该函数似乎对查询持续时间的影响不大。有关详细信息，请参阅兼容级别为 110 的查询执行计划。

tl; dr - 如标题所述 - 如果我禁用表上的非聚集索引，是否仍使用该索引的链接统计信息？

我知道有很多关于“删除与禁用索引”的问题......但我找不到专门涵盖统计信息的问题。

我知道统计数据没有改变或改变（至少这是我从 MS 文档中收集到的）。但我的问题是统计数据是否仍在使用。

作为背景，我正在从事一个大型索引调优项目。它涉及在数百个具有不同工作负载模式的相同数据库中添加/删除索引。总共有超过 200 万个索引。

我的第一步是删除所有“未使用”的索引。但是，我没有放弃它们，而是考虑禁用它们以保留定义。这将允许我在表中记录任何禁用的索引的实例、数据库、对象 ID 和索引名称/ID。如果之后性能开始下降，可以重新启用（重建）索引。

但是，如果已禁用索引的统计信息仍用于生成计划...那么禁用它们不会产生与删除它们相同的性能影响。如果是这种情况，那么禁用索引就不是“真正的”性能影响测试，如果禁用的索引最终被删除，我就有引入性能问题的风险。

我的 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 的内存？当我在存储过程之外运行查询时，也会出现此问题。

我在 AWS i3.16xlarge 上运行 SQL Server 2022 RC1 设置，具有 2 个套接字、2 个 NUMA 节点、每个节点 32 个逻辑处理器、总共 64 个逻辑处理器。

安装程序推荐 MAXDOP 8：

但是，如果您单击该链接来配置 MAXDOP，建议会说：

根据那篇知识库文章，MAXDOP 应该是 16，而不是 8。当然，从技术上讲，8 小于 16 - 但 2、4 或 15 也是如此。8 来自哪里？

SQL Server 安装完成并启动服务后，日志显示 SQL Server 正在自动实现具有 4 个节点的 Soft-NUMA，每个节点具有 16 个逻辑处理器：

同样，这表明 MAXDOP 应该是 16。

这是一个错误，还是我错过了一些明显的东西？设置将在 MAXDOP 8 停止的地方是否还有其他不成文的规则？

我有一个表，其中包含一个记录 ID、一个组 ID（将 1 个或多个记录链接到一个组中）和每个记录的哈希值。

CREATE TABLE HashTable(
    RecordID VARCHAR(255),
    GroupIdentifier VARCHAR(255),
    Hash VARCHAR (255),
    GroupHashList VARCHAR(4000)
)

（我知道这不是一个高效的表，但对于本示例而言，它只是一个临时表）。

我想为每个组生成一个哈希，所以我认为最简单的方法是连接组中每个记录的哈希。RecordID 是唯一的，但这些记录相关的内容不一定是唯一的，因此哈希可能是重复的。这样做的目的是标记完全重复的组，即一个组是该组中的所有记录都是另一个组中所有记录的重复。如果要将组的所有成员识别为重复组，则 GUI 需要组的所有成员具有相同的哈希值。

我正在使用 STRING_AGG 连接组中记录的各个散列，并按散列对它们进行排序，以确保我得到重复组的相同字符串。我实际上并不关心哈希的顺序是什么，只要每次都相同。当我将它作为 SELECT 查询运行时，它工作正常，我可以看到重复组的相同字符串。当我采用相同的 SELECT 查询并将其放入 UPDATE 查询时，排序似乎丢失了。

SELECT STRING_AGG([Hash],';') WITHIN GROUP (ORDER BY [Hash] ASC) 
FROM HashTable
GROUP BY [GroupIdentifier]

这给出了结果（例如一对重复组）：

73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697
73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697

当我将相同的代码放入 UPDATE 查询时，它没有正确排序它们：

UPDATE HashTable
SET GroupHashList = c.HashList
FROM HashTable
INNER JOIN (
    SELECT (STRING_AGG([Hash],';') WITHIN GROUP (ORDER BY [Hash] ASC)) AS [HashList],
        [GroupIdentifier]
    FROM HashTable
    GROUP BY [GroupIdentifier]) c
ON c.[GroupIdentifier] = HashTable.[GroupIdentifier]

写入表的相同两组的结果：

73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697
73F294873462B2BA0E930FD16DCCB7;E44256CE7CFCB971EB679BAC25A697;90E749375DF806CB6E3F5CA48FFA38

我错过了什么？

我第一次得到的是

Hash1; Hash2; Hash3
Hash1; Hash2; Hash3

但是当它在 UPDATE 查询中时，我得到

Hash1; Hash2; Hash3
Hash1; Hash3; Hash2

更新查询是按记录 ID 排序的，虽然不知道是不是巧合。（https://dbfiddle.uk/CPG1-z2l）

我再次发现 SQL Server 和 MERGE 语句存在问题，需要一些确认。

我可以在 Azure 数据库上不断重现我的问题（但不能在本地 SQL Server 2017/2019 上）。

请执行以下步骤（一步一步，而不是一个命令执行）！

1) 架构脚本：

    CREATE TABLE [dbo].[ImpactValueHistory]
    (
        [Rn] BIGINT NOT NULL,

        [ImpactId] UNIQUEIDENTIFIER NOT NULL,
        [ImpactValueTypeId] INT NOT NULL,

        [Date] DATE NOT NULL,
        [Value] DECIMAL(38, 10) NOT NULL,

        [ValidFrom] DATETIME2 NOT NULL CONSTRAINT [DF_ImpactValueHistory_ValidFrom] DEFAULT CONVERT(DATETIME2, '0001-01-01'),
        [ValidTo] DATETIME2 NOT NULL CONSTRAINT [DF_ImpactValueHistory_ValidTo] DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),

        [ImpactPeriodId] INT NOT NULL,

        [NormalizedValue] DECIMAL(38, 10) NOT NULL,
    )
    GO

    CREATE CLUSTERED COLUMNSTORE INDEX [COLIX_ImpactValueHistory]
        ON [dbo].[ImpactValueHistory];
    GO

    CREATE NONCLUSTERED INDEX [IX_ImpactValueHistory_ValidFrom_ValidTo_ImpactId_DimensionItemId]
        ON [dbo].[ImpactValueHistory] ([ValidFrom], [ValidTo], [ImpactId], [ImpactValueTypeId], [Date]);
    GO


    CREATE TABLE [dbo].[ImpactValue]
    (
        [Rn] BIGINT NOT NULL IDENTITY(1,1),

        [ImpactId] UNIQUEIDENTIFIER NOT NULL,
        [ImpactValueTypeId] INT NOT NULL,

        [Date] DATE NOT NULL,
        [Value] DECIMAL(38, 10) NOT NULL,

        [ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL CONSTRAINT [DF_ImpactValue_ValidFrom] DEFAULT CONVERT(DATETIME2, '0001-01-01'),
        [ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL CONSTRAINT [DF_ImpactValue_ValidTo] DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),

        [ImpactPeriodId] INT NOT NULL,

        [NormalizedValue] DECIMAL(38, 10) NOT NULL,

        PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo]),

        CONSTRAINT [PK_ImpactValue] PRIMARY KEY NONCLUSTERED ([ImpactId], [ImpactValueTypeId], [Date], [ImpactPeriodId])
    )
    WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[ImpactValueHistory]))
    GO

    CREATE UNIQUE CLUSTERED INDEX [IX_ImpactValue_Id] ON [dbo].[ImpactValue]([Rn])
    GO

    CREATE COLUMNSTORE INDEX [CIX_ImpactValue] ON [dbo].[ImpactValue] ([ImpactId], [ImpactValueTypeId], [Date], [Value], [NormalizedValue])
    GO

2) 插入一些随机数据的脚本

DECLARE @inserted0 TABLE ([Date] DATE, [ImpactId] uniqueidentifier, [ImpactPeriodId] int, [ImpactValueTypeId] int);
MERGE [dbo].[ImpactValue] USING (
SELECT TOP 278 -- <-- this number is critical
        DATEADD(MONTH, ROW_NUMBER() OVER(ORDER BY [Name]) - 1, '2000-01-01') AS [Date],
        NEWID() AS [ImpactId], 
        1 AS [ImpactPeriodId], 
        1 AS [ImpactValueTypeId], 
        99 AS [Value], 
        99 AS [NormalizedValue]
    FROM [sys].[all_columns]
) AS i ([Date], [ImpactId], [ImpactPeriodId], [ImpactValueTypeId], [Value], [NormalizedValue]) ON 1=0
WHEN NOT MATCHED THEN
INSERT ([Date], [ImpactId], [ImpactPeriodId], [ImpactValueTypeId], [Value], [NormalizedValue])
VALUES (i.[Date], i.[ImpactId], i.[ImpactPeriodId], i.[ImpactValueTypeId], i.[Value], i.[NormalizedValue])
OUTPUT INSERTED.[Date], INSERTED.[ImpactId], INSERTED.[ImpactPeriodId], INSERTED.[ImpactValueTypeId]
INTO @inserted0;

SELECT * FROM @inserted0

这一步应该返回所有插入的行！

3）从步骤2中删除数据） 这一步正在填充配置的历史表

DELETE [dbo].[ImpactValue]

4）再次插入一些随机数据 您可以使用步骤2中的脚本）

我必须注意，步骤 1) - 4) 应该单独执行，而不是在GO.

这一步应该再次返回所有插入的行！但事实并非如此！ 在我这边，我总是得到一个空的结果。这可以在我们的三个生产数据库上重现:(

MERGE 语句由 EF Core 生成，目前我正在通过设置 Max Batch Size 来解决此问题。但这不可能是最终的解决方案。

它必须与在时态表上配置了非聚集索引的时态表有关。

也可以看看：

在过去，我已经偶然发现了这个问题：

• https://stackoverflow.com/questions/70734060/why-does-a-merge-into-a-temporal-table-with-a-nonclustered-index-in-the-history。
• https://github.com/dotnet/efcore/issues/22852

但是我当前的问题只能在 Azure SQL 数据库上重现，并且不会引发任何错误。

有趣的旁注：

1. 如果我暂时禁用临时表->它正在工作
2. 如果我删除非聚集索引 [IX_ImpactValueHistory_ValidFrom_ValidTo_ImpactId_DimensionItemId] -> 它正在工作
3. 如果我在步骤 2 中使用 SELECT TOP (@BatchSize) -> 它正在工作
4. 如果我只使用 OUTPUT 而不是 OUTPUT INTO @inserted0 --> 它正在工作

如果没有历史表上的 COLUMNSTORE 索引，它就可以工作。通过仅删除主表上的 COLUMNSTORE 索引，我看到了同样的问题。

TOP 278(a) 重现问题和 (b) 不重现的情况的实际执行计划TOP (@BatchSize)可在https://1drv.ms/u/s!AsOa6e9ukBWQlIRg9_9eySDFp5hvEA?e=KBQBsP获得。我还添加了批量大小为 277 的实际执行计划。两者都使用这个大小！