Geoff Patterson's questions

以下查询SUM对包含 的列存储表执行窗口化1500 total rows，其中每个表的值为 0 或 1，并且它溢出INT数据类型。为什么会这样？

SELECT a, p, s, v, m, n,
    SUM(CASE WHEN n IS NULL THEN 0 ELSE 1 END)
        OVER (PARTITION BY s, v, a ORDER BY p) AS lastNonNullPartition
FROM (
    SELECT a, p, s, v, m, n,
        RANK() OVER (PARTITION BY v, s, a, p ORDER BY m) AS rank
    FROM #t /* A columnstore table with 1,500 rows */
)  x
WHERE x.rank = 1
--Msg 8115, Level 16, State 2, Line 1521
--Arithmetic overflow error converting expression to data type int.

完整脚本

请参阅此文件以获取完整的复制脚本。

查询计划

这是一个带注释的估计查询计划（粘贴计划的完整 XML）。

成功执行的类似查询

如果进行以下任何修改，则不会出现错误：

• 无论并行性的成本阈值如何，都使用跟踪标志8649来首选并行计划
• 使用跟踪标志9453禁用批处理模式
• 使用COUNT聚合函数而不是SUM函数
• 删除WHERE x.rank = 1谓词

例如，此查询成功执行：

SELECT a, p, s, v, m, n,
    SUM(CASE WHEN n IS NULL THEN 0 ELSE 1 END)
        OVER (PARTITION BY s, v, a ORDER BY p) AS lastNonNullPartition
FROM (
    SELECT a, p, s, v, m, n,
        RANK() OVER (PARTITION BY v, s, a, p ORDER BY m) AS rank
    FROM #t /* A columnstore table with 1,500 rows */
)  x
WHERE x.rank = 1
OPTION (QUERYTRACEON 9453/* Disable batch mode */) 

考虑 SQL Server 2014 中的以下查询计划：

在查询计划中，自联接ar.fId = ar.fId产生 1 行的估计值。然而，这是一个逻辑上不一致的估计：ar有20,608行和只有一个不同的值fId（准确反映在统计数据中）。因此，此连接会生成行 (rows) 的完整叉积~424MM，从而导致查询运行数小时。

我很难理解为什么 SQL Server 会得出一个很容易证明与统计数据不一致的估计值。有任何想法吗？

初步调查和其他细节

根据 Paul 在此处的回答，似乎用于估计连接基数的 SQL 2012 和 SQL 2014 启发式方法应该可以轻松处理需要比较两个相同直方图的情况。

我从跟踪标志 2363 的输出开始，但没那么容易理解。以下代码片段是否意味着 SQL Server 正在比较fId和bId的直方图以估计仅使用的联接的选择性fId？如果是这样，那显然是不正确的。还是我误读了跟踪标志输出？

Plan for computation:
  CSelCalcExpressionComparedToExpression( QCOL: [ar].fId x_cmpEq QCOL: [ar].fId )
Loaded histogram for column QCOL: [ar].bId from stats with id 3
Loaded histogram for column QCOL: [ar].fId from stats with id 1
Selectivity: 0

请注意，我已经提出了几种解决方法，它们包含在完整的重现脚本中，并将此查询缩短到毫秒。这个问题的重点是了解行为，如何在以后的查询中避免它，并确定它是否是应该向 Microsoft 提交的错误。

这是完整的重现脚本，这是跟踪标志 2363 的完整输出，这是查询和表定义，以防您想在不打开完整脚本的情况下快速查看它们：

WITH cte AS (
    SELECT ar.fId, 
        ar.bId,
        MIN(CONVERT(INT, ar.isT)) AS isT,
        MAX(CONVERT(INT, tcr.isS)) AS isS
    FROM  #SQL2014MinMaxAggregateCardinalityBug_ar ar 
    LEFT OUTER JOIN #SQL2014MinMaxAggregateCardinalityBug_tcr tcr
        ON tcr.rId = 508
        AND tcr.fId = ar.fId
        AND tcr.bId = ar.bId
    GROUP BY ar.fId, ar.bId
)
SELECT s.fId, s.bId, s.isS, t.isS
FROM cte s 
JOIN cte t 
    ON t.fId = s.fId 
    AND t.isT = 1

