SQL Server性能优化

huhahapz

<iframe marginwidth="0" marginheight="0" src="http://218.16.120.35:65001/PC/Global/images/b.html" frameborder="0" width="728" scrolling="no" height="90"></iframe>
一、概述
在数据库应用中，速度是一个永恒的话题。有许多因素会影响数据库的性能表现，例如：操作系统，硬件方面的因素,如内存和磁盘空间，访问数据库的应用软件。除此之外，数据库本身的设计也是一个影响性能的重要因素。
本文要讨论的是我们可以采取哪些措施提高SQL Server数据库的性能。讨论的焦点主要集中在SQL Server 2000为视图创建索引的能力，以及如何使用Index Tuning Wizard（ITW，索引调整向导）优化索引。另外，我们还要讨论如何确保查询充分地利用了索引以及数据库统计信息。
性能问题应该在数据库设计的初始阶段就开始考虑。不过，即使在数据库正式开始运行之后，我们仍旧可以进行一些修改，这些修改将显著地影响数据库的性能表现。索引是一种优化数据查询和排序操作的数据库实体，正确配置的索引能够使数据库查询或更新数据所需要的时间发生巨大的变化，ITW能够帮助我们确定如何在数据库中配置索引以获得最佳的性能。
我们可以让ITW根据指定的Workload（工作负荷）推荐最佳的数据库索引配置。Workload是保存到外部文件的SQL脚本或跟踪结果。ITW的建议是根据给定Workload而提出的最优化建议，因此事先准备合适的基础数据非常重要。
为ITW创建工作负荷文件最简单、最全面的方法或许应该是保存由SQL Profiler创建的跟踪。SQL Profiler是自SQL Server 7.0开始提供的新工具。它能够用指定的过滤器和条件，记录服务器的活动情况。使用SQL Profiler为ITW创建跟踪时，应当确保跟踪已经记录了典型的数据库活动。换句话说，应当选择一个数据库负载不是特别繁重、也不是特别轻松的时段进行跟踪。至于跟踪要运行多少长时间，这由系统本身的特点决定。有些时候，我们可能只需跟踪数小时就可以得到系统典型活动情况的记录；有些时候，我们可能要让跟踪持续几天，才能记录下数据库中所有典型的活动情况。
二、索引调整向导
准备好工作负荷文件之后，我们就可以在Enterprise Manager的树形视图中选择服务器启动ITW。从Tools菜单选择Wizards，在树形视图中找到Management节点，选择Index Tuning Wizard，此时我们就可以看到ITW的欢迎屏幕。
ITW的第二个屏幕让我们指定要分析的是哪一个服务器和数据库。在这个屏幕上，我们还有另外两个选项：Keep All Existing Indexes（保留现有索引），Perform Thorough Analyses（进行完全分析）。清除Keep All Existing Indexes选项使得ITW能够提出最优索引建议，但此时ITW可能建议删除或者修改现有的某些索引。如果你不想修改现有的索引，请保留这个选项的选中状态。选中Perform Thorough Analyses选项使得ITW进行最广泛、深入的分析。虽然进行完全分析可能提高分析结果的质量，但它一般需要较长的时间才能完成；而且完全分析运行时，它很可能导致服务器负载过重。由于这些原因，如果你需要执行完全分析，那么最好在测试服务器上进行，或者在正式提供服务的机器上，选择一个比较空闲的时段进行。
ITW的第三个屏幕让我们指定对哪一个workload进行分析。如果你使用的是SQL Profiler创建的文件，请选择My Workload File选项按钮，然后在文件对话框中找到以前保存的跟踪文件。在这个屏幕中，点击Advanced Options命令按钮可以设置一些高级选项。这些选项包括：被推荐的索引可以使用的最大磁盘空间总量，工作负荷文件中查询取样的最大数量。
在第四个屏幕中，我们可以指定ITW应该对哪个或者哪些表的索引提出建议。只选择那些相关的表有利于节省时间，而且它有助于我们把注意力集中到特定的问题之上。不过，如果要让ITW对整个数据库的优化提出建议，我们应该选中数据库里面所有的表。
ITW的下一个屏幕根据我们设定的条件，显示出它对索引配置的建议（参见图1）。我们可以选择立即执行它提出的建议，或者计划在以后执行，或者把执行脚本保存到外部文件。
http://www.itlearner.com/article/UploadFiles/2005/10301152734.gif
图1
ITW不会对主键或者其他唯一性索引提出建议，也不会对系统表的索引提出建议。ITW的其他局限还包括：在给定的工作负荷中，它分析的索引不能超过32767个；不能对SQL Server 6.5或者更早版本创建的数据库提出索引建议。
注意，ITW是以用户所提供数据的样本为基础提出索引配置建议。由于这个原因，你可能会发现：如果让ITW对同一个工作负荷分析多次，它可能会提出多种不同的索引配置建议。如果ITW不能提出任何建议，它可能是由于下面两种原因之一造成：第一，与数据库中现有的索引配置方案相比，ITW无法提出任何能够进一步提高性能的索引建议；第二；取样的表里面没有足够的数据，无法确定一个合适的索引配置方案。
三、视图索引
SQL Server 2000企业版除了能够创建表的索引，它还能够创建视图的索引。假设我们对Pubs数据库中每一份订单的销售总量感兴趣。下面的代码在Pubs数据库中创建一个名为Quantity_Totals的视图，该视图除了提供上述信息之外，还提供了订单所包含项目的总数：

