为什么我的SELECT DISTINCT TOP N查询会扫描整个表？

我遇到了一些SELECT DISTINCT TOP N查询，这些查询似乎没有被SQL Server查询优化器优化。让我们从一个简单的例子开始：一个带有两个交替值的百万行表。我将使用GetNums函数生成数据：

DROP TABLE IF EXISTS X_2_DISTINCT_VALUES;

CREATE TABLE X_2_DISTINCT_VALUES (PK INT IDENTITY (1, 1), VAL INT NOT NULL);

INSERT INTO X_2_DISTINCT_VALUES WITH (TABLOCK) (VAL)
SELECT N % 2
FROM dbo.GetNums(1000000);

UPDATE STATISTICS X_2_DISTINCT_VALUES WITH FULLSCAN;

对于以下查询：

SELECT DISTINCT TOP 2 VAL
FROM X_2_DISTINCT_VALUES
OPTION (MAXDOP 1);

SQL Server仅通过扫描表的第一个数据页就可以找到两个不同的值，但是它会扫描所有数据。 SQL Server为什么不扫描直到找到所需数量的不同值？

对于此问题，请使用以下测试数据，该数据包含1000万行，并在块中生成10个不同值：

DROP TABLE IF EXISTS X_10_DISTINCT_HEAP;

CREATE TABLE X_10_DISTINCT_HEAP (VAL VARCHAR(10) NOT NULL);

INSERT INTO X_10_DISTINCT_HEAP WITH (TABLOCK)
SELECT REPLICATE(CHAR(65 + (N / 100000 ) % 10 ), 10)
FROM dbo.GetNums(10000000);

UPDATE STATISTICS X_10_DISTINCT_HEAP WITH FULLSCAN;

带有聚集索引的表的答案也可以接受：

DROP TABLE IF EXISTS X_10_DISTINCT_CI;

CREATE TABLE X_10_DISTINCT_CI (PK INT IDENTITY (1, 1), VAL VARCHAR(10) NOT NULL, PRIMARY KEY (PK));

INSERT INTO X_10_DISTINCT_CI WITH (TABLOCK) (VAL)
SELECT REPLICATE(CHAR(65 + (N / 100000 ) % 10 ), 10)
FROM dbo.GetNums(10000000);

UPDATE STATISTICS X_10_DISTINCT_CI WITH FULLSCAN;

以下查询扫描所有10表格中的一百万行。我怎么能得到不能扫描整个桌子的东西？我正在使用SQL Server 2016 SP1。

SELECT DISTINCT TOP 10 VAL
FROM X_10_DISTINCT_HEAP
OPTION (MAXDOP 1);

#1 楼

在上面的查询中，看起来有三种不同的优化器规则可以执行DISTINCT操作。以下查询引发错误，表明该列表是详尽无遗的：

SELECT DISTINCT TOP 10 ID
FROM X_10_DISTINCT_HEAP
OPTION (MAXDOP 1, QUERYRULEOFF GbAggToSort, QUERYRULEOFF GbAggToHS, QUERYRULEOFF GbAggToStrm);

Msg 8622，级别16，状态1，行1

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

GbAggToSort将分组汇总（区别）实现为一种独特的排序方式。这是一个阻塞运算符，它将在产生任何行之前从输入读取所有数据。 GbAggToStrm将group-by聚合实现为流聚合（在这种情况下，也需要输入排序）。这也是一个阻塞运算符。 GbAggToHS实现为哈希匹配，这是我们从问题中的错误计划中看到的结果，但可以实现为哈希匹配（聚合）或哈希匹配（流程不同）。

哈希匹配（流不同）运算符是解决此问题的一种方法，因为它不会阻塞。 SQL Server一旦找到足够多的不同值，便应该能够停止扫描。<<流/>逻辑>操作器将扫描输入，删除重复项。尽管Distinct运算符会在产生任何输出之前消耗所有输入，但是Flow Distinct运算符会返回从输入中获取的每一行（除非该行是重复的，在这种情况下将被丢弃）。