CREATE TABLE #SQL2014MinMaxAggregateCardinalityBug_ar (
    fId INT NOT NULL,
    bId INT NOT NULL,
    isT BIT NOT NULL
    PRIMARY KEY (fId, bId)
)

CREATE TABLE #SQL2014MinMaxAggregateCardinalityBug_tcr (
    rId INT NOT NULL,
    fId INT NOT NULL,
    bId INT NOT NULL,
    isS BIT NOT NULL
    PRIMARY KEY (rId, fId, bId, isS)
)

在下面的查询计划片段中，很明显Concatenation运算符的行估计应该是~4.3 billion rows，或者它的两个输入的行估计的总和。

但是，生成了 的估计值~238 million rows，导致次优Sort/Stream Aggregate策略将数百 GB 的数据溢出到 tempdb。在这种情况下，逻辑上一致的估计会产生Hash Aggregate，消除溢出，并显着提高查询性能。

这是 SQL Server 2014 中的错误吗？在任何有效情况下，低于输入值的估计值是合理的吗？可能有哪些解决方法？

这是完整的查询计划（匿名）。我没有系统管理员访问此服务器的权限，无法提供来自QUERYTRACEON 2363或类似跟踪标志的输出，但如果有用的话，我可以从管理员那里获得这些输出。

该数据库的兼容级别为 120，因此使用新的 SQL Server 2014 基数估算器。

每次加载数据时都会手动更新统计信息。鉴于数据量，我们目前使用默认采样率。较高的采样率（或FULLSCAN）可能会产生影响。

我们有几个数据库，其中创建和删除了大量表。据我们所知，SQL Server 不会对系统基表进行任何内部维护，这意味着它们会随着时间的推移变得非常碎片化并且体积膨胀。这会给缓冲池带来不必要的压力，也会对计算数据库中所有表的大小等操作的性能产生负面影响。

有没有人建议尽量减少这些核心内部表上的碎片？一个明显的解决方案可以避免创建如此多的表（或在 tempdb 中创建所有临时表），但出于这个问题的目的，假设应用程序没有这种灵活性。

编辑：进一步的研究表明这个悬而未决的问题，它看起来密切相关，并表明某种形式的手动维护ALTER INDEX...REORGANIZE可能是一种选择。

初步研究

有关这些表的元数据可以在以下位置查看sys.dm_db_partition_stats：

-- The system base table that contains one row for every column in the system
SELECT row_count,
    (reserved_page_count * 8 * 1024.0) / row_count AS bytes_per_row, 
    reserved_page_count/128. AS space_mb
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('sys.syscolpars')
    AND index_id = 1
-- row_count:       15,600,859
-- bytes_per_row:   278.08
-- space_mb:        4,136

但是，sys.dm_db_index_physical_stats似乎不支持查看这些表的碎片：

-- No fragmentation data is returned by sys.dm_db_index_physical_stats
SELECT *
FROM sys.dm_db_index_physical_stats(
    DB_ID(),
    OBJECT_ID('sys.syscolpars'),
    NULL,
    NULL,
    'DETAILED'
)

Ola Hallengren 的脚本还包含一个考虑对is_ms_shipped = 1对象进行碎片整理的参数，但即使启用了此参数，该过程也会默默地忽略系统基表。Ola 澄清说这是预期的行为；仅msdb.dbo.backupset考虑 ms_shipped（例如）的用户表（不是系统表）。

-- Returns code 0 (successful), but does not do any work for system base tables.
-- Instead of the expected commands to update statistics and reorganize indexes,
-- no commands are generated. The script seems to assume the target tables will
-- appear in sys.tables, but this does not appear to be a valid assumption for
-- system tables like sys.sysrowsets or sys.syscolpars.
DECLARE @result int;
EXEC @result = IndexOptimize @Databases = 'Test',
        @FragmentationLow = 'INDEX_REORGANIZE',
        @FragmentationMedium = 'INDEX_REORGANIZE',
        @FragmentationHigh = 'INDEX_REORGANIZE',
        @PageCountLevel = 0,
        @UpdateStatistics = 'ALL',
        @Indexes = '%Test.sys.sysrowsets.%',
        -- Proc works properly if targeting a non-system table instead
        --@Indexes = '%Test.dbo.Numbers.%',
        @MSShippedObjects = 'Y',
        @Execute = 'N';
PRINT(@result);

额外要求的信息

