Paul White's questions

SQL Server构建采样统计时，采样百分比是如何计算的？

例如，更新Stack Overflow 2013 数据库中dbo.Votes表的PostId列的统计信息：

UPDATE STATISTICS dbo.Votes
(
    _WA_Sys_00000002_0AD2A005
) 
WITH MAXDOP = 1;

SQL Server 构建一个内部语句：

SELECT 
    StatMan
    (
        [SC0], 
        [SB0000]
    ) 
    FROM 
    (
        SELECT TOP 100 PERCENT 
            [SC0], 
            step_direction([SC0]) over (order by NULL) AS [SB0000]  
        FROM 
        (
            SELECT [PostId] AS [SC0] 
            FROM [dbo].[Votes] 
                TABLESAMPLE SYSTEM (9.234204e-01 PERCENT) 
                WITH (READUNCOMMITTED) 
        ) AS _MS_UPDSTATS_TBL_HELPER 
        ORDER BY 
            [SC0], 
            [SB0000] 
    ) AS _MS_UPDSTATS_TBL  
OPTION (MAXDOP 1)

它从哪里来9.234204e-01 PERCENT？

在聚集列存储表上构建统计信息似乎总是读取整个表，即使我要求一个小样本。为什么是这样？

当我在 SQL Server 中使用大对象 (LOB) 数据类型的变量时，整个内容是否始终保存在内存中？即使它是2GB的大小？

在阅读了 Josh Darnell 的Unusual THREADPOOL Waits之后，一位 Twitter 用户提到有一个未记录的跟踪标志来防止修剪空闲的工作人员：

这个想法是，一旦 SQL Server 创建了足够的线程来为峰值工作负载提供服务，它不应该在 15 分钟左右不需要它们之后修剪工作线程（将它们释放到操作系统）。

空闲的工作线程将继续使用资源（例如内存），THREADPOOL但当突然需要更多工作人员时不会出现等待的爆发。显然，这在使用 Always On 可用性组时会有所帮助。

这个未记录的跟踪标志是什么，它是如何工作的？

如果在可用性组中的主服务器上强制执行计划，它是否应用于在辅助服务器上运行的查询？

我正在寻找涵盖计划强制的两种可能性的答案：

• 计划指南
• 查询存储强制计划

我已阅读以下内容，这些内容表明 QS 强制计划不会延续，但在文档中找不到任何权威性内容或有关计划指南的任何内容。

• Erin Stellato 的查询存储和可用性组
• Vikas Rana在 AlwaysOn 可读辅助节点上查询数据存储强制计划行为

强制的确凿证据将是辅助节点的执行计划中存在Use PlanorPlanGuideName和PlanGuideDB属性。

聊天讨论中出现的一个问题：

我知道哈希连接救助会在内部切换到一种嵌套循环的东西。

SQL Server 对散列聚合救助做了什么（如果它可能发生的话）？

在回答SQL 计算不同分区时， Erik Darling 发布了此代码以解决以下问题COUNT(DISTINCT) OVER ()：

SELECT      *
FROM        #MyTable AS mt
CROSS APPLY (   SELECT COUNT(DISTINCT mt2.Col_B) AS dc
                FROM   #MyTable AS mt2
                WHERE  mt2.Col_A = mt.Col_A
                -- GROUP BY mt2.Col_A 
            ) AS ca;

查询使用CROSS APPLY(not OUTER APPLY) 那么为什么执行计划中有外连接而不是内连接？

另外，为什么取消注释 group by 子句会导致内部联接？

我认为数据并不重要，但复制了 kevinwhat 在另一个问题上给出的数据：

create table #MyTable (
Col_A varchar(5),
Col_B int
)

insert into #MyTable values ('A',1)
insert into #MyTable values ('A',1)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',3)

insert into #MyTable values ('B',4)
insert into #MyTable values ('B',4)
insert into #MyTable values ('B',5)

给定以下具有 400 行编号从 1 到 400 的堆表：

DROP TABLE IF EXISTS dbo.N;
GO
SELECT 
    SV.number
INTO dbo.N 
FROM master.dbo.spt_values AS SV
WHERE 
    SV.[type] = N'P'
    AND SV.number BETWEEN 1 AND 400;

以及以下设置：

SET NOCOUNT ON;
SET STATISTICS IO, TIME OFF;
SET STATISTICS XML OFF;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