为什么问题中的查询使用哈希匹配（聚合）而不是哈希匹配（流不同）？随着表中不同值数量的变化，我希望哈希匹配（流不同）查询的成本会减少，因为对需要扫描到表的行数的估计应该减少。我希望哈希匹配（聚合）计划的成本会增加，因为需要构建的哈希表会变得更大。研究此问题的一种方法是创建计划指南。如果我创建了两个数据副本，但对其中一个应用了计划指南，则我应该能够针对同一数据并排比较哈希匹配（聚合）与哈希匹配（不同）。请注意，我无法通过禁用查询优化器规则来做到这一点，因为相同的规则适用于两个计划（GbAggToHS）。

这是获取我所遵循的计划指南的一种方法：

DROP TABLE IF EXISTS X_PLAN_GUIDE_TARGET;

CREATE TABLE X_PLAN_GUIDE_TARGET (VAL VARCHAR(10) NOT NULL);

INSERT INTO X_PLAN_GUIDE_TARGET WITH (TABLOCK)
SELECT CAST(N % 10000 AS VARCHAR(10))
FROM dbo.GetNums(10000000);

UPDATE STATISTICS X_PLAN_GUIDE_TARGET WITH FULLSCAN;

-- run this query
SELECT DISTINCT TOP 10 VAL  FROM X_PLAN_GUIDE_TARGET  OPTION (MAXDOP 1)

获取计划手柄并将其用于创建计划指南：

-- plan handle is 0x060007009014BC025097E88F6C01000001000000000000000000000000000000000000000000000000000000
SELECT qs.plan_handle, st.text FROM 
sys.dm_exec_query_stats AS qs   
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st  
WHERE st.text LIKE '%X[_]PLAN[_]GUIDE[_]TARGET%'
ORDER BY last_execution_time DESC;

EXEC sp_create_plan_guide_from_handle 
'EVIL_PLAN_GUIDE', 
0x060007009014BC025097E88F6C01000001000000000000000000000000000000000000000000000000000000;

计划指南仅适用于确切的查询文本，因此让我们从计划指南中将其复制回：

SELECT query_text
FROM sys.plan_guides
WHERE name = 'EVIL_PLAN_GUIDE';

重置数据：

TRUNCATE TABLE X_PLAN_GUIDE_TARGET;

INSERT INTO X_PLAN_GUIDE_TARGET WITH (TABLOCK)
SELECT REPLICATE(CHAR(65 + (N / 100000 ) % 10 ), 10)
FROM dbo.GetNums(10000000);

应用计划指南获取查询的查询计划：

SELECT DISTINCT TOP 10 VAL  FROM X_PLAN_GUIDE_TARGET  OPTION (MAXDOP 1)

它具有我们想要的测试数据哈希匹配（流不同）运算符。请注意，SQL Server希望从表中读取所有行，并且估计成本与具有哈希匹配项（聚合）的计划完全相同。我所做的测试表明，当计划的行目标大于或等于SQL Server从表中期望的不同值的数量时，这两个计划的成本是相同的，在这种情况下，可以直接从表中得出统计。不幸的是（对于我们的查询），当成本相同时，优化器会选择哈希匹配（聚合），而不是哈希匹配（流不同）。因此，我们比所需的计划少了0.0000001个魔术优化器单元。

解决此问题的一种方法是减少行目标。如果从优化器的角度来看行目标小于行的不同计数，则我们可能会得到哈希匹配（流不同）。这可以通过OPTIMIZE FOR查询提示来完成：

DECLARE @j INT = 10;

SELECT DISTINCT TOP (@j) VAL
FROM X_10_DISTINCT_HEAP
OPTION (MAXDOP 1, OPTIMIZE FOR (@j = 1));

对于此查询，优化器将创建一个计划，就好像该查询只需要第一行，但是在执行查询时一样它会返回前10行。在我的计算机上，此查询从X_10_DISTINCT_HEAP扫描892800行，并在299毫秒内完成，具有250毫秒的CPU时间和2537逻辑读取。

请注意，如果统计信息仅报告一个不同的值（针对偏斜数据的抽样统计信息可能会发生这种情况），则此技术将无效。但是，在这种情况下，您的数据不太可能足够密集地打包以使用此类技术进行验证。扫描表中的所有数据可能不会造成很多损失，特别是如果可以并行完成的话。