我在检查系统表缓冲池使用情况下使用了 Aaron 查询的改编版本，结果发现缓冲池中有数十 GB 的系统表仅用于一个数据库，在某些情况下，大约 80% 的空间是可用空间.

-- Compute buffer pool usage by system table
SELECT OBJECT_NAME(p.object_id),
    COUNT(b.page_id) pages,
    SUM(b.free_space_in_bytes/8192.0) free_pages
FROM sys.dm_os_buffer_descriptors b
JOIN sys.allocation_units a
    ON a.allocation_unit_id = b.allocation_unit_id
JOIN sys.partitions p
    ON p.partition_id = a.container_id
    AND p.object_id < 1000 -- A loose proxy for system tables
WHERE b.database_id = DB_ID()
GROUP BY p.object_id
ORDER BY pages DESC

对于COUNT(DISTINCT)具有约 10 亿个不同值的 a，我得到一个查询计划，其散列聚合估计只有约 300 万行。

为什么会这样？SQL Server 2012 产生了一个很好的估计，那么这是 SQL Server 2014 中的一个错误，我应该在 Connect 上报告吗？

查询及差评

-- Actual rows: 1,011,719,166
-- SQL 2012 estimated rows: 1,079,130,000 (106% of actual)
-- SQL 2014 estimated rows: 2,980,240 (0.29% of actual)
SELECT COUNT(DISTINCT factCol5)
FROM BigFactTable
OPTION (RECOMPILE, QUERYTRACEON 9481) -- Include this line to use SQL 2012 CE

-- Stats for the factCol5 column show that there are ~1 billion distinct values
-- This is a good estimate, and it appears to be what the SQL 2012 CE uses
DBCC SHOW_STATISTICS (BigFactTable, _WA_Sys_00000005_24927208)
--All density   Average Length  Columns
--9.266754E-10  8               factCol5
SELECT 1 / 9.266754E-10
-- 1079126520.46229

查询计划

完整脚本

这是使用仅统计数据库的情况的完整重现。

到目前为止我试过的

我深入研究了相关列的统计数据，发现密度向量显示了大约 11 亿个不同的值。SQL Server 2012 使用此估计并生成一个好的计划。令人惊讶的是，SQL Server 2014 似乎忽略了统计数据提供的非常准确的估计，而是使用了一个低得多的估计。这会产生一个慢得多的计划，它不会保留几乎足够的内存并溢出到 tempdb。

我尝试了 trace flag 4199，但这并没有解决问题。最后，我尝试通过跟踪标志的组合深入了解优化器信息，如本文(3604, 8606, 8607, 8608, 8612)后半部分所示。但是，在最终输出树中出现之前，我无法看到任何解释错误估计的信息。

连接问题

根据这个问题的答案，我也将此作为问题提交到 Connect

我有一个带有字符串列和一个谓词的表，用于检查具有一定长度的行。在 SQL Server 2014 中，无论我检查的长度如何，我都看到了 1 行的估计值。这会产生非常糟糕的计划，因为实际上有数千甚至数百万行，并且 SQL Server 选择将此表放在嵌套循环的外侧。

SQL Server 2014 的基数估计为 1.0003 而 SQL Server 2012 估计为 31,622 行，是否有解释？有没有好的解决方法？

以下是该问题的简短再现：

-- Create a table with 1MM rows of dummy data
CREATE TABLE #customers (cust_nbr VARCHAR(10) NOT NULL)
GO

INSERT INTO #customers WITH (TABLOCK) (cust_nbr)
    SELECT TOP 1000000 
        CONVERT(VARCHAR(10),
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) AS cust_nbr
    FROM master..spt_values v1
    CROSS JOIN master..spt_values v2
GO

-- Looking for string of a certain length.
-- While both CEs yield fairly poor estimates, the 2012 CE is much
-- more conservative (higher estimate) and therefore much more likely
-- to yield an okay plan rather than a drastically understimated loop join.
-- 2012: 31,622 rows estimated, 900K rows actual
-- 2014: 1 row estimated, 900K rows actual
SELECT COUNT(*)
FROM #customers
WHERE LEN(cust_nbr) = 6
OPTION (QUERYTRACEON 9481) -- Optionally, use 2012 CE
GO

这是一个更完整的脚本，显示了其他测试

我还阅读了有关 SQL Server 2014 Cardinality Estimator 的白皮书，但没有找到任何可以澄清情况的内容。

