Simple Query 优化后变为 Serial，需要 Parallel

我继承了一个具有支持 Web 应用程序的存储过程的遗留系统。它每天运行数千次。该应用程序允许用户输入部分客户详细信息，并且底层存储过程将执行 LIKE 命令以将可能客户的候选名单返回给 Web 应用程序。

它甚至允许部分输入客户 ID（整数值），因此如果将值“123”输入到应用程序中，则存储过程应返回 ID 包含“123”的所有客户，即“612345”或“222123” '.....是的，我知道疯了，输入'1'会返回一个巨大的数据集，但我们无法更改应用程序。

搜索客户 ID 是存储过程中最慢的部分。我已经“优化”了存储过程（在 DEV 中），因此它使用更少的 IO 和更少的 CPU ......但它已经串行化并且运行时间太长......帮助！

这是重新创建问题的方法。

--CREATE TEST TABLE
CREATE TABLE dbo.Test(ID INT IDENTITY(1,1) PRIMARY KEY
                        , Val Float
                        , CodeVal CHAR(100)
                        )
GO

--GENERATE A FEW MILLION TEST RECORD
INSERT INTO dbo.Test
SELECT
    RAND()
    ,CONVERT(varchar(255), NEWID())
FROM 
    sys.objects --Contains 639 Rows
GO 10000

--CREATE INDEX ON ID FIELD
CREATE UNIQUE NONCLUSTERED INDEX idx ON dbo.Test(ID)

现有查询执行并行索引扫描。

DECLARE @ID VARCHAR(20) = '123456'

--DROP TABLE #TMP1
CREATE TABLE #TMP1(ID INT)

INSERT INTO #TMP1
SELECT  ID 
FROM    dbo.Test
WHERE   CONVERT(VARCHAR(20),ID) LIKE '%'+@ID+'%'

然后我意识到，如果用户输入的部分客户 ID 为“123456”，那么结果集永远不会包含小于“123456”的值。所以我在代码中添加了一个额外的行（见下文），现在我有一个 INDEX SEEK，它减少了 IO 并减少了 CPU 时间。但令我恐惧的是，它已经消失了串行所以现在它需要永远。如果上线，那么用户的等待时间将从 4 秒跳到 20 秒。

INSERT INTO #TMP1
SELECT  ID 
FROM    dbo.Test
WHERE   CONVERT(VARCHAR(20),ID) LIKE '%'+@ID+'%'
AND     ID >= @ID   --New line of code

我现在已经到了盲目编码的地步，希望它能并行。

谁能解释为什么会发生这种情况（除了“优化器认为它更好”的股票答案）？

谁能弄清楚如何使查询并行？

跟踪标志 8649 对我来说不是一个选项。

我已经阅读了Paul White的这篇非常有帮助的文章。

更新：

看起来提供的示例会根据系统规范和测试表上的行数的组合产生不同的结果。

抱歉，如果您无法重现问题。但这是我的问题答案的一部分。

更新：（执行计划）

Aaron：很抱歉，如果它在您的系统上不起作用，会令人沮丧。（由于信息安全的原因，我无法分享我公司的实际执行计划）

我已经在我的家庭系统上重新创建了这个问题。

这是执行计划，（我将完整上传到计划后）

我当前的 dbo.Test 行数是 2124160，非聚集索引有 2627 页和 0.04% 碎片，下面是我的统计信息。

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 1 ms.
Table 'Test'. Scan count 3, logical reads 2652, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 688 ms,  elapsed time = 368 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 2 ms.
Table 'Test'. Scan count 1, logical reads 1106, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 281 ms,  elapsed time = 302 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

与客户打交道的呼叫中心工作人员每天使用存储过程大约 5000 次。增加 16 秒的等待时间将花费员工每天 22 多小时等待结果。以 8 小时的轮班时间计算，这相当于 3 名全职员工每年的成本约为 36,000 英镑。有钱升级到 SQL Sentry 许可证（大粉丝），我们目前有 Spotlight（仍然是一个很好的产品），但我想要更深入的东西。

更新：（幻数）

有 12,000 行，两个查询都是串行的。

对于 2,124,160 行，一个查询是并行的。

66,000,000 行都是并行的。

优化器肯定有一个规则/指标使用，如果你有一个索引扫描或索引搜索，它是不同的。这是否意味着将查询从 Scan 转换为 Seek 会对特定大小的表/索引产生不利影响？

更新：（解决方案）

谢谢乔，您对最大并行度非常了解。

我用了：

OPTION (OPTIMIZE FOR (@ID  = 1))

UNKNOWN 对我的环境没有作用。

对于任何对此感兴趣的人，这里是Kendra Little的一篇有用的文章。