SQL语句优化流程

确认是否因为做了物理IO而导致的性能不佳

需打开set statistics io on 和 set statistics time on以后再运行语句才能得到

set statistics io on

GO

set statistics time on

GO

SQL 语句

go

检查set statistics io on的输出，

如果“physical reads”和“read-aheadreads","lob physical reads”和"lob read-ahead reads"都是0。这意味着语句完全没有做物理的I/O。

如果不为0：

(1)检查生产服务器是否有内存瓶颈，是否存在经常换页的现象。

(2)检查这句话，和它访问的数据，是被经常使用的，还是偶尔使用的。

(3)检查语句执行计划，是否能够减少其访问的数据量。

(4)检查磁盘子系统的性能。

是否是因为编译时间长而导致性能不佳

一般管理员需要对两类语句重点检查编译时间。一种是比较简单，长度比较短，涉及表格比较少，但是在应用或任务里反复调用的语句。比较简单的语句执行起来会比较快。如果一句话执行只要100毫秒，编译也要100毫秒，那SQL Server花这么多时间编译似乎有点不划算。如果能够通过执行计划重用来去除编译时间，或者通过调整数据库设计来降低编译时间，那整体的效率就能够提高40%～50%。如果语句只执行一遍，从200毫秒降低到100毫秒似乎意义不大。但是，如果一个任务要调用同样的语句成千上万遍，效果就能很明显了。还有一类，是语句本身比较复杂，或者其所基于的表格上有太多的索引可供选择，使得编译时间就超过1秒，甚至更长。

set statistics time on

1.CPU time。

这个值的含义指的是在这一步，SQLServer所花的纯CPU时间是多少。也就是说，语句花了多少CPU资源。

2.elapsed time。

此值指这一步一共用了多少时间。也就是说，这是语句运行的时间长短。

3.SQL Server parse and compile time。

这一步，就是语句的编译时间。由于语句运行之前清空了所有执行计划，SQL Server必须要对它编译。这里的编译时间就不为0了。由于编译主要是CPU的运算，所以一般CPU time和elapsed time是差不多的。如果这里相差比较大，就有必要看看SQLServer在系统资源上有没有瓶颈。

1. SQL Server Execution Times。

语句真正运行的时间。由于语句是第一次运行，SQL Server需要把数据从磁盘读到内存里，这里语句的运行发生了比较长的I/O等待。

如果你发现语句性能问题和编译有关系，须考虑的方向有：

(1)检查语句本身是否过于复杂，长度太长。

如果是的，可以考虑把一句话拆成几句更简单的语句，或者用temp table来替代大的“in”子句。

(2)检查语句使用的表格上是不是有太多的索引。

索引越多，SQL Server要评估的执行计划就越多，花的时间越长。作为一个设计严谨的数据库，要把没有用的索引及时删除。

(3)引导SQLServer尽量多重用执行计划，减少编译。

判断执行计划是否合适

预估cost的准确性

在Set statistics profile on里，

它是根据EstimateRows、AvgRowSize和每一步要做的事情，估算出EstimateIO和EstimateCPU,然后再根据这两个值算出TotalSubtreeCost。SQLServer在候选的执行计划中，挑一个它算出来TotalSubtreeCost最低的。而一般AvgRowSize是不会估错的，所以，如果SQLServer选择的执行计划有问题，常常是因为EstimateRows估错。

因此判断每一个子句的TotalSubtreeCost是否准确，常常就转化为检查EstimateRows和真实Rows的差别。这两个数据，可以通过set statistics profile on的输出获得，也可以在SQL Trace里的ShowplanStatistics Profile事件里得到。

需要说明的是，当SQL Server预估某一步不会有记录返回时，它不是把EstimateRows置为0,而是置为1。所以EstimateRows等于1,常常是意味着SQLServer认为该步骤不会有记录返回。如果实际的Rows不是0,也不是1,而EstimateRows等于1,那就要好好检查SQL Server在这里的预估开销是否准确，是否会影响到执行计划的准确性。

是Index Seek还是Table Scan

一般来讲，如果检索返回的数据量，占整个表格的数据量比较小，那么用seek还是划算的。所以常常说，seek会比scan要好。但是，如果检索返回的数据量差不多是整个表格的数据的一大部分，那么索引上的seek不会有什么帮助，甚至直接用scan可能会更快一些。所以，也不是scan就绝对地比seek要差，不能看到scan就要想办法变成seek。关键还是要看EstimateRows和实际Rows的大小。

因此，用户还是要比较实际返回行数和表格的整体行数，具体看，到底在语句的上下文下，是scan好，还是seek好。