我有一个查询在 SQL Server 2012 中运行 800 毫秒，在 SQL Server 2014 中需要大约170 秒。我认为我已将其缩小到对Row Count Spool运营商的基数估计不佳。我已经阅读了一些关于 spool 操作符的信息（例如，这里和这里），但仍然无法理解一些事情：

• 为什么这个查询需要一个Row Count Spool操作符？我认为正确性没有必要，那么它试图提供什么具体的优化呢？
• 为什么 SQL Server 估计连接到Row Count Spool运算符会删除所有行？
• 这是 SQL Server 2014 中的错误吗？如果是这样，我将在 Connect 中归档。但我想先有更深入的了解。

注意：我可以将查询重写为 aLEFT JOIN或向表中添加索引，以便在 SQL Server 2012 和 SQL Server 2014 中实现可接受的性能。所以这个问题更多的是关于深入了解这个特定的查询和计划，而不是关于如何以不同的方式表达查询。

慢查询

有关完整的测试脚本，请参阅此 Pastebin。这是我正在查看的特定测试查询：

-- Prune any existing customers from the set of potential new customers
-- This query is much slower than expected in SQL Server 2014 
SELECT *
FROM #potentialNewCustomers -- 10K rows
WHERE cust_nbr NOT IN (
    SELECT cust_nbr
    FROM #existingCustomers -- 1MM rows
)

SQL Server 2014：估计的查询计划

SQL Server 认为这Left Anti Semi Join会将Row Count Spool10,000 行过滤到 1 行。出于这个原因，它选择 aLOOP JOIN用于后续连接到#existingCustomers。

SQL Server 2014：实际的查询计划

正如预期的那样（除了 SQL Server 之外的所有人！），Row Count Spool没有删除任何行。因此，当 SQL Server 预计只循环一次时，我们循环了 10,000 次。

SQL Server 2012：估计的查询计划

使用 SQL Server 2012（或OPTION (QUERYTRACEON 9481)在 SQL Server 2014 中）时，Row Count Spool不会减少估计的行数并选择哈希连接，从而产生更好的计划。

LEFT JOIN 重写

作为参考，这是一种我可以重写查询的方法，以便在所有 SQL Server 2012、2014 和 2016 中实现良好的性能。但是，我仍然对上述查询的具体行为以及是否它感兴趣是新的 SQL Server 2014 基数估计器中的一个错误。

