2007-05-24 11:20 | 只看楼主
树型| 收藏| 小 中 大 1

SQLServer怎样使用可用的索引和统计决定执行计划

SQLServer怎样使用可用的索引和统计决定执行计划


--王成辉翻译整理，转贴请注明出自微软BI开拓者www.windbi.com
--原帖地址

作为一个SQLServer的DBA，我的一个任务就是监控我们数据库的性能。我们主要的数据库有1800万用户。基于WEB的应用程序用不同的查询条件查询数据库。当检查这些查询的性能时，大多数都执行得很好。但另一方面，少数查询也超时。

分析超时问题的第一步是找到在运行慢的查询里使用的搜索条件。我发现在查询的字段上建好了索引常常有一个好的响应时间。但当我跟踪这些查询的执行计划时，我发现可用的索引常常没有被用到。

这让我决定研究研究SQLServer是怎样决定查询的执行计划的。通过更好的理解背后的原理，我能改善索引的结构并全面提升有问题查询的性能。 

执行计划

当SQLServer执行一个查询时，它将使用一个存在的执行计划。如果先前没有存在的计划，SQLServer会执行一系列步骤去创建计划。这样做的目的是找到可接受的计划，而不必是最优计划。找到可能最好的计划也许会比执行一个可接受的计划但不是最优计划花费更长的时间。为了达成这个计划，查询优化器要采取下面的步骤。每一步也许会产生一个或多个计划。每个计划的成本汇总为执行的总成本，SQLServer使用成本最少的计划。

  • 首先，SQLServer检查看是否有价值不高的计划可用。如果有，它会使用这个计划。例如，对于使用VALUES子句的INSERT语句，仅有一个可能的执行计划。

  • 如果没有，SQLServer将试图简化查询，以便一个价值不高的计划可用。这样的结果不会产生一个新计划，但有助于SQL分析查询。在这一点上，SQLServer将装载任何一个有助于接下来基于成本的处理的统计。这个基于成本的处理有3个步骤：

    • 首先是事务处理阶段。在此阶段里，SQLServer选出事务处理数据库的典型的简单查询的计划。如果有一个计划的总成本在一个阈值内，将使用这个计划。 

    • 如果找不到一个成本较低的计划，就会启动快速计划阶段。在此阶段里，SQL包括在更复杂查询上经常有用的那些选择。这也许包括索引和嵌套循环连接的使用。如果一个成本足够低的计划被找到，SQLServer将使用该计划。 

    • 最后的阶段是总体优化阶段。在此阶段里，SQLServer比较每一个可能的执行计划，然后选择成本最低的一个。该阶段有时间限制。当超过时间限制时，SQLServer会选择一个到目前为止发现的成本最低的计划。 

统计

除了那个价值不高的计划外，这个过程独立基于统计。所以，坏统计会产生坏执行计划。如果一个小表上有一个索引，最优的计划也许会忽略这个索引而使用表扫描。如果统计随着表显著的增长而没有更新的话，那么SQLServer将仍然假定表是一个小表而使用表扫描，即使这不再是一个最优的计划。

大多数情况下让SQLServer自动更新统计是明智之举。为了让数据库里所有的统计打开自动更新，如果你还没有打开的话，执行下面的语句：

USE master
EXEC sp_dboption 'MyDatabase', ' auto update statistics', 'true'

要为特定的表打开自动更新，则执行下面的语句，如表Clients：
USE MyDatabase
EXEC sp_autostats Clients, 'ON'

要手动更新一个特定表的统计，则执行下面的语句，如表Clients：

USE MyDatabase
UPDATE STATISTICS Clients

其他更新统计的选项会在该网站的其他地方做介绍。 

统计是怎样工作的

为了得到数据库最优的性能，保持统计最新是至关重要的。然而，某些情况下，一个可用的执行计划对给定环境来说不是很理想。为了理解这种情况在什么时候为什么要发生，重要的是理解统计是怎样工作的。

下面这个命令显示了给定索引的统计（表Clients上的索引IX1a）

USE MyDatabase
DBCC SHOW_STATISTICS (Clients, IX1a)

这给出了该索引上的所有统计信息。包括最后更新的日期和时间、密度，并返回索引的样本。该样本是从数据库里随机选取的，具体信息如下：

图1: DBCC SHOW_STATISTICS输出结果

该输出结果的最后一部分是很重要的。基于这些记录，SQL为搜索参数决定预计读取行的数量。该输出结果有以下条目：

RANGE_HI_KEY: 柱状图步骤的上部绑定值。比如：'Smit'

