我如何调优SQL Server查询

原文:我如何调优SQL Server查询

我是个懒人,我只想干尽可能少的活。当我干活的时候我不想太多。是,你没看错,这看起来很糟糕,作为一个DBA这很不合格。但在今天的文章里,我想给你展示下,当你想对特定查询创建索引设计时,你如何把你的工作和思考过程传达给查询优化器。听起来很有意思?嗯,那就进入我的索引调优世界吧!

有问题的查询

我们来看下列查询:

 1 DECLARE @i INT = 999
 2 SELECT
 3     SalesOrderID,
 4     SalesOrderDetailID,
 5     CarrierTrackingNumber,
 6     OrderQty,
 7     LineTotal
 8 FROM Sales.SalesOrderDetail
 9 WHERE ProductID < @i
10  ORDER BY CarrierTrackingNumber
11  GO

如你所见,这里用了一个本地变量与一个不等于谓语来从Sales.SalesOrderDetail表来获取一些记录。当你执行那个查询,看它的执行计划时,你会发现它有一些严重的问题:

  • SQL Server需要扫描Sales.SalesOrderDetail表的整个非聚集索引,因为没有支持的非聚集索引。对这个扫描,查询需要1382个逻辑读,运行时间近800毫秒。
  • 查询优化器在查询计划里引入了筛选器(Filter)运算符,它进行逐行比较用来检查符合的行(ProductID < @i
  • 因为ORDER BY CarrierTrackingNumber,在执行计划里一个排序(Sort)运算符被引入。
  • 排序运算符蔓延到了TempDb,因为不正确的基数计算(Cardinality Estimation)。用了带了本地变量与不等于谓语的组合,SQL Server从表的基数硬码估计30%的行。在我们的情况里估计行数是36395(121317 * 30%)。实际上查询返回120621行,这意味这排序(Sort)运算符必须蔓延到TempDb,因为请求的内存授予太小了。

现在我问你——你能改善这个查询么?你的建议是什么?休息下,想个几分钟。不修改查询本身,你如何改善这个查询? 

我们来调试查询!

当然,我们要做索引相关的调整来改善。没有支持的非聚集索引,那只能是查询优化器唯一可以使用计划来运行我们的查询。但对这个指定查询,什么是好的非聚集索引呢?一般来说,我通过看搜索谓语来考虑可能的非聚集速印。在我们的例子里,搜索谓语如下: 

WHERE ProductID < @i 

我们请求在ProductID列过滤的行。因此我们想在那个列创建支持的非聚集索引。我们建立索引:

1 CREATE NONCLUSTERED INDEX idx_Test ON Sales.SalesOrderDetail(ProductID)
2 GO

在非聚集索引创建后,我们需要验证下改变,因此我们再次执行刚才的查询代码。结果如何捏?查询优化器并没有使用我们刚创建的非聚集索引!我们在搜索谓语上创建了支持的非聚集索引,查询优化器没有引用它?通常人们对此就无辙了。其实我们可以提示查询优化器来使用非聚集索引,来更好的理解“为什么”查询优化器没有自动选择索引:

 1 DECLARE @i INT = 999
 2
 3 SELECT
 4     SalesOrderID,
 5     SalesOrderDetailID,
 6     CarrierTrackingNumber,
 7     OrderQty,
 8     LineTotal
 9 FROM Sales.SalesOrderDetail WITH (INDEX(idx_Test))
10 WHERE ProductID < @i
11  ORDER BY CarrierTrackingNumber
12  GO

当你现在看执行计划时,你会看到下列的野性——一个并行计划:

 

查询花费了370109个逻辑读!运行时间基本和刚才的一样。这里到底发生了什么?当你仔细看执行计划,你会发现查询优化器引入了书签查找,因为刚才创建的非聚集索引,对于查询来说,不是一个覆盖非聚集索引。查询越过了所谓的临界点(Tipping Point),因为我们用当前的搜索谓语来获得几乎所有行。因此用非聚集索引和书签查找来组合没有意义。

不去想为什么查询优化器不选择刚才创建的非聚集索引,我们已经把自己的思路表达给了查询优化器本身,通过查询提示进行了询问了查询优化器,为什么非聚集索引没被自动选择。如我刚开始说的:我不想考虑太多。

使用非聚集索引解决这个问题,在非聚集索引的叶子层,我们必须对从SELECT列表的请求的额外列进行包含。你可以再次看下书签查找来看下在叶子层哪些列当前丢失: 

  • CarrierTrackingNumber
  • OrderQty
  • UnitPrice
  • UnitDiscountPrice 

我们重建那个非聚集索引: 

1 CREATE NONCLUSTERED INDEX idx_Test ON Sales.SalesOrderDetail(ProductID)
2 INCLUDE (CarrierTrackingNumber, OrderQty, UnitPrice, UnitPriceDiscount)
3 WITH
4 (
5     DROP_EXISTING = ON
6 )
7 GO

我们已经做出了另1个改变,因此我们可以重新运行了查询来验证下。但是这次我们不加查询提示,因为现在查询优化器会自动选择非聚集索引。结果如何捏?当你看执行计划时,索引现在已被选择。

SQL Server现在在非聚集索引上进行了查找操作,但在执行计划里我们还有排序(Sort)运算符。因为基数计算30%的硬编码,排序(Sort)还是要蔓延到TempDb。偶滴神!我们的逻辑读已经降到了757,但运行时间还是近800毫秒。你现在应该怎么做?

现在我们可以尝试在非聚集索引的导航结构直接包含CarrierTrackingNumber列。这是SQL Server进行排序运算符的列。当我们在非聚集索引直接加了这列(作为主键),我们就物理排序了那列,因此排序(Sort)运算符应该会消失。作为积极的副作用,也不会蔓延到TempDb。在执行计划里,现在也没有运算符关心错误的基数计算。因此我们尝试那个假设,再次重建非聚集索引:

1 CREATE NONCLUSTERED INDEX idx_Test ON Sales.SalesOrderDetail(CarrierTrackingNumber, ProductID)
2 INCLUDE (OrderQty, UnitPrice, UnitPriceDiscount)
3 WITH
4 (
5     DROP_EXISTING = ON
6 )
7 GO

从索引定义可以看到,现在我们已经对CarrierTrackingNumber和ProductID列的数据物理预排序。当你再次重新执行查询,在你查看执行计划时,你会看到排序(Sort)运算符已经消失,SQL Server扫描了非聚集索引的整个叶子层(使用剩余谓语(residual predicate)作为搜索谓语)。

这个执行计划并不坏!我们只需要763个逻辑读,现在的运行时间已经降至600毫秒。和刚才的相比已经有25%的改善!但是:查询优化器建议我们一个更好的非聚集索引,通过缺少索引建议(Missing Index Recommendations)!暂且相信下,我们创建建议的非聚集索引:

1 CREATE NONCLUSTERED INDEX [SQL Server doesn't care about names, why I should care about names?]
2 ON [Sales].[SalesOrderDetail] ([ProductID])
3 INCLUDE ([SalesOrderID],[SalesOrderDetailID],[CarrierTrackingNumber],[OrderQty],[LineTotal])
4 GO

当你现在重新执行最初的查询,你会发现令人惊讶的事情:查询优化器使用“我们”刚才创建的非聚集索引,缺少索引建议已经消失!

你刚刚创建了SQL Server从不使用的索引——除了INSERT,UPDATE和DELETE语句,SQL Server都要去维护你的非聚集索引。对于你的数据库,你刚创建了“单纯”浪费空间的索引。当另一方面,你已经通过消除丢失索引建议,满足了查询优化器。但这不是目的:目的是创建会被再次使用的索引。

结论:永不相信查询优化器!

小结

今天的文章有点争议性,但我想你向你展示下,但你在创建索引时,查询优化器如何帮助你,还有查询优化器如何愚弄你。因此做出小的调整,就立即运行你的查询,验证改变非常重要。还有当你使用来查询优化器的缺少索引建议时,请考虑下这个建议是个好的么。额,我已经说过——我会直接咔嚓掉。呵呵~~~

感谢关注!

时间: 2024-11-03 22:26:30

我如何调优SQL Server查询的相关文章

如何调优SQL Server查询_MsSql

在今天的文章里,我想给你展示下,当你想对特定查询创建索引设计时,如何把你的工作和思考过程传达给查询优化器.下面就一起来探讨一下吧! 有问题的查询我们来看下列查询: DECLARE @i INT = 999 SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, LineTotal FROM Sales.SalesOrderDetail WHERE ProductID < @i ORDER BY Carri

如何调优SQL Server查询

在今天的文章里,我想给你展示下,当你想对特定查询创建索引设计时,如何把你的工作和思考过程传达给查询优化器.下面就一起来探讨一下吧! 有问题的查询 我们来看下列查询: DECLARE @i INT = 999 SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, LineTotal FROM Sales.SalesOrderDetail WHERE ProductID < @i ORDER BY Carr

调优SQL过程

您想学习调优 SQL PL 的一些技巧吗?如果是这样的话,本文将描述一些调优 IBM DB2 Universal Database(UDB)V8.2 for Linux,UNIX, and Windows 中的 SQL 过程的常见例子,并将特别关注从其他数据 库管理系统中移植过来的过程.动机 SQL 过程语言(SQL PL)是许多 DB2 开发人员工具箱中的一个流行工具.当将应用程序 从 Oracle.Sybase 或 Microsoft SQL 服务器迁移或移植到 DB2 中时,更是如此.然而

Sql Server 查询性能优化之走出索引的误区分析_MsSql

据了解绝大多数开发人员对于索引的理解都是一知半解,局限于大多数日常工作没有机会.也什么没有必要去关心.了解索引,实在哪天某个查询太慢了找到查询条件建个索引就ok,哪天又有个查询慢了,再建立个索引就是,或者干脆把整个查询SQL直接发给DBA,让DBA直接帮忙优化了,所以造成的状况就是开发人员对于索引的理解.认识很局限,以下就把我个人对于索引的理解及浅薄认识和大家分享下,希望能解除一些大家的疑惑,一起走出索引的误区 误区1.在表上建立了索引,在查询时用到了索引的列,索引就一定会生效 首先明确下这样的

分析Sql Server查询性能优化之走出索引的误区

误区1.在表上建立了索引,在查询时用到了索引的列,索引就一定会生效 首先明确下这样的观点是错误的,SQL Server查询优化器是基于开销进行选择的优化器,通过一系列复杂判断来决定是否使用索引.使用什么类型索引.使用那个索引.SQL Server内部维护着索引列上的数据的统计,统计信息会随着索引列内容的变化而变化,索引的有效期完全取决于索引列上的统计信息,随着数据的变化关于索引的检索机制也随之变化.对于查询优化器来说始终保持查询开销最低始终是其的不二选择,如果一个非聚集索引的列上有大量的重复值,

Sql Server 查询性能优化之走出索引的误区分析

据了解绝大多数开发人员对于索引的理解都是一知半解,局限于大多数日常工作没有机会.也什么没有必要去关心.了解索引,实在哪天某个查询太慢了找到查询条件建个索引就ok,哪天又有个查询慢了,再建立个索引就是,或者干脆把整个查询SQL直接发给DBA,让DBA直接帮忙优化了,所以造成的状况就是开发人员对于索引的理解.认识很局限,以下就把我个人对于索引的理解及浅薄认识和大家分享下,希望能解除一些大家的疑惑,一起走出索引的误区 误区1.在表上建立了索引,在查询时用到了索引的列,索引就一定会生效 首先明确下这样的

SQL Server 查询分析器快捷键集合

下表列出 SQL Server 查询分析器提供的所有键盘快捷方式. 活动 快捷方式 书签:清除所有书签. CTRL-SHIFT-F2 书签:插入或删除书签(切换). CTRL+F2 书签:移动到下一个书签. F2 功能键 书签:移动到上一个书签. SHIFT+F2 取消查询. ALT+BREAK 连接:连接. CTRL+O 连接:断开连接. CTRL+F4 连接:断开连接并关闭子窗口. CTRL+F4 数据库对象信息. ALT+F1 编辑:清除活动的编辑器窗格. CTRL+SHIFT+DEL 编

T-SQL命令在SQL Server查询中的运用

server 首先需要说明的是这篇文章的内容并不是如何调节SQL Server查询性能的(有关这方面的内容能写一本书),而是如何在SQL Server查询性能的调节中利用SET STATISTICS IO和SET STATISTICS TIME这二条被经常忽略的Transact-SQL命令的. 从表面上看,查询性能的调节是一件十分简单的事.从本质上讲,我们希望查询的运行速度能够尽可能地快,无论是将查询运行的时间从10分钟缩减为1分钟,还是将运行的时间从2秒钟缩短为1秒种,我们最终的目标都是减少运

Microsoft SQL Server 查询处理器的内部机制与结构

摘要:本文介绍了在客户机上处理 Microsoft SQL Server 查询的方式,各种客户机与 SQL Server 的交互方式,以及 SQL Server 在处理客户机程序的请求时需要完成的工作. 简介 Microsoft(R) SQL Server(TM) 内部机制和结构是一个非常大的主题,因此本文仅限于程序开发人员感兴趣的问题,集中研究其他源中没有彻底讨论的问题.在讨论 SQL Server 的结构时,我们主要观察客户机的处理过程,研究不同的客户机程序与 SQL Server 的交互方