解决此问题的另一种方法是通过夸大SQL Server期望从基表中获得的估计的不同值的数量。这比预期的要难。应用确定性函数可能无法增加结果的不同计数。如果查询优化器知道该数学事实（一些测试表明至少是出于我们的目的），则应用确定性函数（包括所有字符串函数）将不会增加不同行的估计数量。

许多不确定函数也不起作用，包括NEWID()和RAND()的明显选择。但是，LAG()可以解决此查询问题。查询优化器期望针对LAG表达式期望1000万个不同的值，这将鼓励进行哈希匹配（流量不同）计划：

SELECT DISTINCT TOP 10 LAG(VAL, 0) OVER (ORDER BY (SELECT NULL)) AS ID
FROM X_10_DISTINCT_HEAP
OPTION (MAXDOP 1);

在我的计算机上，此查询扫描892800行X_10_DISTINCT_HEAP并在1165 ms内完成，具有1109 ms的CPU时间和2537逻辑读取，因此LAG()增加了相当多的相对开销。 @Paul White建议尝试对此查询进行批处理模式处理。在SQL Server 2016上，即使使用MAXDOP 1也可以进行批处理模式处理。对行存储表进行批处理方式处理的一种方法是，按如下所示连接到空CCI：

CREATE TABLE #X_DUMMY_CCI (ID INT NOT NULL);

CREATE CLUSTERED COLUMNSTORE INDEX X_DUMMY_CCI ON #X_DUMMY_CCI;

SELECT DISTINCT TOP 10 VAL
FROM
(
    SELECT LAG(VAL, 1) OVER (ORDER BY (SELECT NULL)) AS VAL
    FROM X_10_DISTINCT_HEAP
    LEFT OUTER JOIN #X_DUMMY_CCI ON 1 = 0
) t
WHERE t.VAL IS NOT NULL
OPTION (MAXDOP 1);

该代码将导致此查询计划。

Paul指出，我必须更改查询以使用LAG(..., 1)，因为LAG(..., 0)似乎不符合Window Aggregate优化的条件。此更改将经过时间减少到520 ms，将CPU时间减少到454 ms。

请注意，LAG()方法不是最稳定的方法。如果Microsoft针对该功能更改了唯一性假设，则它可能不再起作用。与旧版CE的估算值不同。同样，针对堆的这种优化也不是一个好主意。如果重建表，则可能会遇到最坏的情况，即几乎所有行都需要从表中读取。

针对具有唯一列的表（例如集群表）问题中的索引示例），我们有更好的选择。例如，我们可以使用始终返回空字符串的SUBSTRING表达式来欺骗优化器。 SQL Server认为SUBSTRING不会更改不同值的数量，因此，如果将其应用于唯一列（例如PK），则估计不同行的数量为1000万。以下查询获取哈希匹配（流区分）运算符：

SELECT DISTINCT TOP 10 VAL + SUBSTRING(CAST(PK AS VARCHAR(10)), 11, 1)
FROM X_10_DISTINCT_CI
OPTION (MAXDOP 1);

在我的计算机上，此查询扫描来自X_10_DISTINCT_CI的900000行，并在333 ms和297 ms CPU中完成时间和3011逻辑读取。

总而言之，当SELECT DISTINCT TOP N> =估计的表中不同的行数时，查询优化器似乎假定将从表中读取所有行。散列匹配（聚合）运算符的成本可能与散列匹配（流不同）运算符的成本相同，但是优化器始终选择聚合运算符。当足够多的不同值位于表扫描开始处附近时，这可能导致不必要的逻辑读取。诱使优化器使用哈希匹配（流不同）运算符的两种方法是使用N提示降低行目标，或使用唯一列上的OPTIMIZE FOR或LAG()增加估计的不同行数。

#2 楼

您已经正确回答了自己的问题。

我想补充一点，即最有效的方法实际上是扫描整个表-如果可以将其组织为列存储“堆”：

CREATE CLUSTERED COLUMNSTORE INDEX CCSI 
ON dbo.X_10_DISTINCT_HEAP;

简单查询：

SELECT DISTINCT TOP (10)
    XDH.VAL 
FROM dbo.X_10_DISTINCT_HEAP AS XDH
OPTION (MAXDOP 1);

然后给出：