-- Re-writing with LEFT JOIN yields much better performance in 2012/2014/2016
SELECT n.*
FROM #potentialNewCustomers n
LEFT JOIN (SELECT 1 AS test, cust_nbr FROM #existingCustomers) c
    ON c.cust_nbr = n.cust_nbr
WHERE c.test IS NULL

为什么第二个INSERT语句比第一个慢 ~5 倍？

从生成的日志数据量来看，我认为第二个不符合最小日志记录的条件。但是，数据加载性能指南中的文档指出这两个插入应该能够被最少地记录。因此，如果最小日志记录是关键的性能差异，为什么第二个查询不符合最小日志记录的条件？可以做些什么来改善这种情况？

查询 #1：使用 INSERT...WITH (TABLOCK) 插入 5MM 行

考虑以下查询，它将 5MM 行插入到堆中。此查询在 中执行1 second并生成64MB所报告的事务日志数据sys.dm_tran_database_transactions。

CREATE TABLE dbo.minimalLoggingTest (n INT NOT NULL)
GO
INSERT INTO dbo.minimalLoggingTest WITH (TABLOCK) (n)
SELECT n
-- Any table/view/sub-query that correctly estimates that it will generate 5MM rows
FROM dbo.fiveMillionNumbers
-- Provides greater consistency on my laptop, where other processes are running
OPTION (MAXDOP 1)
GO

查询 #2：插入相同的数据，但 SQL 低估了行数

现在考虑这个非常相似的查询，它对完全相同的数据进行操作，但恰好是从SELECT基数估计值太低的表（或在我的实际生产案例中具有许多连接的复杂语句）中提取的。此查询在事务日志数据中执行5.5 seconds并生成461MB。

CREATE TABLE dbo.minimalLoggingTest (n INT NOT NULL)
GO
INSERT INTO dbo.minimalLoggingTest WITH (TABLOCK) (n)
SELECT n
-- Any table/view/sub-query that produces 5MM rows but SQL estimates just 1000 rows
FROM dbo.fiveMillionNumbersBadEstimate
-- Provides greater consistency on my laptop, where other processes are running
OPTION (MAXDOP 1)
GO

完整脚本

请参阅此 Pastebin以获取用于生成测试数据并执行其中任一场景的全套脚本。请注意，您必须使用SIMPLE 恢复模型中的数据库。

商业背景

我们半频繁地移动数百万行数据，让这些操作尽可能高效非常重要，无论是在执行时间还是磁盘 I/O 负载方面。我们最初的印象是创建堆表并使用INSERT...WITH (TABLOCK)它是一个很好的方法，但现在我们变得不那么自信了，因为我们在实际生产场景中观察到了上面展示的情况（尽管有更复杂的查询，而不是此处为简化版）。

在我们不一定要确保已预先排序的两个表中查找不同的行时，使用 aFULL OUTER JOIN而不是 a是个好主意UNION吗？这种方法有什么缺点吗？如果它始终更快，为什么查询优化器不为 UNION 选择FULL OUTER JOIN将使用的相同计划？

通过将 a 重写UNION为FULL OUTER JOIN. AUNION似乎是编写逻辑的更直观的方式，但在探索这两个选项后，我发现它在FULL OUTER JOIN内存和 CPU 使用方面都更有效。

如果您想运行我们的生产查询的简化和匿名版本，请参阅以下脚本：

设置脚本

-- Create a 500K row table
SELECT TOP 500000 ROW_NUMBER() OVER (ORDER BY NEWID()) AS id, v1.number % 5 AS val
INTO #t1
FROM master..spt_values v1
CROSS JOIN master..spt_values v2

-- Create a 5MM row table that will match some, but not all, of the 500K row table
SELECT TOP 5000000 ROW_NUMBER() OVER (ORDER BY NEWID()) AS id, v1.number % 5 AS val
INTO #t2
FROM master..spt_values v1
CROSS JOIN master..spt_values v2

-- Optionally, key both tables to see the impact it has on query plans and performance
-- Both queries end up with essentially the same plan and performance in this case
-- So that means that at least there is not a downside to using the FULL OUTER JOIN when the data is sorted
--ALTER TABLE #t1
--ADD UNIQUE CLUSTERED (id)
--ALTER TABLE #t2
--ADD UNIQUE CLUSTERED (id)

全外连接

选择两个表中较小的FULL OUTER JOIN一个作为哈希连接的构建端，这意味着内存使用量与较小表的大小（500K 行）成正比。

-- CPU time = 3058 ms,  elapsed time = 783 ms.
-- MaxUsedMemory: 29016 KB
-- Table '#t1'. Scan count 5, logical reads 1301, physical reads 0
-- Table '#t2'. Scan count 5, logical reads 12989, physical reads 0
SELECT COUNT(*), AVG(id), AVG(val)
FROM (
    SELECT COALESCE(t1.id, t2.id) AS id, COALESCE(t1.val, t2.val) AS val
    FROM #t1 t1
    FULL OUTER JOIN #t2 t2
        ON t2.id = t1.id
        AND t2.val = t1.val
) x
GO

联盟

为整个数据集上的UNION哈希聚合构建一个哈希表，这意味着内存使用与不同行的总数成正比（在这种情况下为 5.4MM 行；通常，至少是较大的行数中的行数）两张表）。内存使用量是 10 倍以上FULL OUTER JOIN，而且 CPU 时间和运行时间也更慢。如果我将其扩展到哈希聚合无法容纳单个查询的内存授权的程度，性能差异将变得巨大（就像在我们的大型生产查询中一样）。

-- CPU time = 4651 ms,  elapsed time = 1188 ms.
-- MaxUsedMemory: 301600 KB
-- Table '#t1'. Scan count 5, logical reads 1301, physical reads 0
-- Table '#t2'. Scan count 5, logical reads 12989, physical reads 0
SELECT COUNT(*), AVG(id), AVG(val)
FROM (
    SELECT t1.id, t1.val
    FROM #t1 t1
    UNION 
    SELECT t2.id, t2.val
    FROM #t2 t2
) x

维护全文索引应考虑哪些准则？

我应该重建还是重组全文目录（参见BOL）？什么是合理的维护节奏？哪些启发式方法（类似于 10% 和 30% 碎片阈值）可用于确定何时需要维护？

（下面的所有内容都只是详细说明问题的额外信息，并显示了我到目前为止的想法。）

额外信息：我的初步研究

有很多关于 b-tree 索引维护的资源（例如，这个问题、Ola Hallengren 的脚本，以及来自其他站点的大量关于该主题的博客文章）。但是，我发现这些资源都没有提供维护全文索引的建议或脚本。

有Microsoft 文档提到，对基表的 b 树索引进行碎片整理，然后对全文目录执行 REORGANIZE 可能会提高性能，但它没有涉及任何更具体的建议。

我也发现了这个问题，但它主要关注更改跟踪（对基础表的数据更新如何传播到全文索引中），而不是可以最大限度地提高索引效率的定期维护类型。

额外信息：基本性能测试

此SQL Fiddle包含可用于创建具有更改跟踪的全文索引的代码，AUTO并在修改表中的数据时检查索引的大小和查询性能。当我在生产数据的副本上运行脚本的逻辑时（与小提琴中的人工制造数据相反），以下是我在每个数据修改步骤后看到的结果摘要：

尽管此脚本中的更新语句相当做作，但这些数据似乎表明定期维护有很多收获。

额外信息：初步想法

我正在考虑创建一个每晚或每周的任务。似乎此任务可以执行 REBUILD 或 REORGANIZE。

因为全文索引可能非常大（数千万或数亿行），所以我希望能够检测到目录中的索引何时足够碎片化，以至于需要进行 REBUILD/REORGANIZE。我有点不清楚启发式方法可能对此有意义。

我在一小时内看到以下 tempdb I/O 行为：

机器上执行的多个 DW 工作负载会产生大量磁盘 I/O，其中一些无法容纳分配给 SQL 的约 280GB 内存。一个有趣的方面是，如此多的 I/O 都集中在旋转磁盘驱动器 (E) 上，而不是处理 I/O 效率更高的固态驱动器（F 和 G）上。

我们已将 F 和 G 驱动器上的全部 300GB（总共 600GB）预先分配给 tempdb（使用 12 个文件），并且我们已将 1.3TB 预​​先分配给 E 驱动器上的 tempdb（当前为 1 个文件）。上面的 I/O 数据表明 tempdb 的使用是根据文件的当前大小分布在文件中的。我找不到关于此的文档，但我还运行了如下查询以进一步调查：

-- While running this query, writes to tempdb are distributed to E/F/G drives
-- in proportion to their current size.  This was shown by both
-- sys.dm_io_virtual_file_stats and the space used on the tempdb files before and after
SELECT TOP 100000000 *
INTO #temp
FROM [A_Really_Big_Table]

理想的行为是 F 和 G 被独占使用，除非它们都已满，在这种情况下，旋转磁盘驱动器应提供额外的 tempdb 空间，以便可以在不耗尽 tempdb 空间的情况下处理偶尔非常大的工作负载。

我们是否在正确的轨道上观察到 tempdb 的使用是根据文件的当前大小分布在文件之间的？看到这种类型的分布而不是看到每个文件的平等使用有点令人惊讶（这可能是设置此硬件的人的假设，并决定只将一个文件分配给旋转磁盘上的 tempdb）。

根据 Paul White 对这个问题的回应，我们正在考虑以下方法：

• 收缩旋转磁盘上的 tempdb 文件。根据我们的初步测试，这应该会将当前的工作分配更多地转移到固态驱动器上
• 配置固态 tempdb 文件以预分配它们的空间（正如我们现在所做的那样）
• 将旋转磁盘 tempdb 文件配置为从不分配开始。确保即时文件初始化已打开。Tempdb 将仅在需要时在旋转磁盘上增长（可能最多每周一次）。
• 创建一个维护计划，在高峰负载后缩小旋转磁盘上的 tempdb 文件，使分布重新有利于固态 tempdb 文件。

这看起来合理吗？是否有任何替代方法或潜在问题需要考虑？我们显然会尽可能地测试该方法，但无法在完全等效的测试硬件上进行测试。

提前为非常详细的问题道歉。我已经包含查询以生成完整的数据集以重现问题，并且我在 32 核机器上运行 SQL Server 2012。但是，我不认为这特定于 SQL Server 2012，并且我已为此特定示例强制 MAXDOP 为 10。

我有两个使用相同分区方案分区的表。当在用于分区的列上将它们连接在一起时，我注意到 SQL Server 无法像预期的那样优化并行合并连接，因此选择使用 HASH JOIN。在这种特殊情况下，我可以通过根据分区函数将查询拆分为 10 个不相交的范围并在 SSMS 中同时运行每个查询来手动模拟更优化的并行 MERGE JOIN。使用 WAITFOR 在完全相同的时间运行它们，结果是所有查询在原始并行 HASH JOIN 使用的总时间的约 40% 内完成。

在等效分区表的情况下，有什么方法可以让 SQL Server 自行进行这种优化？我知道 SQL Server 通常可能会产生大量开销以使 MERGE JOIN 并行，但在这种情况下似乎有一种非常自然的分片方法，开销最小。也许这只是优化器还不够聪明识别的特殊情况？

这是设置简化数据集以重现此问题的 SQL：

/* Create the first test data table */
CREATE TABLE test_transaction_properties 
    ( transactionID INT NOT NULL IDENTITY(1,1)
    , prop1 INT NULL
    , prop2 FLOAT NULL
    )

/* Populate table with pseudo-random data (the specific data doesn't matter too much for this example) */
;WITH E1(N) AS (
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
    UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)
, E2(N) AS (SELECT 1 FROM E1 a CROSS JOIN E1 b)
, E4(N) AS (SELECT 1 FROM E2 a CROSS JOIN E2 b)
, E8(N) AS (SELECT 1 FROM E4 a CROSS JOIN E4 b)
INSERT INTO test_transaction_properties WITH (TABLOCK) (prop1, prop2)
SELECT TOP 10000000 (ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 5) + 1 AS prop1
                , ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) * rand() AS prop2