Use Pubs GO CREATE VIEW Quantity_Totals with SCHEMABINDING AS SELECT ord_num, Total_Quantity = Sum(qty), Total_Items = Count_Big(*) FROM dbo.sales GROUP BY ord_num

COUNT_BIG是SQL Server 2000提供的一个新函数，它的功能与COUNT函数相同。COUNT_BIG和COUNT的不同之处在于，COUNT_BIG返回值是bigint类型，而COUNT返回值是int类型。任何包含GROUP BY子句的视图，如果要使用索引就必须包含COUNT_BIG函数。我们可以用SCHEMABINDING选项创建包含索引的视图。SCHEMABINDING选项是SQL Server 2000的新功能，如果我们指定了SCHEMABINDING选项，视图将被绑定到其基表的模式。
如果视图不包含索引，则数据库中不保存视图返回的结果集。有的时候，我们可能要创建涉及大量记录或必须进行复杂计算的视图，比如要进行聚合分组处理或多重连接操作。如果每次引用这些视图的时候让SQL Server重新生成结果集，数据库开销将非常大。
视图的索引与表的索引在作用方式上非常相似。与表一样，视图可以有一个集簇索引（Clustered Index）和多个非集簇索引。然而，在创建任何非集簇索引之前，我们必须先为视图创建一个唯一性的集簇索引。如果我们创建了视图的集簇索引，数据库将永久保存视图的结果集。虽然创建索引时所保存的结果集只反映当时的数据状态，但任何对基表数据的修改都将自动反应到这个结果集之中。
就象为表创建索引一样，我们可以用CREATE INDEX命令为视图创建索引：

CREATE UNIQUE CLUSTERED INDEX PRIMARY_IDX on Quantity_Totals(ord_num)

上面的代码为我们前面例子中创建的Quantity_Totals视图创建了一个唯一性的集簇索引。创建视图索引不仅能够提高视图的性能，而且有些时候性能的提高达到出乎意料的程度。一旦我们为视图创建并保存了索引，即使对于那些没有直接在FROM子句中引用该视图的查询，Query Optimizer（查询优化器）也可能选择使用该视图索引来提高查询速度。例如，下面这个SQL命令选择出按照订单编号分组的销售数量累计：

SELECT ord_num, Sum(qty) FROM sales GROUP BY ord_num

　　执行这个查询时，Query Optimizer知道：在SQL Server为Quantity_Totals视图创建的索引中，这个销售数量的累计值已经存在。在这种情况下，Query Optimizer会作出这样的判断：如果使用为视图创建的索引，查询的效率将有很大的提高。
　　在为视图创建索引之前，你必须检查视图是否满足创建视图索引的各种条件。下表列出了这些条件中的一部分：

项目	条件
创建视图	创建视图的时候必须指定SCHEMABINDING选项。此外，创建视图时ANSI_NULLS和QUOTED_IDENTIFIER必须设置成ON。
视图引用的表	被视图引用的表应该与视图在同一个数据库中。创建所有被视图引用的表时，ANSI_NULLS必须设置成ON。视图只能引用基表，不能引用其他视图。
视图的SQL命令	视图所包含的SELECT命令不能使用UNION操作符，不能使用任何子查询。另外，它不能包含DISTINCT和ORDER BY关键词，不能使用MIN、MAX和AVG函数。列必须显式引用，SELECT语句不能用*或者table_name.*的方式引用列。