Table 'X_10_DISTINCT_HEAP'. Scan count 1, 
 logical reads 0, physical reads 0, read-ahead reads 0, 
 lob logical reads 66, lob physical reads 0, lob read-ahead reads 0.
Table 'X_10_DISTINCT_HEAP'. Segment reads 13, segment skipped 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 11 ms.

当前不能以批处理模式执行哈希匹配（流区分）。由于从批处理到行处理的（不可见的）昂贵的过渡，使用此方法的方法要慢得多。例如：

SET ROWCOUNT 10;

SELECT DISTINCT 
    XDH.VAL
FROM dbo.X_10_DISTINCT_HEAP AS XDH
OPTION (FAST 1);

SET ROWCOUNT 0;

礼物：

Table 'X_10_DISTINCT_HEAP'. Scan count 1, 
 logical reads 0, physical reads 0, read-ahead reads 0, 
 lob logical reads 20, lob physical reads 0, lob read-ahead reads 0.
Table 'X_10_DISTINCT_HEAP'. Segment reads 4, segment skipped 0.

 SQL Server Execution Times:
   CPU time = 640 ms,  elapsed time = 680 ms.

这是比将表组织为行存储堆时要慢。

#3 楼

这是尝试使用递归CTE模拟重复的部分扫描（类似于但不同于跳过扫描）的尝试。这样做的目的是-避免对表进行排序和多次扫描，因为我们在(id)上没有索引。

绕过一些递归CTE限制有一些技巧：

递归部分中不允许TOP。我们改用子查询和ROW_NUMBER()。
我们不能对常量部分有多个引用，也不能使用LEFT JOIN或从递归部分使用NOT IN (SELECT id FROM cte)。要绕过，我们构建了一个VARCHAR字符串，该字符串累积了所有id值（类似于STRING_AGG或hierarchyID），然后与LIKE进行比较。

对于堆（假设该列名为id）test-1 on rextester.com。

正如测试所示，这不能避免多次扫描，但是在前几页中找到不同的值时执行OK。但是，如果值分布不均匀，则可能会对表的大部分进行多次扫描-这当然会导致性能下降。

WITH ct (id, found, list) AS
  ( SELECT TOP (1) id, 1, CAST('/' + id + '/' AS VARCHAR(MAX))
    FROM x_large_table_2
  UNION ALL
    SELECT y.ID, ct.found + 1, CAST(ct.list + y.id + '/' AS VARCHAR(MAX))
    FROM ct
      CROSS APPLY 
      ( SELECT x.id, 
               rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
        FROM x_large_table_2 AS x
        WHERE ct.list NOT LIKE '%/' + id + '/%'
      ) AS y
    WHERE ct.found < 3         -- the TOP (n) parameter here
      AND y.rn = 1
  )
SELECT id FROM ct ;

，并且当表被聚集时（unique_key上的CI），在rextester.com上进行test-2。

这使用聚簇索引（WHERE x.unique_key > ct.unique_key）避免多次扫描：

WITH ct (unique_key, id, found, list) AS
  ( SELECT TOP (1) unique_key, id, 1, CAST(CONCAT('/',id, '/') AS VARCHAR(MAX))
    FROM x_large_table_2
  UNION ALL
    SELECT y.unique_key, y.ID, ct.found + 1, 
        CAST(CONCAT(ct.list, y.id, '/') AS VARCHAR(MAX))
    FROM ct
      CROSS APPLY 
      ( SELECT x.unique_key, x.id, 
               rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
        FROM x_large_table_2 AS x
        WHERE x.unique_key > ct.unique_key
          AND ct.list NOT LIKE '%/' + id + '/%'
      ) AS y
    WHERE ct.found < 5       -- the TOP (n) parameter here
      AND y.rn = 1
  )
-- SELECT * FROM ct ;        -- for debugging
SELECT id FROM ct ;

该解决方案存在一个相当细微的性能问题。找到第N个值后，最终在表上进行了额外的查找。因此，如果前10个值有10个不同的值，它将寻找第11个不存在的值。最后，您需要进行额外的全面扫描，并且实际上总共有1000万个ROW_NUMBER（）计算。我这里有一个解决方法，可以将机器上的查询速度提高20倍。你怎么看？ brentozar.com/pastetheplan/?id=SkDhAmFKe