是Nested Loops还是Hash (Merge) Join

SQLServer的三种Join方式，Nested Loops比较适合于联接的双方结果集比较小的情况，而Hash(Merge)Join适合结果集比较大的情况。做这么详细的介绍，是因为很多SQL Server没有选对执行计划，而导致的性能问题，都是因为误选了Nested Loops导致的。当Outer Table比较大时，使用Nested Loops方法，Inner Table会被Loop很多次，导致执行复杂度急剧增加。

一般来说，如果看到一个执行计划的Executes值很大，又和一个Nested Loops相关，那就应该好好研究一下这个Nested Loops是否合适。

Filter运算的位置

在一句查询里，常见的是几个表格做联接，同时又有一些Where子句filter掉一些记录。那么是先filter掉记录，再做联接好呢，还是先做联接，再filter呢?两种方法都能计算出正确的结果。但一般来讲，先filter掉一些记录，使得做联接的记录集小一点，会大大降低联接的消耗。所以filter先做，会提高查询的效率。在检查执行计划的时候，用户也要看看，是不是SQLServer及时做了filter。

确认问题产生的原因

(1)预估返回结果集大小(EstimateRows)不准确，导致执行计划实际TotalSubtreeCost比预估的高很多。

统计信息(statistics)不存在，或者没有及时更新，是产生这个问题的主要原因。图13-6的那个例子，就是这个原因导致。应对的方法，是开启数据库上的auto create statistics和auto update statistics。如果这样还不能保证statistics的精确性，可以定义一个任务，定期更新统计信息。

子句太过复杂，也可能使SQL Server猜不出一个准确的值，只好猜一个平均数。比如where子句里对字段做计算、代入函数等行为，都可能会影响SQL Server预估的准确性。如果发现这种情况，就要想办法简化语句，降低复杂度，提高效率。

当语句代入的变量值是一个参数，而SQL Server在编译的时候可能不知道这个参数的值，只好根据某些规则，“猜"一个预估值，这也可能会影响到预估的准确性。这个话题，会在下一节“ParameterSinffing"里做详细介绍。

(2)语句重用了一个不合适的执行计划。

SQL Server的执行计划重用机理，是一次编译，多次重用。根据代入的第一个参数值进行编译，以后不管参数值是多少，都重用根据前面的那个值编译出来的执行计划。这对一些数据分布比较均匀的表格是没有问题的，例如我们的SalesOrderHeader_test。不管你带入什么值，返回的结果集数量都差不多。

但是，有些表格的数据分布不均匀，例如前面使用的SalesOrderDetail_test。它在某些值上，重复的记录很少，但是在另外一些值上，重复的记录又很多。这导致了对于不同的值，SQL Server必须使用不同的执行计划才能达到最优的效率。如果重用的执行计划不合适，就会出现性能问题。

(3)筛选子句写得不太合适，妨碍SQLServer选取更优的执行计划。

当语句要筛选(filter)掉一些记录时，索引会帮上忙。一般来讲，筛选动作做得越早，越能提高效率。SQLServer对筛选条件(search argument/SARG)的写法有一定的建议。

