Martin Smith's questions

对于行存储表的示例...

CREATE TABLE T(Id INT PRIMARY KEY, C1 INT NULL, C2 INT NULL);

有多种不同的方法可以从 SQL Server 中的元数据中检索表行数 - 例如以下方法

SELECT SUM(rows) 
FROM sys.partitions
WHERE object_id = object_id('dbo.T') AND index_id <= 1;

SELECT SUM(row_count) 
FROM sys.dm_db_partition_stats
WHERE object_id = object_id('dbo.T') AND index_id <= 1;

SELECT SUM(rows)
FROM sys.sysindexes
WHERE id = object_id('dbo.T') AND indid <= 1;

SELECT OBJECTPROPERTYEX(object_id('dbo.T'), 'Cardinality') 

执行计划显然显示了正在使用的各种不同对象 - 例如下面的。

• sysrowsets OUTER APPLY OpenRowset(TABLE ALUCOUNT
• sysidxstats 交叉应用 OpenRowSet（表分区计数
• sysidxstats i 交叉应用 OpenRowSet(TABLE INDEXPROP

这是怎么回事？SQL Server 真的在多个地方维护这些元数据吗？如果是这样，哪种方法最可靠？

使用数据库设置

CREATE TYPE dbo.TableType AS TABLE (
prop1 int, 
prop2 datetime2, 
prop3 varchar(1000)
);


GO

CREATE OR ALTER PROC dbo.TestTableTypePerf
@Data dbo.TableType READONLY
AS
SELECT COUNT(*)
FROM @Data;

以下代码以流式传输方式传递 TVP 值。可枚举值直到调用后才被评估ExecuteScalarAsync，并且不需要将全部 5,000,000 个元素具体化为客户端中的集合。

越来越流行的是放弃 TVP 而选择 JSON 字符串，对于 JSON 能做类似的事情吗？

using System.Data;
using Microsoft.Data.SqlClient;
using Microsoft.Data.SqlClient.Server;

const string connectionString =
    @"...";

await TvpTest();

return;

static async Task TvpTest()
{
    await using var conn = new SqlConnection(connectionString);
    await conn.OpenAsync();
    await using var cmd = new SqlCommand("dbo.TestTableTypePerf", conn);
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add(new SqlParameter
    {
        ParameterName = "@Data",
        SqlDbType = SqlDbType.Structured,
        TypeName = "dbo.TableType",
        Value = GetEnumerableOfRandomSqlDataRecords(5_000_000)
    });

    Console.WriteLine($"calling ExecuteScalarAsync at {DateTime.Now:O}");
    var result = await cmd.ExecuteScalarAsync();

    Console.WriteLine($"writing result at {DateTime.Now:O}");
    Console.WriteLine(result);
}

static IEnumerable<SqlDataRecord> GetEnumerableOfRandomSqlDataRecords(uint length)
{
    SqlMetaData[] metaData =
    [
        new SqlMetaData("prop1", SqlDbType.Int),
        new SqlMetaData("prop2", SqlDbType.DateTime2),
        new SqlMetaData("prop3", SqlDbType.VarChar, 1000)
    ];

    foreach (var dto in GetEnumerableOfRandomDto(length))
    {
        var record = new SqlDataRecord(metaData);
        record.SetInt32(0, dto.Prop1);
        record.SetDateTime(1, dto.Prop2);
        record.SetString(2, dto.Prop3);

        yield return record;
    }
}


static IEnumerable<Dto> GetEnumerableOfRandomDto(uint length)
{
    var rnd = new Random();

    for (var i = 0; i < length; i++)
    {
        yield return new Dto(rnd.Next(1, int.MaxValue), 
                             DateTime.Now.AddMinutes(rnd.Next(1, 10000)),
                             Guid.NewGuid().ToString()
                             );

        if ((i + 1) % 100_000 == 0)
            Console.WriteLine($"Generated enumerable {i + 1} at {DateTime.Now:O}");
    }
}


internal record Dto(int Prop1, DateTime Prop2, string Prop3);

对于示例数据...

/*Quick and dirty generation of some rows of data*/
SELECT value as [orderid], 
       1 as [custid], 
       1 as [empid], 
       1 as [shipperid], 
       getdate() as [orderdate], 
       'abcdefgh' as [filler]
INTO dbo.Orders
FROM generate_series(1,10000000)

CREATE CLUSTERED INDEX [idx_cl_od] ON [dbo].[Orders]
(
    [orderdate] ASC
)

UPDATE STATISTICS dbo.Orders WITH FULLSCAN

以及以下查询

SELECT [orderid], [custid], [empid], [shipperid], [orderdate], [filler]
FROM dbo.Orders
WHERE orderid <=7601715 AND 1=1 /*Prevent simple parameterisation*/

然后在我的开发机器（SQL Server 2022，DOP 为 4）上，聚集索引扫描的 IO 成本46.8853与串行或并行计划无关。扫描的 CPU 成本11.0002在串行计划和 2.75004并行计划中，因此我预计计划之间的临界点是并行运算符超出时8.25016（估计进入的行数约为 450 万时达到的阈值）。实际上，在实际发生这种情况时，收集流运算符的成本是13.0501（比我预期的高出约 300 万行）。

如果 SQL Server 不使用总体计划成本作为临界点，那么实际逻辑是什么？

（pastetheplan 上的估算计划的 XML）

我最近遇到一个问题，tSQLt测试需要很长时间才能运行。

被测程序正在执行 38 个表 (!) 连接（具有 37 个伪造的表和一个表值参数）。

只有两个伪造的表和 TVP 插入了任何行

编译时间非常慢。

显示跟踪标志 8675

End of simplification, time: 0.002 net: 0.002 total: 0 net: 0.002
end exploration, tasks: 549 no total cost time: 0.013 net: 0.013 total: 0 net: 0.015
end search(0),  cost: 13372.9 tasks: 3517 time: 0.012 net: 0.012 total: 0 net: 0.028
end exploration, tasks: 3983 Cost = 13372.9 time: 0 net: 0 total: 0 net: 0.028
end search(1),  cost: 6706.79 tasks: 10187 time: 0.024 net: 0.024 total: 0 net: 0.052
end exploration, tasks: 10188 Cost = 6706.79 time: 0 net: 0 total: 0 net: 0.052
end search(1),  cost: 6706.79 tasks: 61768 time: 0.165 net: 0.165 total: 0 net: 0.218
*** Optimizer time out abort at task 614400 ***
end search(2),  cost: 6706.79 tasks: 614400 time: 12.539 net: 12.539 total: 12 net: 12.758
*** Optimizer time out abort at task 614400 ***
End of post optimization rewrite, time: 0.001 net: 0.001 total: 12 net: 12.759
End of query plan compilation, time: 0.003 net: 0.003 total: 12 net: 12.762
SQL Server parse and compile time: 
   CPU time = 12735 ms, elapsed time = 12770 ms.

对于空表之间的每个连接，估计行数看起来呈指数增长，直到最后估计行数为 135,601,000，并且查询具有巨大的估计成本，证明编译时间更长。

许多这些连接涉及一个特定的表，并且向该表插入一行足以阻止该表所涉及的连接的爆炸（基数估计器输出表明它现在正在使用该表的统计直方图)

最初的行为对我来说似乎很奇怪。SQL Server 知道它加入的表是空的，并且计划缓存白皮书指出向空表插入任何行都会导致达到重新编译阈值，那么有什么充分的理由吗？

Repro 显示估计的行数增长（尽管没有很长的编译时间）

CREATE TABLE T1(C1 INT);

INSERT INTO T1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9);