RANGE_ROWS: 位于柱状图步骤内的示例的行数，上部绑定值除外。例如，在'Smit'和'Snijders'之间有66811行。

EQ_ROWS:与柱状图步骤上部绑定值相等的行的数目。例如，值为'Smit'的有48760行。

DISTINCT_RANGE_ROWS: 柱状图步骤内非重复值的数目，上部绑定除外。例如，在'Smit'和'Snijders'之间有462个不重复的值（'Smit'和'Snijders'除外）。

AVG_RANGE_ROWS: 柱状图步骤内每个不重复值的平均数目，上部绑定除外。由RANGE_ROWS / DISTINCT_RANGE_ROWS计算得来。例如，在'Smit'和'Snijders'之间有66811行和462行不重复的值，则AVG_RANGE_ROWS的值为114.61255。

一个对于'Smits'的搜索(between 'Smit' and 'Snijders')的估计执行计划确定下面的数目：

图2: 估计执行计划 - 'Smits'预计行计数

基于这个估计，执行计划将决定下面的步骤。 如果搜索值和统计表匹配，EQ_ROWS的值将是估计行计数，这能在用'Smit'做搜索条件情况下看到：

图3: 估计执行计划- 'Smit'预计行计数

变化

对于姓，每个不重复值的数目是大不相同的。在我们的数据库里，大约有1800万条记录而不同的姓大约有50万。即每个姓大约有36条记录。事实上，一半的姓只有1条记录。另外10万个姓至少每个有10条记录。经常使用的姓大约有9.5万个。产生非常严重的不一致。在这种情况下，使用统计的方式就不是很正确了。

如果另一个搜索参数上也建了索引，SQLServer也许决定不用这个索引，只是使用这个搜索参数去过滤数据。实际上当实际的行计数太高的时候，这个计划不是很理想，也许会导致极端的查询时间。

查询Smit的记录会直接在统计里发现。因此估计的行计数等于实际的行计数48760行。对于查询街道名为'Kerkstraat'的记录也是一样，它有42722行。如果以姓为'Smit'和街道名为'Kerkstraat'作为搜索的话，将使用下面的计划：

图4: 执行计划- 搜索Smit和Kerkstraat

可以看出，最理想的使用由两个可用的索引组成。如果同样查询'Smits'（注意这里比前面的多了一个S）和'Kerkstraat'，估计行计数是144（象上面计算的）而实际行计数是25718（给出的），使用下面的计划：


图5: 执行计划-搜索Smits和Kerkstraat

因为SQLServer预计144行，第二个参数过滤记录的结果预计比使用两个索引更快。然而，实际的行计数高得多，导致这个计划根本不理想。 即使'Smits'的数目大约是'Smit'数目的一半，这个查询执行花费了3分半钟而对'Smit'的查询仅花费了1秒钟。 

逐步解决

那么你怎么解决这个问题呢？统计没有被扩大。他们被设计得很小，以便决定执行计划的过程保持很小。即使统计扩大一倍，这个问题仍然会发生。

唯一有用的选择是使用索引提示，在所有查询的搜索参数所在的字段上使用。添加索引提示是手动进行的，它需要对数据集有所认识。在决定最有用的索引上人和SQLServer自身一样会遭遇相同的困难。在生产环境下自动化索引提示的使用也是耗时和根本没有效率的。

在这些情形下，最好的选择是逐步地解决。通过将特定的索引改成聚集索引，就不必再需要书签查找（bookmark lookup）。这将节省相当可观的时间和努力。如果统计关闭，采样数目超过预计数，所有相关的记录聚集在一起，则需要很少额外的时间。

在上面的例子里，如果在gnaam上有个聚集索引，执行计划会是下面的样子：

图6: 执行计划- 用聚集索引搜索Smits和Kerkstraat

这个查询执行花了7秒钟。有重大的改进。 

索引计划

测试一下将目前的聚集索引改成非聚集索引的效果。一个表只能有一个聚集索引。一般而言，在一个有唯一值的字段上建聚集索引与建非聚集索引之间的性能差异是很小的。然而，如果这个字段用在一对多关系上，那么对于数据库全部查询的性能通常也许会下降。

当为你的数据库设计索引结构时，考虑所有的这些因素是明智的。努力试试不同的配置找出最适合的一个常常是有价值的。当然使用索引调优向导有助于这个过程。
最后编辑拓狼 最后编辑于 2008-08-11 16:06:13
虽有智慧,不如乘势;虽有鎡基,不如待时。
君子学以聚之,问以辨之,宽以居之,仁以行之。
独学而无友，则孤陋而寡闻。