Erik Darling's questions

大XML4u

某些查询计划太大而无法正确存储为 XML。您收到错误：XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels.

这很好。一切都需要限制以防止滥用。谁知道 129 层嵌套会发生什么。

当然，对于查询计划，您可以将文本格式的 XML 保存为 .sqlplan 文件，然后将其作为图形执行计划重新打开。

我的查询遇到的问题是，当我尝试显示该查询时，<已更改为<和。不幸的是，这在您将文件保存为有效的 .sqlplan（可以重新打开）之前增加了两个额外的步骤。>>

现在，我的查询执行以下操作：

    query_plan = 
         CASE
             WHEN TRY_CAST(qsp.query_plan AS XML) IS NOT NULL
             THEN TRY_CAST(qsp.query_plan AS XML)
             WHEN TRY_CAST(qsp.query_plan AS XML) IS NULL
             THEN 
                 (
                     SELECT
                         (
                             SELECT
                                 N''-- '' + NCHAR(13) + NCHAR(10) +
                                 N''-- This is a huge query plan.'' + NCHAR(13) + NCHAR(10) +
                                 N''-- Remove the headers and footers, save it as a .sqlplan file, and re-open it.'' + NCHAR(13) + NCHAR(10) +
                                 N''-- Depending on local factors, you may need to replace "<" and ">" too.'' + NCHAR(13) + NCHAR(10) +
                                 N''-- '' + NCHAR(13) + NCHAR(10) +
                                 REPLACE(qsp.query_plan, N''<RelOp'', NCHAR(13) + NCHAR(10) + N''<RelOp'') +
                                 NCHAR(13) + NCHAR(10) + N''--'' COLLATE Latin1_General_Bin2 AS [processing-instruction(query_plan)]
                         )
                         FOR XML PATH(N''''), 
                                 TYPE
                 )
         END,

通常，我会遵循自己的建议并将其添加到末尾，但这样做会让我回到有关节点、嵌套和 128 等的原始 XML 错误。

.value
     (
         './text()[1]', 
         'nvarchar(max)'
     );

我尝试添加REPLACE内部和外部选择来尝试修复问题，但该斑点似乎对我的魅力免疫。

有没有办法阻止或规避这些字符的转换，以使保存文件更容易一些？

如果您本地有 StackOverflow 数据库的副本，则可以生成一个查询计划，该计划在 中以 null 结尾TRY_CAST，并且在使用以下查询将其视为 XML 时抛出错误：

WITH
    p0 AS (SELECT pt.* FROM dbo.PostTypes AS pt),
    p1 AS (SELECT pp.* FROM p0 AS p CROSS JOIN p0 AS pp),
    p2 AS (SELECT pp.* FROM p1 AS p CROSS JOIN p1 AS pp),
    p3 AS (SELECT pp.* FROM p2 AS p CROSS JOIN p2 AS pp),
    p4 AS (SELECT pp.* FROM p3 AS p CROSS JOIN p3 AS pp),
    p5 AS (SELECT pp.* FROM p4 AS p CROSS JOIN p4 AS pp),
    p6 AS (SELECT pp.* FROM p5 AS p CROSS JOIN p5 AS pp),
    p7 AS (SELECT pp.* FROM p6 AS p CROSS JOIN p6 AS pp),
    p8 AS (SELECT pp.* FROM p7 AS p CROSS JOIN p7 AS pp),
    p9 AS (SELECT pp.* FROM p8 AS p CROSS JOIN p8 AS pp)
SELECT
    c = COUNT_BIG(*)
FROM p9 AS p
OPTION(RECOMPILE);

如果您只想查看生成的废话，可以使用此 GitHub gist 链接。

比方说

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

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

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

设置

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
);

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

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

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

谢谢！

过度扩张

在阻塞进程报告和死锁 XML 的 SQL Server 扩展事件中，可以获取多个 SQL 句柄值以识别引发事件中涉及的查询。

由于可能涉及到一个或多个 SQL 句柄，因此可靠地查询 XML 以检索它们可能很困难，并且还会使更直接的 XQuery 不正确，因为它只检索第一个存储的值。

sqlhandle = bd.value('(process/executionStack/frame/@sqlhandle)[1]', 'varchar(130)'),

用于说明的示例 XML 片段如下所示：

<executionStack>
      <frame line="1" stmtend="108" sqlhandle="0x020000008d18260040e407ba48fc247b0cb6121c21c2cf2b0000000000000000000000000000000000000000" />
      <frame line="1" stmtend="108" sqlhandle="0x02000000dd847b18dcaa4a09a89f56595186fcf91da8a7f70000000000000000000000000000000000000000" />
</executionStack>

此 SQL Fiddle提供了更完整的示例。

我已经做到了：

SELECT 
    sql_handle = 
        @x.query('for $s in //executionStack/frame return $s');  

但这并没有得到我所追求的。扩展该查询以使用该@sqlhandle属性会引发错误：

SELECT 
    sql_handle = 
        @x.query('for $s in //executionStack/frame/@sqlhandle return $s');  

消息 2396，级别 16，状态 1，第 60 行 XQuery [query()]：属性可能不会出现在元素之外

如何像这样查询 XML 以将所有列出的 SQL 句柄作为逗号分隔列表返回？

设置

对于这个演示，我使用的是2013 版本的 Stack Overflow 数据库和 SQL Server 2022 CTP2，但它可以追溯到 SQL Server 2017，这是我想检查的。

功能一

对于此函数，SQL Server 跟踪函数中花费的执行时间：

CREATE OR ALTER FUNCTION
    dbo.ScoreStats
(
    @UserId int
)
RETURNS
    @out table
    (
        TotalScore bigint
    )
WITH SCHEMABINDING
AS 
BEGIN

    INSERT
        @out
    (
        TotalScore
    )
    SELECT
        TotalScore = 
            SUM(x.Score)
    FROM 
    (
        SELECT
            Score = 
                SUM(p.Score)
        FROM dbo.Posts AS p
        WHERE p.OwnerUserId = @UserId

        UNION ALL

        SELECT
            Score = 
                SUM(c.Score)
        FROM dbo.Comments AS c
        WHERE c.UserId = @UserId    
    ) AS x;

    RETURN;

END;

这是查询和执行计划：

SELECT
    u.DisplayName,
    TotalScore = 
        (
            SELECT
                ss.TotalScore
            FROM dbo.ScoreStats(u.Id) AS ss
        )
FROM dbo.Users AS u
WHERE u.Reputation >= 1000000;

您可以看到，在查询计划和 Query Time Stats 属性中都准确地跟踪了时间。

功能二

这是第二个功能，它不会发生：

CREATE OR ALTER FUNCTION
    dbo.VoteStats()
RETURNS
    @out table
    (
        PostId int,
        UpVotes int,
        DownVotes int,
        UpMultipier AS 
             UpVotes * 2
    )
WITH SCHEMABINDING
AS 
BEGIN

    INSERT
        @out
    (
        PostId,
        UpVotes,
        DownVotes
    )
    SELECT
        v.PostId,
        UpVotes = 
            SUM
            (
                CASE v.VoteTypeId
                     WHEN 2
                     THEN 1
                     ELSE 0
                END
            ),
        DownVotes = 
            SUM
            (
                CASE v.VoteTypeId
                     WHEN 3
                     THEN 1
                     ELSE 0
                END
            )
    FROM dbo.Votes AS v
    GROUP BY 
        v.PostId;

    RETURN;

END;

这是查询和执行计划：

SELECT TOP (100)
     p.Id,
     vs.UpVotes,
     vs.DownVotes
FROM dbo.VoteStats() AS vs
JOIN dbo.Posts AS p
    ON vs.PostId = p.Id
WHERE vs.DownVotes > vs.UpMultipier
AND   p.CommunityOwnedDate IS NULL
AND   p.ClosedDate IS NULL
ORDER BY vs.UpVotes DESC;

在此查询中，时间没有在图形执行计划中准确跟踪，而是在 Query Time Stats 属性中进行跟踪。

MAXDOP 1 处的功能二

即使是强制连载，也无法准确跟踪时间：

SELECT TOP (100)
     p.Id,
     vs.UpVotes,
     vs.DownVotes
FROM dbo.VoteStats() AS vs
JOIN dbo.Posts AS p
    ON vs.PostId = p.Id
WHERE vs.DownVotes > vs.UpMultipier
AND   p.CommunityOwnedDate IS NULL
AND   p.ClosedDate IS NULL
ORDER BY vs.UpVotes DESC
OPTION(MAXDOP 1);

问题

回到手头的问题：为什么在一个查询计划中可以准确地跟踪时间，而在另一个查询计划中却没有？

线轴

在 SQL Server 中有几种假脱机。我感兴趣的两个是Table Spool s 和Index spools，在修改查询之外。

只读查询，特别是在嵌套循环连接的内侧，可以使用表或索引假脱机来潜在地减少 I/O 并提高查询性能。这些线轴可以是Eager或Lazy。就像你和我一样。

我的问题是：

• 哪些因素会影响表与索引假脱机的选择
• 在 Eager Spools 和 Lazy Spools 之间做出选择的因素有哪些

我听说过有关并行选择查询的内存授予的相互矛盾的事情：

• 内存授予乘以 DOP
• 内存授予除以 DOP

它是哪一个？

沙袋

在处理 Top Quality Blog Posts®时，我遇到了一些我发现非常有趣的优化器行为。我没有立即得到解释，至少不是我满意的解释，所以我把它放在这里以防有人聪明出现。

如果您想继续学习，可以在此处获取 2013 版的 Stack Overflow 数据转储。我正在使用 Comments 表，上面有一个额外的索引。

CREATE INDEX [ix_ennui] ON [dbo].[Comments] ( [UserId], [Score] DESC );

查询一

当我这样查询表时，我得到一个奇怪的查询计划。

WITH x
    AS
     (
         SELECT   TOP 101
                  c.UserId, c.Text, c.Score
         FROM     dbo.Comments AS c
         ORDER BY c.Score DESC
     )
SELECT *
FROM   x
WHERE  x.Score >= 500;

Score 上的 SARGable 谓词未被推入 CTE。它在计划的后期出现在过滤器运算符中。

我觉得很奇怪，因为它ORDER BY与过滤器位于同一列。

查询二

如果我更改查询，它确实会被推送。

WITH x
    AS
     (
         SELECT   c.UserId, c.Text, c.Score
         FROM     dbo.Comments AS c
     )
SELECT TOP 101 *
FROM   x
WHERE  x.Score >= 500
ORDER BY x.Score DESC;

查询计划也发生变化，运行速度更快，不会溢出到磁盘。它们都产生相同的结果，谓词在非聚集索引扫描中。

查询三

这相当于像这样编写查询：

SELECT   TOP 101
         c.UserId, c.Text, c.Score
FROM     dbo.Comments AS c
WHERE c.Score >= 500
ORDER BY c.Score DESC;

查询四

使用派生表得到与初始 CTE 查询相同的“坏”查询计划

SELECT *
FROM   (   SELECT   TOP 101
                    c.UserId, c.Text, c.Score
           FROM     dbo.Comments AS c
           ORDER BY c.Score DESC ) AS x
WHERE x.Score >= 500;

事情变得更奇怪，当...

我将查询更改为按升序排列数据，并将过滤器更改为<=.

为了避免让这个问题过长，我将把所有内容放在一起。

查询

--Derived table
SELECT *
FROM   (   SELECT   TOP 101
                    c.UserId, c.Text, c.Score
           FROM     dbo.Comments AS c
           ORDER BY c.Score ASC ) AS x
WHERE x.Score <= 500;


--TOP inside CTE
WITH x
    AS
     (
         SELECT   TOP 101
                  c.UserId, c.Text, c.Score
         FROM     dbo.Comments AS c
         ORDER BY c.Score ASC
     )
SELECT *
FROM   x
WHERE  x.Score <= 500;


--Written normally
SELECT   TOP 101
         c.UserId, c.Text, c.Score
FROM     dbo.Comments AS c
WHERE c.Score <= 500
ORDER BY c.Score ASC;

--TOP outside CTE
WITH x
    AS
     (
         SELECT   c.UserId, c.Text, c.Score
         FROM     dbo.Comments AS c
     )
SELECT TOP 101 *
FROM   x
WHERE  x.Score <= 500
ORDER BY x.Score ASC;

计划

计划链接。

请注意，这些查询都没有利用非聚集索引——这里唯一改变的是过滤器运算符的位置。在任何情况下都不会将谓词推送到索引访问。

问题出现！

在某些情况下可以推送 SARGable 谓词而不是在其他情况下是否有原因？按降序排序的查询之间的差异很有趣，但这些查询与按升序排列的查询之间的差异很奇怪。

对于任何感兴趣的人，这里是只有一个索引的计划Score：

• 描述符
• 升序控制

我有一个 SQL Server 数据库，其中的查询非常慢，并且有很多锁定和阻塞。

当我查看缺少的索引 DMV 和查询计划时，没有任何建议。

这是为什么？

我有一个数据库触发器，用于防止我在用户数据库中创建某些过程。

它出现在 中sys.triggers，带有object_id，但我无法使用该object_id函数找到它。

SELECT OBJECT_ID(t.name, t.type) AS object_id, *
FROM   sys.triggers AS t;

同样，我可以在sys.dm_exec_trigger_stats. 我object_name无法解决，但object_definition确实如此。

SELECT OBJECT_NAME(dets.object_id, dets.database_id) AS object_name,
       OBJECT_DEFINITION(dets.object_id) AS object_definition,
       *
FROM   sys.dm_exec_trigger_stats AS dets;

是否有一个函数可以接受数据库级触发器的对象 ID，并返回其名称？

我读到只有Trivial Plans 可以是 Simple Parameterized，并且并非所有查询（即使计划是 Trivial ）都可以是 Simple Parameterized。

那为什么这个计划同时显示Full Optimization和Simple Parameterization？

[问候]

（勾选一项）

[ ] Well trained professional, [ ] Casual reader, [ ] Hapless wanderer,

我有一个（勾选所有适用项）

[ ] query [ ] stored procedure [ ] database thing maybe  

运行良好（如果适用）

[ ] yesterday [ ] in recent memory [ ] at some point 

但现在突然变慢了。

我已经检查以确保它没有被阻止，并且它不是一些长期运行的维护任务、报告或其他带外进程的受害者。

有什么问题，我应该怎么做，我可以提供什么信息来获得帮助？

[*Insert appropriate closing remarks*]

我经常看到用DATEADD和DATEDIFF在WHERE子句中定义范围、将日期时间展平为 0 小时或查找一个月或一年的最后一天的查询，但我不明白所有部分是如何工作的。

例如，这将查找从当天开始到 30 天前开始的日期。

SELECT *
FROM tbl
WHERE datecol >= DATEADD(DAY, DATEDIFF(DAY, 0, GETUTCDATE()), 0)
AND datecol   <  DATEADD(DAY, DATEDIFF(DAY, 0, GETUTCDATE()), -30);

这一切的不同部分完成了什么？

批处理模式内存授予反馈是2017 查询处理器中的一系列功能的一部分，其中包括：

• 批处理模式自适应连接
• 交错执行
• 批处理模式内存授予反馈

那么批处理模式内存授予反馈是如何工作的呢？

Interleaved Execution 是2017 查询处理器中一系列功能的一部分，其中包括：

• 批处理模式自适应连接
• 交错执行
• 批处理模式内存授予反馈

那么交错执行是如何工作的呢？

批处理模式自适应联接是2017 查询处理器中一系列功能的一部分，其中包括：

• 批处理模式自适应联接
• 交错执行
• 批处理模式内存授予反馈

那么自适应联接是如何工作的呢？

有关SQL Server中标量 UDF的危险的文章很多。随意搜索会返回大量结果。

不过，在某些地方，标量 UDF 是唯一的选择。

例如：在处理 XML 时：XQuery 不能用作计算列定义。Microsoft 记录的一种选择是使用标量 UDF将 XQuery 封装在标量 UDF 中，然后在计算列中使用它。

这有各种影响和一些解决方法。

• 查询表时逐行执行
• 强制对表的所有查询串行运行

您可以通过模式绑定函数来绕过逐行执行，并保留计算列或对其进行索引。即使没有引用标量 UDF，这些方法都不能阻止对表的查询的强制序列化。

有没有已知的方法可以做到这一点？

查看sys.dm_os_wait_stats时，以下列定义为BIGINT

• waiting_tasks_count
• 等待时间毫秒
• 最大等待时间毫秒

如果这些值中的任何一个超过9,223,372,036,854,775,807，计数器是重置为 0，还是干脆停止计数？

从表定义中不清楚幕后发生了什么：

sp_helptext 'sys.dm_os_wait_stats'

退货：

CREATE VIEW sys.dm_os_wait_stats AS  
 SELECT *  
 FROM OpenRowset(TABLE SYSWAITSTATS)  

所以这有点像一个黑盒子。

在某些 DMV 中，大数字可能会变为负数。一个例子是 中的total_elapsed_time列dm_exec_requests。

SQL Server 的问答从这个问题迁移到这里。

如果将“NOT NULL”约束添加到已具有 NULL 值的列的表中，会发生什么情况？

这不是关于：

这不是关于接受用户输入或使用变量的全面查询的问题。

这严格来说是关于ISNULL()在WHERE子句中用于将值替换为金丝雀值以与谓词进行比较的查询，以及在 SQL Server中将NULL这些查询重写为SARGable的不同方法。

你怎么不在那边占位子？

我们的示例查询针对 SQL Server 2016 上 Stack Overflow 数据库的本地副本，并查找NULL年龄或年龄 < 18 岁的用户。

SELECT COUNT(*)
FROM dbo.Users AS u
WHERE ISNULL(u.Age, 17) < 18;

查询计划显示了一个经过深思熟虑的非聚集索引的扫描。

扫描操作符显示（感谢在 SQL Server 的较新版本中对实际执行计划 XML 的添加）我们读取了每个臭名昭著的行。

总的来说，我们进行了 9157 次读取并使用了大约半秒的 CPU 时间：

Table 'Users'. Scan count 1, logical reads 9157, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 485 ms,  elapsed time = 483 ms.

问题： 有什么方法可以重写此查询以使其更高效，甚至可能是 SARGable？

随时提供其他建议。我认为我的答案不一定是答案，并且有足够多的聪明人想出可能更好的替代方案。

如果你想在自己的电脑上玩，请到这里下载 SO 数据库。

谢谢！

这绝对是一个错误。详细信息在旧的 Connect 站点上，并且不再可用，因为它已经退役，取而代之的是数量不确定的其他解决方案，包括第三方和第一方。

在测试对sp_BlitzCache的一些更改时（完全披露，我是作者之一），我遇到了我认为是我们代码中的错误。

有一次，我们匹配查询计划哈希以获得查询成本。我们这样做：statement.value('sum(/p:StmtSimple[xs:hexBinary(substring(@QueryHash, 3)) = xs:hexBinary(sql:column("b.QueryHash"))]/@ StatementSubTreeCost)', '浮动')

据我所知，这已经奏效了。然而，在一个奇怪的情况下，XML 中的子字符串抛出一个NULL值，而计划显示成本为 0，尽管它相当高。

深入研究执行计划，我注意到一个问题哈希的查询计划哈希长 17 个字符，而其余的是 18 个字符。以下是示例：

QueryPlanHash="0x4410B0CA640CDA89"
QueryPlanHash="0x2262FEA4CE645569"
QueryPlanHash="0xED4F225CC0E97E5" -- 问题！
QueryPlanHash="0xBF878EEE6DB955EA"
QueryPlanHash="0x263B53BC8C14A452"
QueryPlanHash="0x89F5F146CF4B476F"
QueryPlanHash="0xEF47EA40805C8961"
QueryPlanHash="0xB7BE27D6E43677A5"
QueryPlanHash="0x815C54EC43A6A6E9"

查询计划哈希被列为——大概这应该总是相同binary 8的长度，但是像我这样的人对二进制值了解多少？

稍微尝试一下 XQuery，我发现通过将子字符串更改为从第二个位置开始，它会得出一个有效（尽管不正确）的哈希值。

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
SELECT   
        QueryPlanCost = statement.value('sum(/p:StmtSimple/@StatementSubTreeCost)', 'float'),
        q.n.value('substring(@QueryPlanHash, 2)', 'binary(8)')
FROM    #statements s
CROSS APPLY s.statement.nodes('/p:StmtSimple') AS q(n)
OPTION(RECOMPILE);

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
SELECT   
        QueryPlanCost = statement.value('sum(/p:StmtSimple/@StatementSubTreeCost)', 'float'),
        q.n.value('substring(@QueryPlanHash, 3)', 'binary(8)')
FROM    #statements s
CROSS APPLY s.statement.nodes('/p:StmtSimple') AS q(n)
OPTION(RECOMPILE);

我正在运行 SQL Server 2016 SP1 (13.0.4001)。

有没有人遇到过这个？

17 个字符是值的有效长度binary 8吗？