– Joe Obbish
17年2月21日在3:07

#4 楼

为了完整性，解决此问题的另一种方法是使用OUTER APPLY。我们可以为每个需要查找的不同值添加一个OUTER APPLY运算符。这在概念上与ypercube的递归方法相似，但是有效地手工编写了递归。一个优点是，我们能够在派生表中使用TOP而不是ROW_NUMBER()解决方法。一个很大的缺点是查询文本随着N的增加而变长。

这是针对堆的查询的一种实现：

SELECT VAL
FROM (
    SELECT t1.VAL VAL1, t2.VAL VAL2, t3.VAL VAL3, t4.VAL VAL4, t5.VAL VAL5, t6.VAL VAL6, t7.VAL VAL7, t8.VAL VAL8, t9.VAL VAL9, t10.VAL VAL10
    FROM 
    ( 
    SELECT TOP 1 VAL FROM X_10_DISTINCT_HEAP 
    ) t1
    OUTER APPLY
    ( 
    SELECT TOP 1 VAL FROM X_10_DISTINCT_HEAP t2 WHERE t2.VAL NOT IN (t1.VAL)
    ) t2
    OUTER APPLY
    ( 
    SELECT TOP 1 VAL FROM X_10_DISTINCT_HEAP t3 WHERE t3.VAL NOT IN (t1.VAL, t2.VAL)
    ) t3
    OUTER APPLY
    ( 
    SELECT TOP 1 VAL FROM X_10_DISTINCT_HEAP t4 WHERE t4.VAL NOT IN (t1.VAL, t2.VAL, t3.VAL)
    ) t4
    OUTER APPLY
    ( 
    SELECT TOP 1 VAL FROM X_10_DISTINCT_HEAP t5 WHERE t5.VAL NOT IN (t1.VAL, t2.VAL, t3.VAL, t4.VAL)
    ) t5
    OUTER APPLY
    ( 
    SELECT TOP 1 VAL FROM X_10_DISTINCT_HEAP t6 WHERE t6.VAL NOT IN (t1.VAL, t2.VAL, t3.VAL, t4.VAL, t5.VAL)
    ) t6
    OUTER APPLY
    ( 
    SELECT TOP 1 VAL FROM X_10_DISTINCT_HEAP t7 WHERE t7.VAL NOT IN (t1.VAL, t2.VAL, t3.VAL, t4.VAL, t5.VAL, t6.VAL)
    ) t7
    OUTER APPLY
    ( 
    SELECT TOP 1 VAL FROM X_10_DISTINCT_HEAP t8 WHERE t8.VAL NOT IN (t1.VAL, t2.VAL, t3.VAL, t4.VAL, t5.VAL, t6.VAL, t7.VAL)
    ) t8
    OUTER APPLY
    ( 
    SELECT TOP 1 VAL FROM X_10_DISTINCT_HEAP t9 WHERE t9.VAL NOT IN (t1.VAL, t2.VAL, t3.VAL, t4.VAL, t5.VAL, t6.VAL, t7.VAL, t8.VAL)
    ) t9
    OUTER APPLY
    ( 
    SELECT TOP 1 VAL FROM X_10_DISTINCT_HEAP t10 WHERE t10.VAL NOT IN (t1.VAL, t2.VAL, t3.VAL, t4.VAL, t5.VAL, t6.VAL, t7.VAL, t8.VAL, t9.VAL)
    ) t10
) t
UNPIVOT 
(
  VAL FOR VALS IN (VAL1, VAL2, VAL3, VAL4, VAL5, VAL6, VAL7, VAL8, VAL9, VAL10)
) AS upvt;

这里是上述查询的实际查询计划。在我的计算机上，此查询在713毫秒（625毫秒的CPU时间和12605逻辑读取）中完成。我们每10万行获得一个新的不同值，因此我希望该查询能够扫描约900000 * 10 * 0.5 = 4500000行。从理论上讲，此查询应从另一个答案中对该查询进行逻辑读取五倍：

DECLARE @j INT = 10;

SELECT DISTINCT TOP (@j) VAL
FROM X_10_DISTINCT_HEAP
OPTION (MAXDOP 1, OPTIMIZE FOR (@j = 1));

