主页 / user-1192

Paul White's questions

Martin Hope
Paul White
Asked: 2025-02-18 16:34:13 +0800 CST
  • 21

设置

-- 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 演示

结果

SSMS 结果

执行计划

SSMS 执行计划

问题

当聚集索引提供确切的顺序时，为什么 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;

计划包含 1,000 行

sql-server
Martin Hope
Paul White
Asked: 2024-05-22 23:24:21 +0800 CST
  • 11

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
Martin Hope
Paul White
Asked: 2020-08-30 06:55:14 +0800 CST
  • 12

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

鸣叫

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

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

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

sql-server database-internals
Martin Hope
Paul White
Asked: 2019-08-30 14:54:29 +0800 CST
  • 17

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

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

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

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

sql-server availability-groups
Martin Hope
Paul White
Asked: 2019-06-06 06:08:22 +0800 CST
  • 18

在回答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)
sql-server execution-plan
Martin Hope
Paul White
Asked: 2019-02-26 02:22:55 +0800 CST
  • 18

给定以下具有 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声明这么慢？

sql-server performance
Martin Hope
Paul White
Asked: 2019-01-29 04:15:34 +0800 CST
  • 22

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

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

SSMS

SSMS计划

SSMS 属性

计划探索者

计划探索者

展示计划 xml

<Warnings>
    <MemoryGrantWarning GrantWarningKind="Excessive Grant"
        RequestedMemory="5128" GrantedMemory="5128" MaxUsedMemory="16" />
</Warnings>
sql-server execution-plan
Martin Hope
Paul White
Asked: 2018-09-02 06:22:32 +0800 CST
  • 10

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

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 performance
Martin Hope
Paul White
Asked: 2018-07-14 09:02:49 +0800 CST
  • 7

以纤程模式（轻量池）运行 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 类型？

sql-server t-sql
Martin Hope
Paul White
Asked: 2018-07-04 01:00:56 +0800 CST
  • 10

对于下面的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。

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

sql-server execution-plan
Martin Hope
Paul White
Asked: 2017-09-11 12:12:10 +0800 CST
  • 14

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

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 update
Martin Hope
Paul White
Asked: 2017-01-07 00:30:16 +0800 CST
  • 20

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

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

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

  1. 用于聚集列存储表上的非聚集 b 树索引的“书签”是什么？
  2. 上面描述的唯一和非唯一非聚集索引之间的差异是否仍然适用？
sql-server sql-server-2016
Martin Hope
Paul White
Asked: 2016-06-04 22:35:05 +0800 CST
  • 59

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

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

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

sql-server functions
Martin Hope
Paul White
Asked: 2016-05-09 06:07:58 +0800 CST
  • 26

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

设置

以下脚本创建了一个包含 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 读取如此不同？
  • 肯定在每次测试中都读取了完全相同的数据吗？
sql-server performance