升级到SQL Server 2014遇到的查询性能问题

[TOC]

问题描述

• 在同一个SQL Server 2014实例上执行一个存储过程，很慢要60s
• 如果在把数据库兼容性级别由120改为110就很快

问题原因

查询优化器为了找出有效的执行计划，会根据 cost 运算，取出 cost 最小的计划，作为执行计划。其中影响 cost 最重要的一项就是基数评估（估计行数）。

SQL Server 升级到2014后，执行计划的改变，是由于SQL Server 2014重新改写了基数评估（Cardinality Estimator）这个模块。具体见下面文档：

Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator

Summary: SQL Server 2014 introduces the first major redesign of the SQL Server Query Optimizer cardinality estimation process since version 7.0. The goal for the redesign was to improve accuracy, consistency and supportability of key areas within the cardinality estimation process, ultimately affecting average query execution plan quality and associated workload performance. This paper provides an overview of the primary changes made to the cardinality estimator functionality by the Microsoft query processor team, covering how to enable and disable the new cardinality estimator behavior, and showing how to troubleshoot plan-quality regressions if and when they occur.

来源：https://msdn.microsoft.com/en-us/library/dn673537.aspx

这个功能对大部分的语句，其执行计划会有改善，但对于某些情况，则会和之前的预估不符，所以走了别的执行计划。针对这种Regression。有下面的一些方法：

What Actions can You Take if You See a Plan Regression?

Consider the following actions if you have encountered performance degradation directly caused by the new CE:

• Retain the new CE setting if specific queries still benefit, and “design around” performance issues using alternative methods. For example, for a relational data warehouse query with significant fact-table cardinality estimate skews, consider moving to a columnstore index solution. In this scenario, you retain the cardinality estimate skew. However, the compensating performance improvement of a columnstore index could remove the performance degradation from the legacy version.
• Retain the new CE, and use trace flag 9481 for those queries that had performance degradations directly caused by the new CE. This may be appropriate for tests where only a few queries in a workload had performance degradation caused by the new CE.
• Revert to an older database compatibility level, and use trace flag 2312 for queries that had performance improvements using the new CE. This may be appropriate for tests where only a few queries in the workload had improved performance.
• Use fundamental cardinality estimate skew troubleshooting methods. This option does not address the original root cause. However, using fundamental cardinality estimation troubleshooting methods might address an overall estimate skew problem and improve query performance. For information about troubleshooting, read the Fundamental Troubleshooting Methods section.
• Revert to the legacy CE entirely. This might be appropriate if multiple, critical queries encounter performance regressions and you do not have time to test and redesign around the issues.

启用新的基数评估

当数据库的兼容级别为120的时候，就是启用了新的基数评估，默认使用新的基数评估，但是可以启用跟踪标记使用老的基数评估；

• 2312 ：在兼容级别低于 120 的时候使用新的基数评估
• 9481 ：在兼容级别在 120 下，使用老的基数评估

验证基数评估的版本

查看SQL语句的实际执行计划，从图形执行计划或者 XML 执行计划的属性里面找到 CardinalityEstimationModelVersion，如果为 120 表示这是新的基数评估，70 就是老的基数评估

解决办法

方案1-修改数据库的兼容级别

• 修改 Compatibility Level 为早期的兼容级别，比如将120改为110，以修改xxx库为例；

--.设置xxx库的兼容级别
ALTER DATABASE xxx SET COMPATIBILITY_LEVEL = 110

--.查看所有db的兼容级别
SELECT name,compatibility_level FROM sys.databases

方案2-激活9481跟踪标志

• 对于在兼容级别 120 及以上设置的db，激活跟踪标志 9481 会强制系统使用 CE 版本 70，也就是关闭新的Cardinality Estimator，使用老的基数评估。

--.全局激活9481跟踪标记
DBCC TRACEON(9481, -1);

• 执行 DBCC TRACESTATUS 若返回如下信息则表示9481跟踪标记已启用

TraceFlag	Status	Global	Session
9481	1	1	0

• 执行如下语句，以全局方式关闭9481跟踪标记：

--.全局禁用指定的跟踪标记
DBCC TRACEOFF(9481, -1)

几点注意

• 使用 DBCC TRACEON 启用的跟踪标志，重启SQL服务后将失效；
• 若要启动SQL服务之后自动启用某跟踪标记，可使用 -T9481 指示 SQL Server 实例启动时，指定的跟踪标志应同时生效；
• 若使用 -T 服务启动选项，则无法启动会话级别的跟踪标志；

参考

• 参考：SQL Server 基数估计
• 参考：DBCC TRACEON - 激活跟踪标志
• 参考：数据库引擎服务启动选项

方案3-数据库级别改回早期的技术评估

众所周知，SQL Server 有很多配置选项只能在 SQL Server 实例级别进行全局配置，例如MAXDOP选项和跟踪标记。但是从 SQL Server 2016 开始，这些配置选项可以在数据库级别就可以配置。通过对特定db打开早期参数估计（Legacy Cardinality Estimation ）以决定使用老的基数估计，而不必使用特定的跟踪标记（Trace Flags）。

执行如下语句，打开当前库的早期基数评估（默认关闭），即 使用老的参数评估：

USE xxx
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;

亦可在该db的属性页修改，如下图示：