SQL Server 2014 执行计划创建耗时较长(旧版本快) [英] SQL Server 2014 execution plan creation takes a long time (fast in older versions)

问题描述

我在 SQL Server 2014 中遇到查询问题.第一次运行此查询时，生成执行计划需要很长时间.

I've encountered a problem with a query in SQL Server 2014. The first time this query is run, it takes ages for the execution plan to be generated.

奇怪的是，它在所有以前版本的 SQL Server(2012、2008 R2、2008 等)中都运行良好.它似乎与所涉及的表之一上的唯一索引有关，并结合了主查询中的一定数量的子查询.

The strange thing is that it has worked just fine in all previous versions of SQL Server (2012, 2008 R2, 2008, etc.). It seems to be related to a unique index on one of the involved tables, combined with a certain amount of subqueries in the main query.

这是查询中涉及的表.与原始表格相比，我已经简化了很多表格，但问题仍然存在.请注意 table2 上的唯一约束，这似乎是导致问题的原因.不管是唯一约束，唯一索引，还是Table2上的主键，结果都是一样的.

Here are the involved tables in the query. I've simplified the tables a lot compared to the originals, but the problem persists. Note the unique constraint on table2, this seems to be what is causing the issue. It does not matter if it is a unique constraint, unique index, or even primary key on Table2, the result is the same.

IF OBJECT_ID('Table2') IS NOT NULL DROP TABLE [Table2]
IF OBJECT_ID('Table1') IS NOT NULL DROP TABLE [Table1]
CREATE TABLE [dbo].[Table1] ( [ReferencedColumn] [int] NOT NULL PRIMARY KEY)
CREATE TABLE [dbo].[Table2] ( [ReferencedColumn] [int] NOT NULL FOREIGN KEY REFERENCES [Table1] ([ReferencedColumn]), [IntColumn] [int] NOT NULL, [AnotherIntColumn] [int] NULL )
CREATE UNIQUE NONCLUSTERED INDEX [IX_Table2] ON [dbo].[Table2] ([ReferencedColumn], [IntColumn])

如果我然后在 select 语句中使用索引从表中执行一些子查询，则第一次完成需要很长时间(在我的测试中超过 30 秒).

If I then do some subqueries from the table with the index in a select statement, it takes ages to complete the first time (more than 30 seconds in my tests).

SELECT  (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 1 ),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 2 ),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 3 ),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 4 ),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 5 ),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 6 ),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 7 ),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 8 ),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 9 ),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 10),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 11),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 12),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 13),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 14),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 15),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 16),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 17),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 18),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 19),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 20),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 21),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 22),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 23),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 24),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 25)
FROM    Table1 F

由于表中没有行，并且查询在第一次运行后立即运行，在我看来，它必须是执行计划需要很长时间才能生成.

Since the tables have no rows in them, and since the query runs instantly after the first run, it seems to me that it must be the execution plan that takes a long time to generate.

但是，如果您执行下面列出的更改之一，则会立即生成执行计划.

However, if you do one of the changes listed below, the execution plan is generated instantly instead.

• 删除索引
• 删除索引的 UNIQUE 部分
• 向索引添加另一个IntColumn
• 将数据库的兼容性级别设置为 SQL Server 2012

值得注意的是，不同版本生成的执行计划是一样的，只是生成的时间有所不同.该计划包括许多计算标量"操作，但我不认为在 2012/2008 年立即生成相同的计划时会有什么问题.

It is worth noting that the execution plan that is generated is the same across the versions, only the time to generate it changes. The plan includes many "Compute Scalar" operations, but I don't see how can be a problem, when the same plan is generated instantly in 2012/2008.

我仅在 SQL Server 2014 Enterprise 和 Web 版本的几个实例上对其进行了测试，但我认为在 2014 的其他版本上也会发生相同的行为.

I've only tested it on a couple of instances of SQL Server 2014 Enterprise and Web editions, but I assume the same behavior will occur on other editions of 2014.

我已经有几种解决问题的方法(修改索引、更改兼容性级别、重写查询)，但我很好奇为什么与旧版本的 SQL Server 相比性能下降如此之大?

I already have several ways of solving the problem (modify the index, change compatibility level, rewrite the query), but I am curious why there is such a big drop in performance compared to the older versions of SQL Server?

推荐答案

SQL Server 2014 有一个 全新的查询优化器.基数估计(猜测语句将返回多少行)比过去的版本更具侵略性.存在新优化器需要更长时间才能找到最佳查询计划的错误和边缘情况.设置较低的兼容性级别会回退到以前的查询优化器.

SQL Server 2014 had a brand new Query Optimizer. Cardinality Estimation (guessing how many rows a statement will return) is much more aggressive in than in past versions. There are bugs and edge cases where the new optimizer will take longer to find the optimal query plan. Setting a lower compatibility level falls back to the previous Query Optimizer.

您的查询几乎是一种折磨测试.有更好的方法来编写它.但是我认为您在新的查询优化器中暴露了一个错误.在 SQL Connect 上提交错误报告.

Your query is pretty much a torture test. There are better ways to write it. But I think you exposed a bug in the new Query Optimizer. File a bug report on SQL Connect.

这篇关于SQL Server 2014 执行计划创建耗时较长(旧版本快)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！