以下SELECT语句在大约6 秒内完成（demo、plan）：

DECLARE @n integer = 400;

SELECT
    c = COUNT_BIG(*) 
FROM dbo.N AS N
CROSS JOIN dbo.N AS N2
CROSS JOIN dbo.N AS N3
WHERE 
    N.number <= @n
    AND N2.number <= @n
    AND N3.number <= @n
OPTION
    (OPTIMIZE FOR (@n = 1));

_{注意：@OPTIMIZE FOR该条款只是为了生成一个合理大小的重现，以捕获实际问题的基本细节，包括可能由于各种原因而出现的基数错误估计。}

当单行输出写入表时，需要19 秒（演示，计划）：

DECLARE @T table (c bigint NOT NULL);

DECLARE @n integer = 400;

INSERT @T
    (c)
SELECT
    c = COUNT_BIG(*) 
FROM dbo.N AS N
CROSS JOIN dbo.N AS N2
CROSS JOIN dbo.N AS N3
WHERE 
    N.number <= @n
    AND N2.number <= @n
    AND N3.number <= @n
OPTION
    (OPTIMIZE FOR (@n = 1));

除了插入一行之外，执行计划看起来完全相同。

所有额外的时间似乎都被 CPU 使用率消耗掉了。

为什么INSERT声明这么慢？

产生“过度授予”执行计划警告的条件是什么？

查询内存授权检测到“ExcessiveGrant”，这可能会影响可靠性。授权大小：初始 5128 KB，最终 5128 KB，已用 16 KB。

SSMS

计划探索者

展示计划 xml

<Warnings>
    <MemoryGrantWarning GrantWarningKind="Excessive Grant"
        RequestedMemory="5128" GrantedMemory="5128" MaxUsedMemory="16" />
</Warnings>

以下是查询存储遇到的性能问题的简化：

CREATE TABLE #tears
(
    plan_id bigint NOT NULL
);

INSERT #tears (plan_id) 
VALUES (1);

SELECT
    T.plan_id
FROM #tears AS T
LEFT JOIN sys.query_store_plan AS QSP
    ON QSP.plan_id = T.plan_id;

该plan_id列被记录为 的主键sys.query_store_plan，但执行计划没有像预期的那样使用连接消除：

1. DMV 没有投影任何属性。
2. DMV 主键plan_id不能复制临时表中的行
3. 使用了 A ，因此无法删除LEFT JOIN任何行。T

执行计划

这是为什么，如何才能在此处获得连接消除？

以纤程模式（轻量池）运行 SQL Server 会禁用 SQL CLR：

轻量级池不支持公共语言运行时 (CLR) 执行。禁用以下两个选项之一：“clr enabled”或“lightweight pooling”。依赖 CLR 且在纤程模式下无法正常工作的功能包括层次结构数据类型、复制和基于策略的管理。

另一方面，单独禁用 SQL CLR（不启用轻量级池）不会禁用内置 CLR 类型，如geometry, 和geography（尽管hierarchyid上面提到过），如禁用“CLR”时“HierarchyID”类型如何工作中所示?

现在一些新的语言特性依赖于 CLR ，FORMAT例如函数：

FORMAT 依赖于 .NET Framework 公共语言运行时 (CLR) 的存在。

以光纤模式运行 SQL Server 是否会禁用该FORMAT功能和/或使用 CLR 类型？

对于下面的AdventureWorks示例数据库查询：

SELECT 
    P.ProductID, 
    CA.TransactionID
FROM Production.Product AS P
CROSS APPLY
(
    SELECT TOP (1)
        TH.TransactionID
    FROM Production.TransactionHistory AS TH
    WHERE
        TH.ProductID = P.ProductID
    ORDER BY 
        TH.TransactionID DESC
) AS CA;

执行计划显示Index Seek的Estimated Operator Cost为0.0850383 (93%) ：

成本与使用的基数估计模型无关。

它不是Estimated CPU Cost和Estimated I/O Cost的简单相加。它也不是一次执行Index Seek的成本乘以Estimated Number of Executions。

这个成本数字是如何得出的？

以有效并行度 x运行的 SQL Server 查询是否有可能将并行工作程序分配给多个x不同的调度程序？即使执行计划有很多并行区域？