FROM E8

/* Create the second test data table */
CREATE TABLE test_transaction_item_detail
    ( transactionID INT NOT NULL
    , productID INT NOT NULL
    , sales FLOAT NULL
    , units INT NULL
    )

 /* Populate the second table such that each transaction has one or more items
     (again, the specific data doesn't matter too much for this example) */
INSERT INTO test_transaction_item_detail WITH (TABLOCK) (transactionID, productID, sales, units)
SELECT t.transactionID, p.productID, 100 AS sales, 1 AS units
FROM test_transaction_properties t
JOIN (
    SELECT 1 as productRank, 1 as productId
    UNION ALL SELECT 2 as productRank, 12 as productId
    UNION ALL SELECT 3 as productRank, 123 as productId
    UNION ALL SELECT 4 as productRank, 1234 as productId
    UNION ALL SELECT 5 as productRank, 12345 as productId
) p
    ON p.productRank <= t.prop1

/* Divides the transactions evenly into 10 partitions */
CREATE PARTITION FUNCTION [pf_test_transactionId] (INT)
AS RANGE RIGHT
FOR VALUES
(1,1000001,2000001,3000001,4000001,5000001,6000001,7000001,8000001,9000001)

CREATE PARTITION SCHEME [ps_test_transactionId]
AS PARTITION [pf_test_transactionId]
ALL TO ( [PRIMARY] )