CREATE TABLE T2(C1 INT, C2 VARCHAR(MAX));

SELECT *
FROM T1 LEFT OUTER JOIN T2 ON T1.C1 = T2.C1
        LEFT OUTER JOIN T2 T3 ON T3.C1 = T2.C1
        LEFT OUTER JOIN T2 T4 ON T4.C1 = T2.C1
        LEFT OUTER JOIN T2 T5 ON T5.C1 = T2.C1
        LEFT OUTER JOIN T2 T6 ON T6.C1 = T2.C1
        LEFT OUTER JOIN T2 T7 ON T7.C1 = T2.C1
        LEFT OUTER JOIN T2 T8 ON T8.C1 = T2.C1
        LEFT OUTER JOIN T2 T9 ON T9.C1 = T2.C1
        LEFT OUTER JOIN T2 T10 ON T10.C1 = T2.C1
        LEFT OUTER JOIN T2 T11 ON T11.C1 = T2.C1
        LEFT OUTER JOIN T2 T12 ON T12.C1 = T2.C1
        LEFT OUTER JOIN T2 T13 ON T13.C1 = T2.C1
        LEFT OUTER JOIN T2 T14 ON T14.C1 = T2.C1
        LEFT OUTER JOIN T2 T15 ON T15.C1 = T2.C1
        LEFT OUTER JOIN T2 T16 ON T16.C1 = T2.C1
        LEFT OUTER JOIN T2 T17 ON T17.C1 = T2.C1
        LEFT OUTER JOIN T2 T18 ON T18.C1 = T2.C1
        LEFT OUTER JOIN T2 T19 ON T19.C1 = T2.C1
        LEFT OUTER JOIN T2 T20 ON T20.C1 = T2.C1
        LEFT OUTER JOIN T2 T21 ON T21.C1 = T2.C1
        LEFT OUTER JOIN T2 T22 ON T22.C1 = T2.C1
        LEFT OUTER JOIN T2 T23 ON T23.C1 = T2.C1
        LEFT OUTER JOIN T2 T24 ON T24.C1 = T2.C1
        LEFT OUTER JOIN T2 T25 ON T25.C1 = T2.C1
        LEFT OUTER JOIN T2 T26 ON T26.C1 = T2.C1
        LEFT OUTER JOIN T2 T27 ON T27.C1 = T2.C1
        LEFT OUTER JOIN T2 T28 ON T28.C1 = T2.C1
        LEFT OUTER JOIN T2 T29 ON T29.C1 = T2.C1
        LEFT OUTER JOIN T2 T30 ON T30.C1 = T2.C1
        LEFT OUTER JOIN T2 T31 ON T31.C1 = T2.C1
        LEFT OUTER JOIN T2 T32 ON T32.C1 = T2.C1
        LEFT OUTER JOIN T2 T33 ON T33.C1 = T2.C1
        LEFT OUTER JOIN T2 T34 ON T34.C1 = T2.C1
        LEFT OUTER JOIN T2 T35 ON T35.C1 = T2.C1
        LEFT OUTER JOIN T2 T36 ON T36.C1 = T2.C1
        LEFT OUTER JOIN T2 T37 ON T37.C1 = T2.C1
        LEFT OUTER JOIN T2 T38 ON T38.C1 = T2.C1
        LEFT OUTER JOIN T2 T39 ON T39.C1 = T2.C1