我注意到执行计划有时包含一个StatementParameterizationType属性。

这是什么，它是什么意思，它什么时候出现？

给定下表、唯一聚集索引和统计信息：

CREATE TABLE dbo.Banana
(
    pk integer NOT NULL, 
    c1 char(1) NOT NULL, 
    c2 char(1) NOT NULL
);

CREATE UNIQUE CLUSTERED INDEX pk ON dbo.Banana (pk);

CREATE STATISTICS c1 ON dbo.Banana (c1);
CREATE STATISTICS c2 ON dbo.Banana (c2);

INSERT dbo.Banana 
    (pk, c1, c2) 
VALUES 
    (1, 'A', 'W'), 
    (2, 'B', 'X'), 
    (3, 'C', 'Y'), 
    (4, 'D', 'Z');

-- Populate statistics
UPDATE STATISTICS dbo.Banana;

统计行修改计数器在任何更新之前显然显示为零：

-- Show statistics modification counters
SELECT
    stats_name = S.[name], 
    DDSP.stats_id,
    DDSP.[rows],
    DDSP.modification_counter
FROM sys.stats AS S
CROSS APPLY sys.dm_db_stats_properties(S.object_id, S.stats_id) AS DDSP
WHERE
    S.[object_id] = OBJECT_ID(N'dbo.Banana', N'U');

pk将每行的每列值递增1：

-- Increment pk in every row
UPDATE dbo.Banana 
SET pk += 1;

使用执行计划：

它产生以下统计修改计数器：

问题

1. 拆分、排序和折叠运算符有什么作用？
2. 为什么pk统计数据显示 2 个修改，但c1显示c25 个？

在 SQL Server 中，行存储表上的非唯一非聚集索引在非聚集索引结构的所有级别合并了基础对象的书签（RID 或聚集键）。书签作为非聚集索引键的一部分存储在所有索引级别。

另一方面，如果非聚集索引是唯一的，则书签仅存在于索引的叶级别 - 不作为键的一部分（实际上，书签作为一个或多个包含列存在）。

在 SQL Server 2016 中，可以在面向列的表（具有聚集列存储索引的表）上构建非聚集 b 树索引。

1. 用于聚集列存储表上的非聚集 b 树索引的“书签”是什么？
2. 上面描述的唯一和非唯一非聚集索引之间的差异是否仍然适用？

多语句表值函数在表变量中返回其结果。

这些结果是否曾经被重用，或者每次调用时总是对函数进行全面评估？

众所周知，SCHEMABINDING一个函数可以避免更新计划中不必要的假脱机：

如果您使用不涉及任何表（即不访问数据）的简单 T-SQL UDF，请确保SCHEMABINDING在创建 UDF 期间指定选项。这将使 UDF 绑定模式并确保查询优化器不会为涉及这些 UDF 的查询计划生成任何不必要的假脱机运算符。

SCHEMABINDING即使函数不访问数据，还有其他优点吗？

以下是读取相同数据但报告非常不同的逻辑读取的三个简单测试：

设置

以下脚本创建了一个包含 100 个相同行的测试表，每个行都包含一个xml列，其中包含足够的数据以确保它存储在行外。在我的测试数据库中，生成的xml的长度为每行 20,204 字节。

-- Conditional drop
IF OBJECT_ID(N'dbo.XMLTest', N'U') IS NOT NULL
    DROP TABLE dbo.XMLTest;
GO
-- Create test table
CREATE TABLE dbo.XMLTest
(
    ID integer IDENTITY PRIMARY KEY,
    X xml NULL
);
GO
-- Add 100 wide xml rows
DECLARE @X xml;

SET @X =
(
    SELECT TOP (100) *
    FROM  sys.columns AS C
    FOR XML 
        PATH ('row'),
        ROOT ('root'),
        TYPE
);

INSERT dbo.XMLTest
    (X)
SELECT TOP (100)
    @X
FROM  sys.columns AS C;

-- Flush dirty buffers
CHECKPOINT;

测试

以下三个测试读取xml列：

1. 一个简单的SELECT声明
2. 将xml分配给变量
3. 用于SELECT INTO创建临时表

-- No row count messages or graphical plan
-- Show I/O statistics
SET NOCOUNT ON;
SET STATISTICS XML OFF;
SET STATISTICS IO ON;
GO
PRINT CHAR(10) + '=== Plain SELECT ===='

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

