我有一个数据仓库数据库,但我在使用 SQL Server 2014 的新基数估计器时遇到问题。
将数据库服务器升级到 SQL Server 2014 后,我发现查询性能存在很大差异。某些查询的执行速度要慢得多(SQL 2012 中为 30 秒,而 SQL 2014 中为 5 分钟)。
在研究执行计划后,我发现 SQL Server 2014 上的基数估计相差很大,而且我找不到原因。
以下是 SQL 2012 与 SQL 2014 中的查询执行计划(左上角运算符)的示例:
一些细节:
我的查询是典型的数据仓库事实表加载查询。我查询事务表并连接很多 (15-20) 个维度表(始终有 0 或 1 条记录从维度表连接)。
我已经更新了所有表的统计信息(使用 FULLSCAN)以确保统计信息是最新的。
维度表的业务键是有索引的(唯一非聚集索引)。在我看来,由于该索引的唯一性,旧的基数估计器(SQL 2012)正确地假设有最大值。 1 个连接的记录(执行计划中估计的记录数不会改变)。
我试图将问题缩小到最简单的例子 – 带有 2 个连接的 SELECT:
以下是 SQL 2012 与 SQL 2014 中运算符 1 和 2 的基数估计:
| Est.rows - SQL2012 | Est.rows - SQL2014
Operator 1 | 7653 | 7653
Operator 2 | 7653 | 10000
正如您所看到的,SQL Server 2014 与估计相差超过 30%(10000 与 7653)。因为我有cca。典型的查询中有 15-20 个连接,最终的估计会相差很大。
我可以将数据库置于较低的兼容性模式 (110),然后它就可以正常工作(与 SQL Server 2012 相同),但我真的很想知道这种行为的原因是什么。为什么SQL Server 2014的基数估计器结果错误?
我认为今天这个有趣的问题没有简单的答案。我知道的最佳答案是以下视频:http://channel9.msdn.com/events/TechEd/NorthAmerica/2014/DBI-B331#fbid= http://channel9.msdn.com/events/TechEd/NorthAmerica/2014/DBI-B331#fbid=。它有许多新旧估计器的例子。视频长约 50 多分钟,但值得花时间。
与此问题相关的视频摘要:
基数估计的旧假设:
- 均匀性——数据是均匀分布的。
- 独立性 – 第 1 列与第 2 列没有关系。
- 遏制——当两个属性可能相同时,假定它们相同。
- 包容性——应该有一个匹配。
要在 SQL SERVER 2014 中使用 SQL SERVER 2012 基数估计器,请使用以下选项:
- 选项 (querytraceon 9481) --恢复到 2012 年
新估算器在做什么(基于视频):
- SQL Server 在索引中使用平均选择性并进行估计
行数乘以键的密度乘以总行数
索引中的行。
- 新的估计器不能很好地处理锯齿状分布。
- 估计器之间的大多数差异都基于 WHERE 子句。
- 新的基数估计器认为表之间存在相关性。
- 您可以创建过滤统计信息以改进查询。 (http://msdn.microsoft.com/en-us/library/ms188038.aspx http://msdn.microsoft.com/en-us/library/ms188038.aspx )
待办事项/清单:
1. Auto Create / Update Stats
2. Check database compatibility mode (120/110)
3. Test using query trace flags
4. XML showplan
Update基数估计器的新增功能 (SQL Server 2016)
- 越准确。
- CE 预测您的查询可能返回多少行
- SQL Server 2016 查询存储
- 跟踪 CE 基数预测的另一个选项是使用名为 query_optimizer_estimate_cardinality 的扩展事件
- CE 了解最大值可能高于上次收集统计数据时的值
- CE 理解同一个表上的过滤谓词通常是相关的
- CE 不再假定来自不同表的过滤谓词之间存在任何相关性
更多细节:
https://learn.microsoft.com/en-us/sql/relational-databases/performance/cardinality-estimation-sql-server https://learn.microsoft.com/en-us/sql/relational-databases/performance/cardinality-estimation-sql-server
https://www.sqlshack.com/query-optimizer-changes-in-sql-server-2016-explained/ https://www.sqlshack.com/query-optimizer-changes-in-sql-server-2016-explained/
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)