我有一个包含以下内容并使用 UTF8 编码保存的示例数据文件。

oab~opqr
öab~öpqr
öab~öpqr

该文件的格式为固定宽度，第 1 到第 3 列各分配 1 个字符，第 4 列保留 5 个字符。

我创建了一个 XML 格式文件，如下所示

<?xml version = "1.0"?>  
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">  
   <RECORD>  
      <FIELD xsi:type="CharFixed" ID="Col1" LENGTH="1"/>       
      <FIELD xsi:type="CharFixed" ID="Col2" LENGTH="1"/> 
      <FIELD xsi:type="CharFixed" ID="Col3" LENGTH="1"/> 
      <FIELD xsi:type="CharFixed" ID="Col4" LENGTH="5"/> 
      <FIELD xsi:type="CharTerm" ID="LINE_BREAK" TERMINATOR="\n"/> 
   </RECORD>  
   <ROW>  
      <COLUMN SOURCE="Col1" NAME="Col1" xsi:type="SQLNVARCHAR"/>  
      <COLUMN SOURCE="Col2" NAME="Col2" xsi:type="SQLNVARCHAR"/> 
      <COLUMN SOURCE="Col3" NAME="Col3" xsi:type="SQLNVARCHAR"/>  
      <COLUMN SOURCE="Col4" NAME="Col4" xsi:type="SQLNVARCHAR"/> 
   </ROW>  
</BCPFORMAT>

令人失望地运行以下 SQL...

SELECT *
FROM OPENROWSET
(
BULK 'mydata.txt',
FORMATFILE = 'myformat_file.xml',
CODEPAGE = '65001'
) AS X

产生以下结果

Col1 Col2 Col3 Col4
---- ---- ---- -----
o    a    b    ~opqr
�    �    a    b~öp
�    �    a    b~öp

我从中得出的结论LENGTH是计算字节而不是字符。

有什么方法可以让这个使用 UTF8 编码的固定字符宽度正常工作吗？

（目标环境是从 Blob 存储读取的 Azure SQL 数据库）

COLLATION="LATIN1_GENERAL_100_CI_AS_SC_UTF8"注意：评论中建议添加FIELD元素可能会有所帮助，但结果保持不变。

设置

DROP TABLE IF EXISTS #EmptyTable, #BigTable

CREATE TABLE #EmptyTable(A int);
CREATE TABLE #BigTable(A int);

INSERT INTO #BigTable
SELECT TOP 10000000 CRYPT_GEN_RANDOM(3)
FROM   sys.all_objects o1,
       sys.all_objects o2,
       sys.all_objects o3;

询问