该查询进行了2537次逻辑读取。 2537 * 5 = 12685，非常接近12605。

对于具有聚集索引的表，我们可以做得更好。这是因为我们可以将最后一个集群键值传递到派生表中，以避免两次扫描相同的行。一种实现：

SELECT VAL
FROM (
    SELECT t1.VAL VAL1, t2.VAL VAL2, t3.VAL VAL3, t4.VAL VAL4, t5.VAL VAL5, t6.VAL VAL6, t7.VAL VAL7, t8.VAL VAL8, t9.VAL VAL9, t10.VAL VAL10
    FROM 
    ( 
    SELECT TOP 1 PK, VAL FROM X_10_DISTINCT_CI 
    ) t1
    OUTER APPLY
    ( 
    SELECT TOP 1 PK, VAL FROM X_10_DISTINCT_CI t2 WHERE PK > t1.PK AND t2.VAL NOT IN (t1.VAL)
    ) t2
    OUTER APPLY
    ( 
    SELECT TOP 1 PK, VAL FROM X_10_DISTINCT_CI t3 WHERE PK > t2.PK AND t3.VAL NOT IN (t1.VAL, t2.VAL)
    ) t3
    OUTER APPLY
    ( 
    SELECT TOP 1 PK, VAL FROM X_10_DISTINCT_CI t4 WHERE PK > t3.PK AND t4.VAL NOT IN (t1.VAL, t2.VAL, t3.VAL)
    ) t4
    OUTER APPLY
    ( 
    SELECT TOP 1 PK, VAL FROM X_10_DISTINCT_CI t5 WHERE PK > t4.PK AND t5.VAL NOT IN (t1.VAL, t2.VAL, t3.VAL, t4.VAL)
    ) t5
    OUTER APPLY
    ( 
    SELECT TOP 1 PK, VAL FROM X_10_DISTINCT_CI t6 WHERE PK > t5.PK AND t6.VAL NOT IN (t1.VAL, t2.VAL, t3.VAL, t4.VAL, t5.VAL)
    ) t6
    OUTER APPLY
    ( 
    SELECT TOP 1 PK, VAL FROM X_10_DISTINCT_CI t7 WHERE PK > t6.PK AND t7.VAL NOT IN (t1.VAL, t2.VAL, t3.VAL, t4.VAL, t5.VAL, t6.VAL)
    ) t7
    OUTER APPLY
    ( 
    SELECT TOP 1 PK, VAL FROM X_10_DISTINCT_CI t8 WHERE PK > t7.PK AND t8.VAL NOT IN (t1.VAL, t2.VAL, t3.VAL, t4.VAL, t5.VAL, t6.VAL, t7.VAL)
    ) t8
    OUTER APPLY
    ( 
    SELECT TOP 1 PK, VAL FROM X_10_DISTINCT_CI t9 WHERE PK > t8.PK AND t9.VAL NOT IN (t1.VAL, t2.VAL, t3.VAL, t4.VAL, t5.VAL, t6.VAL, t7.VAL, t8.VAL)
    ) t9
    OUTER APPLY
    ( 
    SELECT TOP 1 PK, VAL FROM X_10_DISTINCT_CI t10 WHERE PK > t9.PK AND t10.VAL NOT IN (t1.VAL, t2.VAL, t3.VAL, t4.VAL, t5.VAL, t6.VAL, t7.VAL, t8.VAL, t9.VAL)
    ) t10
) t
UNPIVOT 
(
  VAL FOR VALS IN (VAL1, VAL2, VAL3, VAL4, VAL5, VAL6, VAL7, VAL8, VAL9, VAL10)
) AS upvt;

这是上述查询的实际查询计划。在我的计算机上，此查询在154毫秒内完成，具有140毫秒的CPU时间和3203逻辑读取。这似乎比针对聚集索引表的OPTIMIZE FOR查询运行得快一些。我没想到，所以我尝试更仔细地评估性能。我的方法是在没有结果集的情况下运行每个查询十次，并查看sys.dm_exec_sessions和sys.dm_exec_session_wait_stats的总数。会话56是APPLY查询，会话63是OPTIMIZE FOR查询。