SELECT XT.X 
FROM dbo.XMLTest AS XT;
GO
PRINT CHAR(10) + '=== Assign to a variable ===='

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

DECLARE @X xml;

SELECT
    @X = XT.X
FROM dbo.XMLTest AS XT;
GO
PRINT CHAR(10) + '=== SELECT INTO ===='

IF OBJECT_ID(N'tempdb..#T', N'U') IS NOT NULL
    DROP TABLE #T;

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

SELECT 
    XT.X
INTO #T
FROM dbo.XMLTest AS XT
GO
SET STATISTICS IO OFF;

结果

输出是：

=== 普通选择 ====
表“XMLTest”。扫描计数 1，逻辑读取 3，物理读取 1，预读读取 0，
    lob 逻辑读取 795，lob 物理读取 37，lob 预读读取 796。

=== 赋值给一个变量 ====
表“XMLTest”。扫描计数 1，逻辑读取 3，物理读取 1，预读读取 0，
    lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

=== 选择进入 ====
表“XMLTest”。扫描计数 1，逻辑读取 3，物理读取 1，预读读取 0，
    lob 逻辑读取 300，lob 物理读取 37，lob 预读读取 400。

问题

• 为什么 LOB 读取如此不同？
• 肯定在每次测试中都读取了完全相同的数据吗？

我需要计算一个日期范围内的滚动总和。为了说明，使用AdventureWorks 示例数据库，以下假设语法将完全满足我的需要：

SELECT
    TH.ProductID,
    TH.TransactionDate,
    TH.ActualCost,
    RollingSum45 = SUM(TH.ActualCost) OVER (
        PARTITION BY TH.ProductID
        ORDER BY TH.TransactionDate
        RANGE BETWEEN 
            INTERVAL 45 DAY PRECEDING
            AND CURRENT ROW)
FROM Production.TransactionHistory AS TH
ORDER BY
    TH.ProductID,
    TH.TransactionDate,
    TH.ReferenceOrderID;

遗憾的是，RANGE窗框范围当前不允许 SQL Server 中的间隔。

我知道我可以使用子查询和常规（非窗口）聚合来编写解决方案：

SELECT 
    TH.ProductID,
    TH.TransactionDate,
    TH.ActualCost,
    RollingSum45 =
    (
        SELECT SUM(TH2.ActualCost)
        FROM Production.TransactionHistory AS TH2
        WHERE
            TH2.ProductID = TH.ProductID
            AND TH2.TransactionDate <= TH.TransactionDate
            AND TH2.TransactionDate >= DATEADD(DAY, -45, TH.TransactionDate)
    )
FROM Production.TransactionHistory AS TH
ORDER BY
    TH.ProductID,
    TH.TransactionDate,
    TH.ReferenceOrderID;

给定以下索引：

CREATE UNIQUE INDEX i
ON Production.TransactionHistory
    (ProductID, TransactionDate, ReferenceOrderID)
INCLUDE
    (ActualCost);

执行计划是：

虽然不是非常低效，但似乎应该可以仅使用 SQL Server 2012、2014 或 2016（到目前为止）支持的窗口聚合和分析函数来表达此查询。

为清楚起见，我正在寻找一种对数据执行单次传递的解决方案。

在 T-SQL 中，这很可能意味着子句将OVER完成工作，而执行计划将包含 Window Spools 和 Window Aggregates。所有使用该OVER子句的语言元素都是公平的游戏。SQLCLR 解决方案是可以接受的，只要它保证产生正确的结果。

对于 T-SQL 解决方案，执行计划中的哈希、排序和窗口假脱机/聚合越少越好。随意添加索引，但不允许使用单独的结构（例如，没有预先计算的表与触发器保持同步）。允许参考表（数字表、日期表等）

理想情况下，解决方案将以与上述子查询版本相同的顺序产生完全相同的结果，但任何可以说是正确的也是可以接受的。性能始终是一个考虑因素，因此解决方案至少应该是相当有效的。

专用聊天室：我创建了一个公共聊天室，用于与此问题及其答案相关的讨论。任何拥有至少 20 个声望点的用户都可以直接参与。如果您的代表少于 20 并且想参加，请在下面的评论中联系我。

• 关于“使用窗口函数的日期范围滚动求和”的讨论