WITH agg
     AS (SELECT DISTINCT a
         FROM   #BigTable)
SELECT *
FROM   #EmptyTable E
       INNER HASH JOIN agg B
                    ON B.A = E.A;

执行计划

问题

这是我今天之前没有注意到的现象的简化重现。我对内部散列连接的期望是，如果构建输入为空，则不应执行探测端，因为连接不会返回任何行。上面的示例与此相矛盾，并从表中读取了 1000 万行。这使查询的执行时间增加了 2.196 秒 (99.9%)。

额外的观察

1. 执行OPTION (MAXDOP 1)计划没有从中读取任何行。适用于散列连接内部的所有运算符#BigTable。ActualExecutions0
2. 对于查询SELECT * FROM #EmptyTable E INNER HASH JOIN #BigTable B ON B.A = E.A——我得到一个并行计划，散列连接内部的扫描运算符确实有ActualExecutionsDOP，但仍然没有读取任何行。该计划没有重新分区流运算符（或聚合）

问题

这里发生了什么？为什么原始计划会出现问题而其他情况却不会？

下面是我在生产中遇到的一些简化版本（在处理异常多的批次的一天，计划变得灾难性地更糟）。

已使用新的基数估计器针对 2014 年和 2016 年对重现进行了测试。

CREATE TABLE T1 (FromDate  DATE, ToDate DATE, SomeId INT, BatchNumber INT);

INSERT INTO T1
SELECT TOP 1000 FromDate = '2017-01-01',
                ToDate = '2017-01-01',
                SomeId = ROW_NUMBER() OVER (ORDER BY @@SPID) -1,
                BatchNumber = 1
FROM   master..spt_values v1

CREATE TABLE T2 (SomeDateTime DATETIME, SomeId INT, INDEX IX(SomeDateTime));

INSERT INTO T2
SELECT TOP 1000000 '2017-01-01',
                   ROW_NUMBER() OVER (ORDER BY @@SPID) %1000
FROM   master..spt_values v1,
       master..spt_values v2

T1包含 1,000 行。

、和在所有这些FromDate中都是相同的。唯一不同的值是介于和之间的值ToDateBatchNumberSomeId0999

+------------+------------+--------+-----------+
|  FromDate  |   ToDate   | SomeId | BatchNumber |
+------------+------------+--------+-----------+
| 2017-01-01 | 2017-01-01 |      0 |         1 |
| 2017-01-01 | 2017-01-01 |      1 |         1 |
....
| 2017-01-01 | 2017-01-01 |    998 |         1 |
| 2017-01-01 | 2017-01-01 |    999 |         1 |
+------------+------------+--------+-----------+

T2包含 100 万行

但只有 1,000 个不同的。每个重复 1,000 次如下。

+-------------------------+--------+-------+
|      SomeDateTime       | SomeId | Count |
+-------------------------+--------+-------+
| 2017-01-01 00:00:00.000 |      0 |  1000 |
| 2017-01-01 00:00:00.000 |      1 |  1000 |
...
| 2017-01-01 00:00:00.000 |    998 |  1000 |
| 2017-01-01 00:00:00.000 |    999 |  1000 |
+-------------------------+--------+-------+

执行以下

SELECT *
FROM   T1
       INNER JOIN T2
               ON CAST(t2.SomeDateTime AS DATE) BETWEEN T1.FromDate AND T1.ToDate
                  AND T1.SomeId = T2.SomeId
WHERE  T1.BatchNumber = 1

在我的机器上大约需要 7 秒。实际行和估计行对于计划中的所有操作员来说都是完美的。

现在向 T1 添加 3,000 个附加批次（批次编号为 2 至 3001）。这些每个克隆批号 1 的现有千行

INSERT INTO T1
SELECT T1.FromDate,
       T1.ToDate,
       T1.SomeId,
       Nums.NewBatchNumber
FROM   T1
       CROSS JOIN (SELECT TOP (3000) 1 + ROW_NUMBER() OVER (ORDER BY @@SPID) AS NewBatchNumber
                   FROM   master..spt_values v1, master..spt_values v2) Nums 

并更新运气的统计数据

 UPDATE STATISTICS T1 WITH FULLSCAN

并再次运行原始查询。

SELECT *
FROM   T1
       INNER JOIN T2
               ON CAST(t2.SomeDateTime AS DATE) BETWEEN T1.FromDate AND T1.ToDate
                  AND T1.SomeId = T2.SomeId
WHERE  T1.BatchNumber = 1

在杀死它之前，我让它运行了一分钟。到那时它已经输出了 40,380 行，所以我想输出完整的一百万行需要 25 分钟。

唯一改变的是我添加了一些与T1.BatchNumber = 1谓词不匹配的额外行。

然而，计划现在已经改变了。它使用嵌套循环代替，虽然来自的行数t1仍然正确估计为 1,000 (①)，但连接行数的估计现在已从 100 万下降到一千 (②)。

所以问题是……

为什么添加额外的行会以BatchNumber <> 1某种方式影响对何时连接的行的估计BatchNumber = 1？

向表中添加行最终会减少整个查询中估计的行数，这似乎违反直觉。

在下面的查询中，两个执行计划估计在唯一索引上执行 1,000 次查找。

查找是由同一源表上的有序扫描驱动的，因此看起来应该最终以相同的顺序查找相同的值。

两个嵌套循环都有<NestedLoops Optimized="false" WithOrderedPrefetch="true">

有谁知道为什么这个任务在第一个计划中的成本是 0.172434 而在第二个计划中是 3.01702？

（这个问题的原因是第一个查询被建议给我作为优化，因为计划成本明显低得多。实际上在我看来它好像做了更多的工作但我只是试图解释差异.. .)

设置

CREATE TABLE dbo.Target(KeyCol int PRIMARY KEY, OtherCol char(32) NOT NULL);

CREATE TABLE dbo.Staging(KeyCol int PRIMARY KEY, OtherCol char(32) NOT NULL); 

INSERT INTO dbo.Target
SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY @@SPID), LEFT(NEWID(),32)
FROM master..spt_values v1,  
     master..spt_values v2;

INSERT INTO dbo.Staging
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY @@SPID), LEFT(NEWID(),32)
FROM master..spt_values v1;

查询 1 _{“粘贴计划”链接}

WITH T
     AS (SELECT *
         FROM   Target AS T
         WHERE  T.KeyCol IN (SELECT S.KeyCol
                             FROM   Staging AS S))
MERGE T
USING Staging S
ON ( T.KeyCol = S.KeyCol )
WHEN NOT MATCHED THEN
  INSERT ( KeyCol, OtherCol )
  VALUES(S.KeyCol, S.OtherCol )
WHEN MATCHED AND T.OtherCol > S.OtherCol THEN
  UPDATE SET T.OtherCol = S.OtherCol;

查询 2 _{“粘贴计划”链接}

MERGE Target T
USING Staging S
ON ( T.KeyCol = S.KeyCol )
WHEN NOT MATCHED THEN
  INSERT ( KeyCol, OtherCol )
  VALUES( S.KeyCol, S.OtherCol )
WHEN MATCHED AND T.OtherCol > S.OtherCol THEN
  UPDATE SET T.OtherCol = S.OtherCol; 