四、影响查询性能的因素
除了数据库的物理设计之外，我们用来保存和提取数据的查询命令的结构也会对性能产生重大影响。内存或者磁盘空间不足之类的硬件问题会降低查询的效率；然而，Query Optimizer不能有效地利用索引或者统计信息也是导致查询性能低劣的主要原因之一。
统计信息由关于列里面数据分布情况的信息构成。查询优化器运用统计信息帮助确定执行查询的最优方法。当我们为一个或者多个列创建索引时，SQL Server将自动生成这些索引列的统计数据。默认情况下，SQL Server的Auto Create Statistics数据库选项设置成True。Auto Create Statistics设置成True的结果是，SQL Server同时为表里面的其他列自动生成统计数据。
SQL Server除了生成初始的统计信息之外，它还会定期地更新这些信息。更新这些信息的频繁程度，由列和索引数据的变化频繁程度以及列和索引的数据规模决定。大多数时候，无论是生成初始的统计信息还是更新这些统计信息，SQL Server都能够高效地完成，我们无需进行任何手工干预。但是，有些时候我们可能会发现，系统不能顺利地为表生成必需的初始统计信息，或者这些统计信息更新不够及时。
我们可以用DBCC SHOW_STATISTICS这个T-SQL命令查看索引列的当前统计信息。这个命令有两个参数，第一个参数指出要查看的是哪一个表的统计信息，第二个参数是索引的名字。例如，如果我们在Query Analyzer（查询分析器）中执行下面这个SQL命令，它将显示出有关Pubs数据库中authors表aunmind索引的统计信息：

DBCC SHOW_STATISTICS (authors, aunmind)

DBCC SHOW_STATISTICS命令返回的结果中包含了SQL Server最后一次更新统计信息的日期和时间，以及SQL Server用来计算统计信息的采样记录数。
http://www.itlearner.com/article/UploadFiles/2005/10301152580.gif
图2
我们可以在Query Analyzer中通过图形用户界面管理非索引列的统计信息，具体操作方法是：启动Query Analyzer，从Tools菜单选择Manage Statistics；在Manage Statistics对话框（图2）中选择要管理哪个数据库以及表或视图的统计信息。
Query Analyzer把所有现有的统计信息放入屏幕底部的列表框。选中一个统计项目，点击Delete或Upate按钮就可以删除或者更新统计信息。如果点击New命令按钮，我们将看到Create Statistic对话框。创建新的统计项目时，我们可以选择的参数包括：要包含哪个或者哪些列，SQL Server生成统计信息时从这些列读入取样数据的百分比，是否要求SQL Server在必要时自动更新统计数据。允许SQL Server自动更新统计信息是一种比较好的选择，因为SQL Server通常都能够高效地完成这一任务，而手工完成却非常麻烦。
除了用Query Analyzer提供的图形用户界面生成、更新统计数据之外，我们还可以用CREATE STATISTICS和UPDATE STATISTICS这些T-SQL命令来完成同样的任务。
五、查询执行计划
要了解查询是否充分利用了索引和统计信息，一种方法是使用Query Analyzer查看查询的执行计划。启动Query Analyzer并装入查询，从Query菜单选择Show Execution Plan、执行查询。在查询结果的下面，我们可以看到一个名为Execution Plan的卡式子窗口。点击Execution Plan即可查看查询的执行计划。
另外，在不实际运行查询的情况下，我们也可以查看执行计划，这时的执行计划是“期望中的执行计划”。从Query菜单选择Display Expected Execution Plan，我们就可以看到一个图，它描述了一个估计的查询执行计划。这个图显示了Query Optimizer为执行查询已经（或者准备）采取的措施。从各个步骤的详细说明中，我们可以了解哪一个步骤最耗时、哪一个步骤占用资源最多，以及Query Optimizer是否使用了索引。
http://www.itlearner.com/article/UploadFiles/2005/10301152621.gif
图3
如果Query Optimizer发现某个表的统计信息已经丢失或者过期，它会把表的标题显示成红色。把鼠标停留到表示表的图标上面，我们就可以从一个弹出式窗口中看到关于该步骤的更多说明，如图3所示。SQL Profiler是一个帮助我们判断哪一个查询导致性能问题并需要进一步分析优化的优秀工具；SQL Profiler能够通过记录服务器的具体活动指出哪些查询和SQL命令效率低下。
总而言之，数据库性能优化涉及到相当广泛的问题。单纯地保证系统有正确的索引和统计信息，对于数据库全面优化来说是不够的。然而，它们是能够显著改善数据库响应时间的重要措施。我们只要稍微花上一点时间，就可以利用SQL Server的工具精心调整查询，使得查询能够充分利用索引和统计信息。