/* Apply the same partition scheme to both test data tables */
ALTER TABLE test_transaction_properties
ADD CONSTRAINT PK_test_transaction_properties
PRIMARY KEY (transactionID)
ON ps_test_transactionId (transactionID)

ALTER TABLE test_transaction_item_detail
ADD CONSTRAINT PK_test_transaction_item_detail
PRIMARY KEY (transactionID, productID)
ON ps_test_transactionId (transactionID)

现在我们终于准备好重现次优查询了！

/* This query produces a HASH JOIN using 20 threads without the MAXDOP hint,
    and the same behavior holds in that case.
    For simplicity here, I have limited it to 10 threads. */
SELECT COUNT(*)
FROM test_transaction_item_detail i
JOIN test_transaction_properties t
    ON t.transactionID = i.transactionID
OPTION (MAXDOP 10)

但是，使用单个线程来处理每个分区（下面的第一个分区示例）将导致更有效的计划。我通过在完全相同的时刻对 10 个分区中的每一个运行如下查询来测试这一点，所有 10 个分区都在 1 秒多的时间内完成：

SELECT COUNT(*)
FROM test_transaction_item_detail i
INNER MERGE JOIN test_transaction_properties t
    ON t.transactionID = i.transactionID
WHERE t.transactionID BETWEEN 1 AND 1000000
OPTION (MAXDOP 1)