查询 1

查询 2

以上是在 SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64) 上测试的

@Joe Obbish在评论中指出，更简单的复制是

SELECT *
FROM staging AS S 
  LEFT OUTER JOIN Target AS T 
    ON T.KeyCol = S.KeyCol;

对比

SELECT *
FROM staging AS S 
  LEFT OUTER JOIN (SELECT * FROM Target) AS T 
    ON T.KeyCol = S.KeyCol;

对于 1,000 行的临时表，以上两个仍然具有相同的计划形状，带有嵌套循环，并且没有派生表的计划看起来更便宜，但是对于 10,000 行的临时表和与上面相同的目标表，成本差异确实改变了计划形状（完整扫描和合并连接看起来比昂贵的搜索更具吸引力）显示这种成本差异可能会产生影响，而不仅仅是使比较计划变得更加困难。

我注意到在每日数据仓库构建中有一个运行时间相对较长（20 分钟以上）的自动更新统计操作。涉及的表是

CREATE TABLE [dbo].[factWebAnalytics](
    [WebAnalyticsId] [bigint] IDENTITY(1,1) NOT NULL,
    [MarketKey] [int] NOT NULL CONSTRAINT [DF_factWebAnalytics_MarketKey]  DEFAULT ((-1)),
    /*Other columns removed*/
 CONSTRAINT [PK_factWebAnalytics] PRIMARY KEY CLUSTERED 
(
    [MarketKey] ASC,
    [WebAnalyticsId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [MarketKeyPS]([MarketKey])
) ON [MarketKeyPS]([MarketKey])

这是在 Microsoft SQL Server 2012 (SP1) - 11.0.3513.0 (X64) 上运行，因此可写列存储索引不可用。

该表包含两个不同市场密钥的数据。该构建将特定 MarketKey 的分区切换到暂存表，禁用列存储索引，执行必要的写入，重建列存储，然后将其切换回。

更新统计信息的执行计划显示它从表中提取所有行，对它们进行排序，得到严重错误的估计行数并溢出到tempdb溢出级别 2。

跑步

SELECT [s].[name] AS "Statistic",
       [sp].*
FROM   [sys].[stats] AS [s]
       OUTER APPLY sys.dm_db_stats_properties ([s].[object_id], [s].[stats_id]) AS [sp]
WHERE  [s].[object_id] = OBJECT_ID(N'[dbo].[factWebAnalytics]'); 

演出

如果我明确尝试将该索引的统计数据的样本量减少到其他人使用的样本量

UPDATE STATISTICS [dbo].[factWebAnalytics] [PK_factWebAnalytics] WITH SAMPLE 897667 ROWS

查询再次运行 20 分钟以上，执行计划显示它正在处理所有行，而不是请求的 897,667 样本。

所有这一切结束时生成的统计数据不是很有趣，而且似乎绝对不值得花时间进行全面扫描。

Statistics for INDEX 'PK_factWebAnalytics'.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Name                            Updated                         Rows                            Rows Sampled                    Steps                           Density                         Average Key Length              String Index                    
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PK_factWebAnalytics             Jan 22 2016 11:31AM             420072086                       420072086                       2                               0                               12                              NO                                                              420072086                       

All Density                     Average Length                  Columns                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0.5                             4                               MarketKey                       
2.380544E-09                    12                              MarketKey, WebAnalyticsId       

Histogram Steps                 
RANGE_HI_KEY                    RANGE_ROWS                      EQ_ROWS                         DISTINCT_RANGE_ROWS             AVG_RANGE_ROWS                  
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1                               0                               3.441652E+08                    0                               1                               
2                               0                               7.590685E+07                    0                               1                               

知道为什么我会遇到这种行为，以及除了使用NORECOMPUTE这些行为之外我可以采取哪些步骤？

重现脚本在这里。它只是创建一个具有聚簇 PK 和列存储索引的表，并尝试使用较小的样本量更新 PK 统计信息。这不使用分区 - 表明不需要分区方面。然而，上述分区的使用确实使事情变得更糟，因为切换分区然后将其切换回来（即使没有任何其他更改）将使 modification_counter 增加分区中行数的两倍，从而实际上保证统计信息将是被认为陈旧并自动更新。

我已经尝试将非聚集索引添加到表中，如 KB2986627 中所示（两者均未过滤任何行，然后，当失败时，未过滤的 NCI 也没有任何效果）。

重现未显示 build 11.0.6020.0 上的问题行为，升级到 SP3 后，该问题现已修复。

此连接项中的示例代码

显示一个错误

SELECT COUNT(*)
FROM   dbo.my_splitter_1('2') L1
       INNER JOIN dbo.my_splitter_1('') L2
         ON L1.csv_item = L2.csv_item

返回正确的结果。但以下返回不正确的结果（2014 年使用新的基数估计器）

SELECT
    (SELECT COUNT(*)
    FROM dbo.my_splitter_1('2') L1
     INNER JOIN dbo.my_splitter_1('') L2
        ON L1.csv_item = L2.csv_item)

由于它错误地将 L2 的结果加载到公共子表达式假脱机中，然后重播 L1 结果的结果。

我很好奇为什么两个查询之间的行为会有所不同。Trace Flag 8675 显示有效的进入search(0) - transaction processing，失败的进入search(1) - quick plan。

所以我假设额外转换规则的可用性是行为差异的背后原因（例如，禁用 BuildGbApply 或GenGbApplySimple似乎可以修复它）。

但是为什么针对这些非常相似的查询的两个计划会遇到不同的优化阶段呢？从我读过的内容来看search (0)，至少需要三个表，而第一个示例中肯定不满足该条件。

我最近遇到了这个问题，但在网上找不到任何关于它的讨论。

下面的查询

DECLARE @S VARCHAR(1) = '';

WITH T
     AS (SELECT name + @S AS name2,
                *
         FROM   master..spt_values)
SELECT *
FROM   T T1
       INNER JOIN T T2
         ON T1.name2 = T2.name2;

总是得到一个嵌套循环计划

尝试强制使用INNER HASH JOIN或INNER MERGE JOIN提示问题会产生以下错误。

由于此查询中定义的提示，查询处理器无法生成查询计划。在不指定任何提示且不使用 SET FORCEPLAN 的情况下重新提交查询。

我找到了一个允许使用散列或合并连接的解决方法——将变量包装在一个聚合中。生成的计划成本显着降低（19.2025 对比 0.261987）

DECLARE @S2 VARCHAR(1) = '';

WITH T
     AS (SELECT name + (SELECT MAX(@S2)) AS name2,
                *
         FROM   spt_values)
SELECT *
FROM   T T1
       INNER JOIN T T2
         ON T1.name2 = T2.name2; 

这种行为的原因是什么？有没有比我发现的更好的解决方法？（也许不需要额外的执行计划分支）

对于以下架构和示例数据

CREATE TABLE T
  (
     A INT NULL,
     B INT NOT NULL IDENTITY,
     C CHAR(8000) NULL,
     UNIQUE CLUSTERED (A, B)
  )

INSERT INTO T
            (A)
SELECT NULLIF(( ( ROW_NUMBER() OVER (ORDER BY @@SPID) - 1 ) / 1003 ), 0)
FROM   master..spt_values 

应用程序正在以 1,000 行块的聚集索引顺序处理此表中的行。

从以下查询中检索前 1,000 行。

SELECT TOP 1000 *
FROM   T
ORDER  BY A, B 

该组的最后一行如下

+------+------+
|  A   |  B   |
+------+------+
| NULL | 1000 |
+------+------+

有没有办法编写一个查询，只寻找该复合索引键，然后跟随它来检索下一个 1000 行的块？

/*Pseudo Syntax*/
SELECT TOP 1000 *
FROM   T
WHERE (A, B) is_ordered_after (@A, @B)
ORDER  BY A, B 

到目前为止，我设法获得的最低读取次数是 1020，但查询似乎太复杂了。有没有更简单的相同或更高效率的方法？也许一个设法在一个范围内完成所有工作的人寻求？

DECLARE @A INT = NULL, @B INT = 1000

;WITH UnProcessed
     AS (SELECT *
         FROM   T
         WHERE  ( EXISTS(SELECT A
                         INTERSECT
                         SELECT @A)
                  AND B > @B )
         UNION ALL
         SELECT *
         FROM   T
         WHERE @A IS NULL AND A IS NOT NULL
         UNION ALL
         SELECT *
         FROM   T
         WHERE A > @A        
         )
SELECT TOP 1000 *
FROM   UnProcessed
ORDER  BY A,
          B 

FWIW：如果创建列A并NOT NULL使用标记值-1代替，等效的执行计划当然看起来更简单

但是计划中的单次搜索运算符仍然执行两次搜索，而不是将其折叠到一个连续的范围内，并且逻辑读取几乎相同，所以我怀疑这可能和它会得到的一样好？

给定以下组件

DECLARE @D DATE = '2013-10-13'
DECLARE @T TIME(7) = '23:59:59.9999999'

将它们结合起来以产生DATETIME2(7)具有价值的结果的最佳方法是什么'2013-10-13 23:59:59.9999999'？

下面列出了一些不起作用的东西。

SELECT @D + @T 

操作数数据类型日期对于加法运算符无效。

SELECT CAST(@D AS DATETIME2(7)) + @T 

操作数数据类型 datetime2 对 add 运算符无效。

SELECT DATEADD(NANOSECOND,DATEDIFF(NANOSECOND,CAST('00:00:00.0000000' AS TIME),@T),@D)

datediff 函数导致溢出。分隔两个日期/时间实例的日期部分的数量太大。尝试将 datediff 与不太精确的日期部分一起使用。

* 在 Azure SQL 数据库和 SQL Server 2016 中，使用DATEDIFF_BIG.

SELECT CAST(@D AS DATETIME) + @T 

数据类型 datetime 和 time 在 add 运算符中不兼容。

SELECT CAST(@D AS DATETIME) + CAST(@T AS DATETIME)

返回结果但丢失精度2013-10-13 23:59:59.997

我在这里的评论中遇到了这个难题

CREATE TABLE r (b INT);

SELECT 1 FROM r HAVING 1=1;

SQL Server和PostgreSQL返回 1 行。

MySQL和Oracle返回零行。

哪个是对的？或者两者同样有效？

从此处执行查询以将死锁事件拉出默认扩展事件会话

SELECT CAST (
    REPLACE (
        REPLACE (
            XEventData.XEvent.value ('(data/value)[1]', 'varchar(max)'),
            '<victim-list>', '<deadlock><victim-list>'),
        '<process-list>', '</victim-list><process-list>')
    AS XML) AS DeadlockGraph
FROM (SELECT CAST (target_data AS XML) AS TargetData
    FROM sys.dm_xe_session_targets st
    JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
    WHERE [name] = 'system_health') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
    WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report';

在我的机器上完成大约需要 20 分钟。报告的统计数据是

Table 'Worktable'. Scan count 0, logical reads 68121, physical reads 0, read-ahead reads 0, 
         lob logical reads 25674576, lob physical reads 0, lob read-ahead reads 4332386.

 SQL Server Execution Times:
   CPU time = 1241269 ms,  elapsed time = 1244082 ms.

慢计划 XML

如果我删除该WHERE子句，它将在不到一秒的时间内完成，返回 3,782 行。

同样，如果我添加OPTION (MAXDOP 1)到原始查询中也可以加快速度，现在统计信息显示大量更少的 lob 读取。

Table 'Worktable'. Scan count 0, logical reads 15, physical reads 0, read-ahead reads 0,
                lob logical reads 6767, lob physical reads 0, lob read-ahead reads 6076.

 SQL Server Execution Times:
   CPU time = 639 ms,  elapsed time = 693 ms.

更快的计划 XML

所以我的问题是

谁能解释发生了什么？为什么最初的计划如此灾难性地更糟，有没有可靠的方法来避免这个问题？

添加：

我还发现更改查询以INNER HASH JOIN在一定程度上改善事情（但仍然需要> 3分钟），因为 DMV 结果是如此之小，我怀疑 Join 类型本身是负责的，并假设其他东西必须改变。统计数据

Table 'Worktable'. Scan count 0, logical reads 30294, physical reads 0, read-ahead reads 0, 
          lob logical reads 10741863, lob physical reads 0, lob read-ahead reads 4361042.

 SQL Server Execution Times:
   CPU time = 200914 ms,  elapsed time = 203614 ms.

（和计划）

在填充了扩展事件环形缓冲区（DATALENGTH其中XML4,880,045 字节，其中包含 1,448 个事件。）并测试了原始查询的缩减版本（有和没有MAXDOP提示）。

SELECT COUNT(*)
FROM   (SELECT CAST (target_data AS XML) AS TargetData
        FROM   sys.dm_xe_session_targets st
               JOIN sys.dm_xe_sessions s
                 ON s.address = st.event_session_address
        WHERE  [name] = 'system_health') AS Data
       CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
WHERE  XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

SELECT*
FROM   sys.dm_db_task_space_usage
WHERE  session_id = @@SPID 

给出了以下结果

+-------------------------------------+------+----------+
|                                     | Fast |   Slow   |
+-------------------------------------+------+----------+
| internal_objects_alloc_page_count   |  616 |  1761272 |
| internal_objects_dealloc_page_count |  616 |  1761272 |
| elapsed time (ms)                   |  428 |   398481 |
| lob logical reads                   | 8390 | 12784196 |
+-------------------------------------+------+----------+

616tempdb 分配与显示页面已分配和释放的更快的分配有明显差异。这与将 XML 放入变量时使用的页面数量相同。

对于慢速计划，这些页面分配计数达到数百万。在查询运行时进行轮询dm_db_task_space_usage显示，它似乎在不断地分配和释放页面，tempdb任何时候都分配了 1,800 到 3,000 个页面。

这是我定期遇到的一个问题，但尚未找到好的解决方案。

假设如下表结构

CREATE TABLE T
(
A INT PRIMARY KEY,
B CHAR(1000) NULL,
C CHAR(1000) NULL
)

并且要求是确定任一可空列B或C实际上是否包含任何NULL值（如果是，则包含哪些值）。

还假设该表包含数百万行（并且没有可用的列统计信息可以查看，因为我对此类查询的更通用的解决方案感兴趣）。

我可以想到几种方法来解决这个问题，但都有弱点。

两个单独的EXISTS陈述。这将具有允许查询在找到 a 时尽早停止扫描的优点NULL。但是如果两列实际上都包含 no NULL，那么将产生两次完整扫描。

单一聚合查询

SELECT 
    MAX(CASE WHEN B IS NULL THEN 1 ELSE 0 END) AS B,
    MAX(CASE WHEN C IS NULL THEN 1 ELSE 0 END) AS C
FROM T

这可以同时处理两列，因此最坏的情况是一次完整扫描。缺点是即使NULL很早就在查询中遇到两列中的 a ，最终仍会扫描整个表的其余部分。

用户变量

我可以想到第三种方法

BEGIN TRY
DECLARE @B INT, @C INT, @D INT

SELECT 
    @B = CASE WHEN B IS NULL THEN 1 ELSE @B END,
    @C = CASE WHEN C IS NULL THEN 1 ELSE @C END,
    /*Divide by zero error if both @B and @C are 1.
    Might happen next row as no guarantee of order of
    assignments*/
    @D = 1 / (2 - (@B + @C))
FROM T  
OPTION (MAXDOP 1)       
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 8134 /*Divide by zero*/
    BEGIN
    SELECT 'B,C both contain NULLs'
    RETURN;
    END
ELSE
    RETURN;
END CATCH

SELECT ISNULL(@B,0),
       ISNULL(@C,0)

但这不适用于生产代码，因为未定义聚合连接查询的正确行为。无论如何，通过抛出错误来终止扫描是一个非常可怕的解决方案。

有没有结合上述方法的优势的另一种选择？

编辑

只是用我到目前为止提交的答案的读取结果来更新它（使用@ypercube的测试数据）

+----------+------------+------+---------+----------+----------------------+----------+------------------+
|          | 2 * EXISTS | CASE | Kejser  |  Kejser  |        Kejser        | ypercube |       8kb        |
+----------+------------+------+---------+----------+----------------------+----------+------------------+
|          |            |      |         | MAXDOP 1 | HASH GROUP, MAXDOP 1 |          |                  |
| No Nulls |      15208 | 7604 |    8343 | 7604     | 7604                 |    15208 | 8346 (8343+3)    |
| One Null |       7613 | 7604 |    8343 | 7604     | 7604                 |     7620 | 7630 (25+7602+3) |
| Two Null |         23 | 7604 |    8343 | 7604     | 7604                 |       30 | 30 (18+12)       |
+----------+------------+------+---------+----------+----------------------+----------+------------------+

对于@Thomas 的回答，我改为TOP 3可能TOP 2允许它提前退出。默认情况下，我为该答案制定了并行计划，因此还尝试了MAXDOP 1提示，以使读取次数与其他计划更具可比性。我对结果感到有些惊讶，因为在我之前的测试中，我在没有阅读整个表格的情况下就看到了查询短路。

我的短路测试数据计划如下

ypercube 的数据计划是

因此，它在计划中添加了一个阻塞排序运算符。我也尝试了HASH GROUP提示，但最终还是读取了所有行

因此，关键似乎是让hash match (flow distinct)运营商允许该计划短路，因为其他替代方案无论如何都会阻塞并消耗所有行。我认为没有暗示可以特别强制执行此操作，但显然“通常，优化器会选择一个 Flow Distinct，它确定所需的输出行数少于输入集中的不同值。” .

@ypercube 的数据在每列中只有 1 行带有NULL值（表基数 = 30300），进出运算符的估计行都是1. 通过使谓词对优化器更加不透明，它使用 Flow Distinct 运算符生成了一个计划。

SELECT TOP 2 *
FROM (SELECT DISTINCT 
        CASE WHEN b IS NULL THEN NULL ELSE 'foo' END AS b
      , CASE WHEN c IS NULL THEN NULL ELSE 'bar' END AS c
  FROM test T 
  WHERE LEFT(b,1) + LEFT(c,1) IS NULL
) AS DT 

编辑 2

我发生的最后一个调整是，如果上面的查询遇到的第一行在NULL列B和C. 它将继续扫描而不是立即退出。避免这种情况的一种方法是在扫描行时对其进行反透视。所以我对Thomas Kejser 的回答的最后修改如下

SELECT DISTINCT TOP 2 NullExists
FROM test T 
CROSS APPLY (VALUES(CASE WHEN b IS NULL THEN 'b' END),
                   (CASE WHEN c IS NULL THEN 'c' END)) V(NullExists)
WHERE NullExists IS NOT NULL

谓词可能会更好，WHERE (b IS NULL OR c IS NULL) AND NullExists IS NOT NULL但与之前的测试数据相反，一个人没有给我一个带有 Flow Distinct 的计划，而那NullExists IS NOT NULL个人给了我（下面的计划）。

这份 2007 年的白皮书比较了单个 select/insert/delete/update 和 range select 语句在组织为聚集索引的表上与在与 CI 相同的键列上具有非聚集索引的堆组织的表上的性能桌子。

通常，聚集索引选项在测试中表现更好，因为只需维护一个结构并且不需要书签查找。

本文未涵盖的一个可能有趣的案例是堆上的非聚集索引与聚集索引上的非聚集索引之间的比较。在那种情况下，我预计堆甚至可能会表现得更好，因为一旦在 NCI 叶级 SQL Server 有一个 RID 可以直接遵循，而不需要遍历聚集索引。

有没有人知道在这个领域已经进行了类似的正式测试，如果有，结果如何？

根据这篇（相当古老的）文章，ODBC 转义序列背后的原理是

ODBC 驱动程序读取转义序列并将其转换为特定于 DBMS 的语法，然后再将查询发送到数据库

但是，如果我尝试以下

数据库服务器

CREATE PROCEDURE foo_bar
AS
BEGIN
  SELECT  {fn concat ('foo', 'bar')};
END

我的SQL

DELIMITER $$

CREATE PROCEDURE foo_bar()
BEGIN
  SELECT  {fn concat ('foo', 'bar')};
END

转义序列在对象定义中保持未翻译状态，调用例程工作正常。这向我表明 RDBMS 本身可以理解语法而不是依赖翻译，因此将独立于连接方法工作。这种理解是否正确？如果正确，这是否也适用于 Oracle？