sys.dm_exec_sessions的输出：

╔════════════╦══════════╦════════════════════╦═══════════════╗
║ session_id ║ cpu_time ║ total_elapsed_time ║ logical_reads ║
╠════════════╬══════════╬════════════════════╬═══════════════╣
║         56 ║     1360 ║               1373 ║         32030 ║
║         63 ║     2094 ║               2091 ║         30400 ║
╚════════════╩══════════╩════════════════════╩═══════════════╝

似乎有明显的优势。在APPLY查询的cpu_time和elapsed_time中。

sys.dm_exec_session_wait_stats的输出：

╔════════════╦════════════════════════════════╦═════════════════════╦══════════════╦══════════════════╦═════════════════════╗
║ session_id ║           wait_type            ║ waiting_tasks_count ║ wait_time_ms ║ max_wait_time_ms ║ signal_wait_time_ms ║
╠════════════╬════════════════════════════════╬═════════════════════╬══════════════╬══════════════════╬═════════════════════╣
║         56 ║ SOS_SCHEDULER_YIELD            ║                 340 ║            0 ║                0 ║                   0 ║
║         56 ║ MEMORY_ALLOCATION_EXT          ║                  38 ║            0 ║                0 ║                   0 ║
║         63 ║ SOS_SCHEDULER_YIELD            ║                 518 ║            0 ║                0 ║                   0 ║
║         63 ║ MEMORY_ALLOCATION_EXT          ║                  98 ║            0 ║                0 ║                   0 ║
║         63 ║ RESERVED_MEMORY_ALLOCATION_EXT ║                 400 ║            0 ║                0 ║                   0 ║
╚════════════╩════════════════════════════════╩═════════════════════╩══════════════╩══════════════════╩═════════════════════╝

OPTIMIZE FOR查询具有附加的等待类型RESERVED_MEMORY_ALLOCATION_EXT。我不完全知道这意味着什么。它可能只是散列匹配（流量不同）运算符中开销的度量。无论如何，CPU时间相差70毫秒也许都不值得担心。

#5 楼

我想您对为什么这样有答案
这可能是解决它的一种方式
我知道它看起来很乱，但是执行计划说前2名仅占成本的84％

SELECT distinct top (2)  [enumID]
FROM [ENRONbbb].[dbo].[docSVenum1]

declare @table table (enumID tinyint);
declare @enumID tinyint;
set @enumID = (select top (1) [enumID] from [docSVenum1]);
insert into @table values (@enumID);
set @enumID = (select top (1) [enumID] from [docSVenum1] where [enumID] not in (select enumID from @table));
insert into @table values (@enumID);
set @enumID = (select top (1) [enumID] from [docSVenum1] where [enumID] not in (select enumID from @table));
insert into @table values (@enumID);
set @enumID = (select top (1) [enumID] from [docSVenum1] where [enumID] not in (select enumID from @table));
insert into @table values (@enumID);
set @enumID = (select top (1) [enumID] from [docSVenum1] where [enumID] not in (select enumID from @table));
insert into @table values (@enumID);
set @enumID = (select top (1) [enumID] from [docSVenum1] where [enumID] not in (select enumID from @table));
insert into @table values (@enumID);
set @enumID = (select top (1) [enumID] from [docSVenum1] where [enumID] not in (select enumID from @table));
insert into @table values (@enumID);
set @enumID = (select top (1) [enumID] from [docSVenum1] where [enumID] not in (select enumID from @table));
insert into @table values (@enumID);
set @enumID = (select top (1) [enumID] from [docSVenum1] where [enumID] not in (select enumID from @table));
insert into @table values (@enumID);
set @enumID = (select top (1) [enumID] from [docSVenum1] where [enumID] not in (select enumID from @table));
insert into @table values (@enumID);
select enumID from @table;

这段代码在我的机器上花了5秒钟。看起来，对表变量的联接增加了很多开销。在最终查询中，表变量被扫描892800次。该查询花费了1359毫秒的CPU时间和1374毫秒的经过时间。绝对超出我的预期。向主表变量添加主键似乎有所帮助，尽管我不确定为什么。可能还有其他可能的优化。

– Joe Obbish
17年2月21日在6:02

编程黑洞网