SARG运算符包括=、>、<、>=、<=、IN、BETWEEN,有时还包括LIKE(在进行前缀匹配时，如LIKE John%')。SARG可以包括由AND联接的多个条件。SARG不但可以是匹配特定值的查询，

对于不使用SARG运算符的表达式，索引是没有用的，SQLServer对它们很难使用比较优化的做法。非SARG运算符包括NOT、<>、NOT EXISTS、NOTIN、NOT LIKE和内部函数，例如Convert、Upper等。

Parameter Sniffing

当使用存储过程的时候，总是要使用到一些变量。变量有两种，一种是在存储过程的外面定义的。当调用存储过程的时候，必须要给它代入值。这种变量，SQLServer在编译的时候知道它的值是多少。

还有一种变量是在存储过程里面定义的。它的值是在存储过程的语句执行的过程中得到的。所以对这种本地变量，SQLServer在编译的时候不知道它的值是多少。

SQL Server在处理存储过程的时候，为了节省编译时间，是一次编译，多次重用的。用sp_executesql的方式调用的指令也是这样。那么计划重用就有两个潜在问题。

(1)对于第一类变量，根据第一次运行时代入的值生成的执行计划，是不是就能够适合所有可能的变量值呢?

(2)对于第二类本地变量，SQLServer在编译的时候并不知道它的值是多少，那怎么选择“合适”的执行计划呢?

对于因为重用他人生成的执行计划而导致的“水土不服”的现象，SQL Server有一个专有名词，叫"Parameter Sniffing"。"Parameter Sniffing"是因为语句的执行计划对变量的值很敏感，而导致重用执行计划会遇到性能问题。

Parametre Sniffing的解决方案

1.用EXEC()的方式运行动态SQL语句。

如果在存储过程里不是直接运行语句，而是把语句带上变量，生成一个字符串，再让Exec()这样的命令做动态语句运行，那SQL Server就会在运行到这句话的时候，对动态语句进行编译。这时SQLServer已经知道了变量的值，会根据值生成优化的执行计划，从而绕过了parameter sniffing的问题。

2.使用本地变量(local variable)。

在前面13.4.2节里，提到了如果把变量值赋给一个本地变量，SQL Server在编译的时候是没办法知道这个本地变量的值的。所以它会根据表格里数据的一般分布情况，“猜测”一个返回值。不管用户在调用存储过程的时候代入的变量值是多少，做出来的执行计划都是一样的。而这样的执行计划一般比较“中庸”,不会是最优的执行计划，但是对大多数变量值来讲，也不会是一个很差的执行计划。存储过程Sniff2就是这样一个例子。

这种方法的好处，是保持了存储过程的优点，缺点是要修改存储过程，而且执行计划也不是最优的。

3.在语句里使用query hint,指定执行计划。

在SELECT、INSERT、UPDATE、DELETE语句的最后，可以加一个“Option(<query_hint>)”的子句，对SQLServer将要生成的执行计划进行指导。当数据库管理员知道问题所在以后，可以通过加hint的方式，引导SQLServer生成一个比较安全的，对所有可能的变量值性能都不差的执行计划。现在SQL Server的query hint还是很强大的，有十几种hint。

这些hint的用途不一样。有些是引导执行计划使用什么样的运算的，比如，{ HASH I ORDER }GROUP,{ CONCATIHASHI MERGE}UNION,{LOOPI MERGEIHASH}JOIN。有些是防止重编译的，例如PARAMETERIZATION {SIMPLEIFORCED },KEEP PLAN,KEEPFIXED PLAN。有些是强制重编译的，如RECOMPILE。有些是影响执行计划的选择的，如FAST number_rows,FORCE ORDER,MAXDOPnumber_of_processors,OPTIMIZE FOR(@variable_name =literal_constant[,..n])。所以它们适合在不同的场合。具体的定义，还要参见SQLServer的联机帮助。

2.指定Join运算({LOOPIMERGE IHASH}JOIN)。

很多时候，parameter sniffing的问题是由于SQL Server对一个改用MergelHash Join的情况误用了Nested Loops。确定了问题语句后，当然可以用查询提示，指定语句里所有join的方法。但是这种方法一般很不推荐，因为不是所有的join,SQLServer都能够根据你给的提示做出来执行计划的。如果对例目 L66接受这个查询提示。

3.OPTIMIZE FOR(@variable_name = literal_constant[,...n])

当确认了Parameter Sniffing问题后，会发现，根据某些变量值生成的执行计划，快和慢会相差很大，而根据另外一些变量生成的执行计划，性能在好和坏的时候，相差并不很大。例如当变量等于50000的时候，它用最好的Nested Loops执行计划，用时十几毫秒，用Hash Join的那个执行计划，也不过300多毫秒。但是变量等于75124的时候，Hash Join执行计划需要500多毫秒，用Nested Loops的时候，要用4000多。从绝对值来讲，让用户等几百毫秒一般是没问题的。但是等上几秒钟，就容易收到抱怨了。所以Hash Join是一个比较“安全”的执行计划。如果SQLServer总是使用75124这个值做执行计划，会对大部分查询都比较安全。

这种方法的优点是，既能够让SQL Server做出有倾向性的执行计划，又能够保证SQL Server选择执行计划时候的自由度，所以得到的执行计划一般是比较好的。相对于用join hint,这个方法更精细一些。缺点是，如果表格里的数据分布发生了变化，比如用户有一天把75124的记录全删了，那SQLServer选择的执行计划就不一定继续正确了。所以它也有它的局限性。

1. Plan Guide

调整数据库设计来优化语句性能

当一个语句出现性能问题，而且已经排除了系统资源瓶颈、阻塞与死锁、物理I/O、编译与重编译Parameter Sniffing这些因素以后，那所能做的，要不就是调整数据库设计，提高语句性能，要不就是修改语句本身，以达到更高的效率。

调整数据库的设计基本是两个思路：调整索引，或者使用Plan Guide。这两个方法都不用修改语句本身。\

调整索引

索引设计的优劣很大程度上决定了数据库的性能，也决定了一套系统的最大能力。运气比较好的时候，一套数据库系统性能本来很差，可加一两个索引后就会有很大的改善。所以做语句调优，调整索引是必备的功课。

Clustered Index(聚集索引)。

聚集索引是一个表格里最宝贵的资源。一张表格只能有一个聚集索引，它决定了表格里所有记录排列的顺序。如果一张表经常用某种顺序检索数据，那按照这个顺序建立聚集索引无疑是最理想的。为了提高可检索性，聚集索引最好要建立在重复值很少的字段上。但是并不是说，聚集索引的字段就不可以有重复值。选择什么样的字段，值重复不重复是一个标准，表格的常见检索方式也是一个重要的标准。例如一个Employee表格里，有姓名字段，也有工号字段。一般来讲，姓名可能偶尔会有重复，但是工号不会重复。如果我们对Employee表格的检索，通常只用姓名，很少使用工号，那就应该选姓名作为聚集索引。如果检索经常使用的是工号，不是姓名，就应该用工号作聚集索引。

Nonclustered Index(非聚集索引)。

一个表格会有多种检索方式。例如Employee,不但会按照姓名、工号，还会按照年龄段、性别、职务等方面来检索。一个聚集索引是无法覆盖所有要求的，需要有非聚集索引来辅助。在一张表格上，可以有多个非聚集索引。

建立非聚集索引的字段，不用一定要很少不重复。像性别、职务这样的字段，肯定会有很多重复记录，但是为了提高检索性能，也可以在上面建立非聚集索引。但是非聚集索引里面没有存储记录的所有信息。如果查询要使用的字段没有完全包含在非聚集索引里，那SQL Server就要做BookmarkLookup。这会有额外的开销。

Covering Index(复合索引/涵盖索引)。

一个索引可以建立在一组字段上。这样的好处有：

(1)查询的检索条件，本身是一组字段的组合。直接在上面建索引，检索更快。

(2)如果查询用到的字段索引都能够涵盖，那就不用做Bookmark Lookup,能省时间。但是设计Covering Index的时候需要注意的是：

(1)要慎重选择索引的第一个字段，最好选择一个重复记录最少的字段。这是因为索引上的统计信息只保存第一个字段的数据直方图。如果选一个重复数据很多的字段，这个索引的可选度就比较低了，会影响索引的价值。

(2)SQL Server会按照索引里字段的组合值，建立B-树。字段越多，排序的代价就越高，维护这个索引的代价也就越高。所以选取字段的时候要有合理的要求，不能随便什么字段都往上放。一般不要把长度很长的字段放在索引字段里.

Index with Included Columns(具有包含性列的索引)。

有些查询语句根据一个字段的条件，查询另一个字段的值。例如，返回所有职务是“Manager”的人姓名。

Select name from Employee Where Title ='Manager'

如果建一个covering index在(Titile+ name)上，数据会按照(Title+name)的方式排序。其实不需要这种排序，我只需要按Title的排序。在Name上的排序是多做的。在SQLServer 2005以后，就可以建立一个有包含性列(Included Columns)的索引。

Create index Employee_Title On Employee (Title) Include (name)

这样的索引，既可以节省Bookmark Lookup的消耗，也不需要做不必要的排序。是covering index的一种替代方式。

Indexed View(索引视图)。

视图只是一个逻辑定义，它里面并没有存储任何数据。对它的查询会转向到对它所基于的表格的查询。如果一个视图的定义比较复杂，那么对它的查询会比较耗时。

为了提高视图操作的性能，SQL Server允许在一些视图上建立索引。索引里的字段值会被重新存放一份。如果索引能够覆盖查询要使用到的所有字段，那查询就不必去访问基础表格。可以大大提高检索速度。所以这是一个提高和视图相关的语句性能的好办法。但是SQLServer对索引视图有限制。不是所有的视图都能建索引的。视图要符合一定的先决条件。

Primary Key(主键)。

很多人会把Primary Key和聚集索引搞混起来，或者认为这是一个东西。这个概念是非常错误的。主键是一个约束(constraint),它依附在一个索引上，这个索引可以是聚集索引，也可以是非聚集索引。所以在一个(或一组)字段上有主键，只能说明它上面有个索引，但不一定就是聚集索引。而一个聚集索引里，是可以有重复值的，只要它没有被同时设为主键。

强调这一点，是因为有些用户觉得自己的表格上设置了主键，就认为表格上有聚集索引，按照B-树的方式管理了。如果没有指定主键是个聚集索引，可能表格还是会以堆的方式管理，效率低下。

那面对一个性能不理想的语句，怎么调整索引，才能提高性能呢?

当确认EstimateSubtreeCost这一列是准确的以后，应该找对cost贡献最多的子句。如果它用的是Table Scan,或者Index Scan,请比较它返回的行数和表格实际行数。如果返回行数远小于实际行数，那就说明SQL Server没有合适的索引供它做Seek。这时候加索引就是一个比较好的选择。

SQL Server提供了两种“自动”功能

Missing Index。

在SQLServer对任何一句语句作编译的时候，都会去评估一下，这句话是不是缺少什么索引的支持。如果它认为是，它还会预估，如果有这么一个索引，它的性能能提高多少。对单条语句，这个信息可以通过SQLTrace里的Performance-Showplan XML Statistics Profiles这个事件得到(见图13-40)。

在Trace里Performance-Showplan XML Statistics Profiles这个事件的内容会以一个执行计划图显示，我们想要的信息看不到。你需要选择这个事件，右键点击，选择Extract Event Data,把数据输出到一个.SQLPlan结尾的文件里(见图13-41)。

SQL Server有一个动态管理视图sys.dm_db_missing_index_details,记录了当前数据库下所有的missing index的信息。它针对的是SQLServer从启动以来所有运行的语句，而不是针对某一个查询。管理员也可以看看，哪些表格SQLServer是最有“意见”的(见图13-42)。

Database Engine Tuning Advisor (DTA)

这也是一个非常好用的工具，强力向大家推荐。在Management Studio里选择Tools-Database Engine

Tuning Advisor,就可以开启这个工具。

使用DTA须注意的要点是：

(1)每次分析的输入量要合理。

最好是已经找出的有问题的语句，把它们保存在.sql文件里，作为DTA的输入。切忌抓了个很大的SQLTrace文件，直接当成DTA的输入。这样DTA会运行很久。给的建议针对性也不强。

(2)最好不要在生产数据库上直接运行DTA。

DTA在运行的过程中，会在数据库上建立一些临时对象，测试自己的建议是不是有效。所以不但会对系统资源造成负担，也会修改数据库。在分析接近结束的时候，DTA会删除这些临时对象。但是，如果DTA没有正常结束，或者用户强制终止了DTA的运行，那这些临时对象会遗留在数据库里。这有可能会影响以后数据库的正常运行。

(3)DTA给的建议，要经过确认以后，才能在数据库上实施。

和Missing Index一样，DTA给出的建议也有其片面性。在实施之前，要做整体评估，不能草率地直接实施。

一般来讲，DTA给的建议质量还是比较高的。如果一个语句DTA也给不出建议，那通过人工调优能找出好索引的几率也不是很大。在这种情况下，最好的出路是理解语句的逻辑，将它改写成一个更简单，更合理的形式。也就是说，要通过修改语句设计来优化性能了。

SQL Server Plan Guide(计划指南)

前面讨论Parameter Sniffing问题的时候，曾经提到在SQL Server 2005以后，用户可以给一些语句定义计划指南，让它们在编译的时候，自动使用你想要的执行计划。这是提高语句性能在SQL Server层面上可以做的另外一件事情。但是，使用这个方法要满足几个前提。

(1)问题语句固定，你已经知道了它们的格式，而且应用程序就一直使用这种格式。

在指定执行计划指南的时候，需要告诉SQL Server完整的语句。当SQL Server发现任何一个用户将要运行同样的一句话时，就会应用计划指南。如果问题语句是动态生成的，每一次格式都不一样的话，那计划指南可能就不那么有用。

(2)语句的性能问题可以通过选用一个固定的执行计划得到解决。

也就是说，存在一个“万能”的执行计划，它对任何参数值、任何用户，其执行效率都是稳定可接受的，而且也不会随着数据量的变化，执行效率变得不可接受。如果你找不到这样一个执行计划，那就没有办法使用这个方法。

(3)使用这种方法后，需要有人不断监视语句的性能。

如果数据量或数据分布发生很大的变化，可能先前好的执行计划现在反而会导致更大的问题。需要数据库管理员能够知道计划指南设置的原理，在必要的时候做相应的调整。创建计划指南的语句是：

定义看起来有点晦涩。简单来说，用户可以给一个batch里的一句话定义计划指南，也可以给一个存储过程里的一句话定义。这句话可以有参数，也可以没有参数。计划指南可以是一个Query Hint,也可以直接是一个执行计划。

计划指南这个功能，在急需调整执行计划、数据库和应用设计又不能修改的时候，能起到应急的作用，还是值得数据库管理员掌握的。但是和使用Query Hint的局限性一样，计划指南更多用作短期的解决方案，长期来讲可能会有风险。数据变化后，执行指南可能就不再准确。

调整语句设计提高性能