On the Journey to be a SQL Server Professional: SQL Server 2012实施与管理实战指南摘要

第18章语句调优——问题定位与解决方法 776

问题定位思路：

· 确认是否因为做了物理I/O而导致性能不佳

如果一条语句所要访问的数据页面没有事先缓存在内存里，那么数据页面要在语句运行的过程中，从磁盘读到内存里，这是一个很昂贵的动作。如果语句要访问的数据页面比较多，就会产生比较长的时间消耗。

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

大部分情况下，编译时间会远小于运行时间。而且很多时候SQL会重用执行计划，所以语句的第一次执行有编译时间，后面的执行常常就只有运行时间了。

但是，有些语句处理的数据量比较小，可以很快做完，但是语句本身并不很简单。SQL还是要“想一想”才能找到合适的执行计划。这样的语句，其编译时间可能会占总时间的50%甚至更高。

所以对于这类执行总时间比较短，但是在应用程序里会被反复调用的语句，就有必要检查它们编译时间占总时间的比重。如果编译相对比较昂贵，而运行速度又很快，调优的重点会转向如何避免重编译，或者降低编译时间。语句运行调优的空间，往往不是很大。

如果语句要访问的数据都已经缓存在内存里，而且编译时间很短，那么调优就进入如何降低语句运行时间这个比较纯粹的课题了。而这个工作，也要分两步进行。

· 确认SQLSERVER是否正确地预估了每一步的cost，选择了正确的执行计划

SQL是根据EstimateRows的大小来预估cost的。所以简单来讲，要判断一个执行计划是否合适，最重要的就是要判定SQL是不是正确预估了执行计划每一步返回的结果集的大小。如果预估值和实际值相差很多，就要判定它是否造成SQL选择了一个错误的执行计划。

如果这一步SQL也没问题，就说明以现有的表结构和索引，SQL无法做到在预期时间内完成语句运行。这时就进入最后一步，也就是纯粹的语句调优步骤，

· 检查表结构和语句逻辑，确认是否有调优空间，提高语句的运行速度

调整索引，当然是语句调优的重要途径。可以通过执行计划里的每个子句的cost找到最花时间的部分，看看是否可以通过调整索引的方法，提高运行效率。

但是对于另一些语句，有可能无法通过调整索引来达到目的。但是，做同样的业务逻辑可能会有不同的做法。调整表格结构或者语句的写法或者修改数据库或程序设计虽然比较麻烦，但是可能会比光调SQL设置效果好很多。

18.1. 是否是因为做了物理i/o而导致的性能不佳 777

· 用什么办法能知道一条语句的运行有没有做物理I/O呢？如果物理读取”和“预读”不为0，这意味着语句做了物理I/O。

SET STATISTICS IO ON

· 怎样才知道，一条语句在数据提前缓存在内存里的前提下，要运行多久呢？

SET STATISTICS TIME ON

· 如果你在单个语句调优时发现性能问题，只在物理I/O的时候才出现，那需要做的事情有下面几件：

o 检查生产服务器是否有内存瓶颈，是否存在经常换页（page out/page in）的现象

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

如果问题语句经常会被不同的用户调用到，那按道理它说访问的数据应该缓存在内存中。如果这句话只是被某个特定的用户或任务偶尔调用到，而它访问的数据量既大其它人又不经常使用，那SQL没有把它们放在内存里也是正常的。对这样的语句，其运行时间里有物理I/O时间是合理的。这个可能要和最终用户做一下沟通，设定它们合理的性能期望值。并且安排它们在非业务高峰期时运行，以避免影响其它关键业务。

o 检查语句执行计划，是否能减少其访问的数据量

语句访问的数据量越小，要做的物理I/O就越少，当然能够越快。从这个角度做语句本身的调优也是有用的。

o 检查磁盘子系统的性能

如果语句访问的数据很可能就不在内存里，而其数据量也很大，还一定要提高其性能，那唯一的出路就是提高磁盘子系统的性能了。这种情况，SQL内部的设置可能帮不了什么忙。要做的工作可能更多地会在硬件层面。

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

· 需要对两类语句重点检查编译时间。

o 一种是比较简单、长度比较短、涉及表格比较少，但是在应用或任务里反复调用的语句。看看能否通过执行计划重用来去除编译时间，或者通过调整数据库设计来降低编译时间。

o 还有一类，是语句本身比较复杂，或者其所基于的表格上有太多的索引可供选择，使得编译时间超过1秒，甚至更长。

· 有两种方法检查语句的编译时间。

o SET STATISTICS TIME ON - 只能在测试连接里收集，不能收集到应用发过来的语句信息。

o SQL Trace - 也可以收集到语句的编译时间，而且没有限制，可以收集到任何一个连接的信息。虽然没有SET STATISTICS TIME ON的那么精确，但还是可以拿来参考的。

· Demo: 下面用一个存储过程，来模拟一句会有比较长的编译时间的语句。存储过程的主体是一个三张表的查询，其中有一个“in”子句。根据代入参数@i的值，脚本会为其代入相应个数的SaleOrderID值。如果代入的值很多，“in”子句很长，语句本身就变得很长，SQL就要花比较多的时间和资源做编译。

o Script（我做了些微调整，将SET @j = 0改成@j = 1）

USE [AdventureWorks]

DROP PROC LongCompile

CREATE PROC LongCompile ( @i INT )

DECLARE @cmd VARCHAR(MAX)

DECLARE @j INT

SET @j = 1

SET @cmd = '

select count(b.SalesOrderID),sum(p.Weight)

from dbo.SalesOrderHeader_test a

inner join dbo.SalesOrderDetail_test b

on a.SalesOrderID=b.SalesOrderID

inner join Production.Product p

on b.ProductID=p.ProductID

where a.SalesOrderID in(43659'

WHILE @j < @i

BEGIN

SET @cmd = @cmd + ',' + STR(@j + 43659)

SET @j = @j + 1

END

SET @cmd = @cmd + ')'

--PRINT @cmd

EXEC(@cmd)

o EXEC LongCompile 10打印出的结果：

SELECT COUNT(b.SalesOrderID) ,

SUM(p.Weight)

FROM dbo.SalesOrderHeader_test a

INNER JOIN dbo.SalesOrderDetail_test b ON a.SalesOrderID = b.SalesOrderID

INNER JOIN Production.Product p ON b.ProductID = p.ProductID

WHERE a.SalesOrderID IN ( 43659, 43660, 43661, 43662, 43663, 43664, 43665, 43666, 43667, 43668)

·               SQL Trace的事件不会直接打印出编译时间，但是可以计算出。编译会发生在下的面情况：

o   一个新的批处理batch开始运行前

ü      一个Batch的编译时间=SQL:StmtStarting – SQL:BatchStarting（因为ＳＱＬ是先编译整个Batch，然后再开始运行第一句话）。如果两者相等，要么执行计划被重用，要么编译太短，被忽略不计。

ü      如果是动态语句，它的编译时间是Exec指令和真正的语句这两个SQL:StmtStarting 或SP：StmtStarting事件之间（如果动态语句是在usp中）。

o   一个新的存储过程stored procedure开始运行前

ü      一个usp的编译时间=SQL:StmtStarting(或RPC:Starting) – SP:StmtStarting（因为ＳＱＬ是先编译整个SP，然后再开始运行第一句话）。如果两者相等，要么执行计划被重用，要么编译太短，被忽略不计。

ü      也会发生SP：CacheInsert事件

o   语句发生重编译recompile的时候

ü      同上。

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

o   检查语句本身是否过于复杂，长度太长。如果是，可以考虑把一句话拆成几句更简单的语句，或者用temp table来替代大的“in”子句。因为SQL是先编译整个batch或者sp，然后再开始运行第一句话

o   检查语句使用的表格上是不是有太多的索引 - 索引越多，SQL要评估的执行计划就越多，花的时间越长。作为一个设计严谨的数据库，要把没有用的索引及时删除。

o   引导SQL尽量多重用执行计划，减少编译

18.3. 判断执行计划是否合适 784

大部分情况下SQL计算得会比人脑要准确，它选择的执行计划是有道理的。要找出一个比它选的更好的执行计划，不是那么容易。但是SQL还是会犯错误。通常可以从以下这几个角度，判断现在得到的执行计划是否准确，

18.3.1. 预估cost的准确性 784

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

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

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

· 在17.1.3的案例中，作者将问题定位到了两个表的错误联接上。那是怎麽定位到这一点呢？

· 在出问题时的执行计划里，Rows的值和EstimateRows的值有明显差距。根据SQL的预估，联接最里面的几层应该不返回记录（EstimateRows=1.0），所以它使用了nested loops。而实际上，它们返回了9653条记录。这麽大的结果集，用nested loops是不太合适的。而查询速度正常时候的执行计划。显示SQL预估的值要高得多，所以SQL选择的是HASH MATCH JOIN和MERGE JOIN的方式。这证明了前面的猜想。在查询有性能问题的时候，SQL没有正确地估计联接返回记录的多少，所以错误地估计了nested loops联接动作的开销，选择了一个错误的执行计划

· Case 2 – 在这个案例里，有一个查询，会返回大约12万条记录。请运行它们三次。第一次是正常运行，第二次运行之前，会先清空执行计划，然后设置语句只返回一条记录（set rowcount 1），第三次运行之前，不清空执行计划，但是设置语句返回所有记录（set rowcount 0）。按道理，第一次运行和第三次运行应该速度相当，因为它们都要返回12万条记录。第二次会最快，因为它只返回一条记录。结果呢？

--第一次

USE [AdventureWorks]

--清空缓存数据页面buffer pool

DBCC DROPCLEANBUFFERS

--清空执行计划

DBCC FREEPROCCACHE

SET ROWCOUNT 0

SET STATISTICS PROFILE ON

--返回12万行。正确地使用了Hash Join

SELECT p.[ProductID] ,

p.[Weight]

FROM [dbo].[SalesOrderHeader_test] a

INNER JOIN [dbo].[SalesOrderDetail_test] b ON a.[SalesOrderID] = b.[SalesOrderID]

INNER JOIN [Production].[Product] p ON b.[ProductID] = p.[ProductID]

WHERE a.[SalesOrderID] = 75124

--第二次

USE [AdventureWorks]

--清空页面缓存

DBCC FREEPROCCACHE

--只返回一行

SET ROWCOUNT 1

SET STATISTICS PROFILE ON

--返回一行。正确地使用了Nested Join

SELECT p.[ProductID] ,

p.[Weight]

FROM [dbo].[SalesOrderHeader_test] a

INNER JOIN [dbo].[SalesOrderDetail_test] b ON a.[SalesOrderID] = b.[SalesOrderID]

INNER JOIN [Production].[Product] p ON b.[ProductID] = p.[ProductID]

WHERE a.[SalesOrderID] = 75124

--第三次

USE [AdventureWorks]

SET ROWCOUNT 0

SET STATISTICS PROFILE ON

--返回12万行，重用了第二次的Nested Loopsd的执行计划。Wrong!

--也可看到EstimateRows太离谱，与实际的Rows差别较大。

--可喜的是，在SQL Server 2012中，它考虑到了SET ROWCOUNT 的情况，不再重用第二次的，而是生成了新的计划。与第一次一样，也是Hash Join。

SELECT p.[ProductID] ,

p.[Weight]

FROM [dbo].[SalesOrderHeader_test] a

INNER JOIN [dbo].[SalesOrderDetail_test] b ON a.[SalesOrderID] = b.[SalesOrderID]

INNER JOIN [Production].[Product] p ON b.[ProductID] = p.[ProductID]

WHERE a.[SalesOrderID] = 75124

18.3.2. 是index seek还是table scan 790

· 检查执行计划的第二个重点，是要检查SQL从表格里检索数据的时候，是否选择了合适的方法。SQL会根据检索数据的多少，以及索引的结构，选择是用Index Seek还是Table Scan。

· 如果某个数据检索动作实际返回的行数不多，但是SQL选择了scan方法，那就要重视了，因为这种scan，会带来比较大的性能影响。

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

18.3.3. 是nested loops还是hash (merge) join 794

· Nested loops比较合适于联接的双方结果集比较小的情况，而hash（merge）join 适合结果集比较大的情况。很多SQL没有选对执行计划，而导致性能问题，都是因为误选了nested loops导致的。当Outer table比较大时，使用nested loops方法，inner table会被loop很多次，导致执行复杂度急剧增加。

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

18.3.4. filter运算的位置 795

· 在一句查询里，常见的是几个表格做联接，同时又有一些where子句filter掉一些记录。那么是先filter掉记录，再做联接好呢，还是先做联接，再filter呢？一般来讲，先filter掉一些记录，使得做联接的记录集小一点，会大大降低联接的消耗。所以filter先做，会提高查询效率。在检查执行计划的时候，用户也要看看，是不是SQL及时做了filter。

· Demo：现在有两个查询。对用户来讲，它们是一样的，只不过第二个查询把p.ProductID加了个1以后再做比较。返回的结果集也会一样。但是两条语句的执行效率却有很大差别，第二条比第一条慢一倍还要多，CPU时间也高。这是为什麽呢？

USE [AdventureWorks]

SET STATISTICS PROFILE ON

SELECT COUNT(b.[ProductID])

FROM [dbo].[SalesOrderHeader_test] a

INNER JOIN [dbo].[SalesOrderDetail_test] b ON a.[SalesOrderID] = b.[SalesOrderID]

INNER JOIN [Production].[Product] p ON b.[ProductID] = p.[ProductID]

WHERE p.[ProductID] BETWEEN 758 AND 800

--查询二

USE [AdventureWorks]

SET STATISTICS PROFILE ON

SELECT COUNT(b.[ProductID])

FROM [dbo].[SalesOrderHeader_test] a

INNER JOIN [dbo].[SalesOrderDetail_test] b ON a.[SalesOrderID] = b.[SalesOrderID]

INNER JOIN [Production].[Product] p ON b.[ProductID] = p.[ProductID]

WHERE ( p.[ProductID] + 1 ) BETWEEN 759 AND 801

· 因为第一句的做法，是dbo.SalesOrderDetail_test和dbo.SalesOrderHeader_test这两张表先做join，再和product表做join。有意思的是，虽然语句里只有一个where子句：WHERE p.[ProductID] BETWEEN 758 AND 800，但是执行计划里可以看到两个filter动作。一个在dbo.SalesOrderDetail_test上，一个在Product上。这是因为SQL发现这两张表将要通过ON b.[ProductID]=p.[ProductID]做联接，所以在Product上的条件，同样适合在dbo.SalesOrderDetail_test上。这样，SQL先在dbo.SalesOrderDetail_test上做一个filter，结果集就小得多再做join，花费就能节省不少。而第二句的做法，也是SalesOrderHeader_test和SalesOrderDetail_test这两张表先做join，再和Product做join。但是filter动作只发生在Product上，没有发生在SalesOrderDetail_test上。所以SalesOrderHeader_test和SalesOrderDetail_test做联接的时候，结果集会大一些。产生这种现象的原因，是语句的where子句的写法：WHERE (p.[ProductID] +1) BETWEEN 759 AND 801。SQL没有办法把这样的filter也适用在SalesOrderDetail_test这张表上。从这个例子，可以看出filter动作的位置对语句性能的影响。

18.3.5. 确认问题产生的原因 - 产生问题的原因小结

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

o 统计信息不存在，或者没有及时更新，是产生这个问题的主要原因。应对办法：开启“自动创建统计信息”，“自动更新统计信息”如果这样还不能保证统计信息的精确性，可以定义一个任务，定期更新统计信息。

o 子句太过复杂，也可能使SQL猜不出一个准确的值，只好猜一个平均数。比如where子句里对字段做计算，代入函数等行为，都可能会影响SQL预估的准确性。如果发现这种情况，就要想办法简化语句，降低复杂度，提高效率。当语句代入的变量值是一个参数，而SQL在编译的时候可能不知道这个参数的值，只好根据某些规则，“猜”一个预估值，这也会影响到预估的准确性。这个话题就是“参数嗅探”。

· （2）语句重用了一个不合适的执行计划

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

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

· （3）筛选子句写得不太合适，妨碍SQL选取更优的执行计划

o 当语句要筛选掉一些记录时，索引会帮上忙。一般来讲，筛选动作做得越早，越能提高效率。

o SQL对筛选条件（search argument/SARG）的写法有一定建议。筛选条件应该采用以下格式之一：

ü 列名运算符 <常量或变量>：name='annie'；mount>4000；department='hr'

ü <常量或变量> 运算符列名： 6000<amount

o SARG运算符包括：=、>、<、>=、<=、IN、BETWEEN、LIKE（在进行前缀匹配时，如like 'john%'）

o SARG可以包括由AND联接的多个条件。

o SARG不但可以是匹配特定值的查询，还可以是匹配一定范围的值的查询，例如：amount>4000 and amount<6000；customerid IN('action’,’ about')

o 非SARG运算符包括NOT、<>、NOT EXISTS、NOT IN、NOT LIKE和内部函数，例如：CONVERT(),UPPER()等。对于不是有SARG运算符的表达式，索引是没有用的，SQL对它们很难使用比较优化的做法。

· 总之，当确认了执行计划出问题的原因，就可以对症下药，引导SQL总是选择一个好的执行计划，当然在有些情况下，基于现有语句和数据库结构，很难选择更好的执行计划。那就要通过修改数据库结构，或者修改语句设计来解决问题，提高效率。

18.4. parameter sniffing 800

· 当使用存储过程的时候，总是要使用到一些变量。变量有两种，一种是在存储过程的外面定义的。当调用存储过程的时候，必须要给它代入值。这种变量，SQL在编译的时候知道它的值是多少。还有一种变量是在存储过程里面定义的。它的值在存储过程的语句执行的过程中得到的。所以对这种本地变量，SQL在编译的时候不知道它的值是多少。

· 对于第一类变量，根据第一次运行时代入的值生成的执行计划，是不是就能够适合所有可能的变量值呢？对于第二类本地变量，SQL在编译的时候并不知道它的值是多少，那怎么选择“合适”的执行计划呢？

· 对于第一个问题，会引出对 “参数嗅探”问题的定义。而对于第二个问题，本节将介绍使用本地变量对执行计划选择的影响。最后介绍参数嗅探问题的候选解决方案。

18.4.1. 什么是“parameter sniffing” 801

· 因为重用它人生成的执行计划而导致的水土不服现象，叫“参数嗅探 parameter sniffing”是因为语句的执行计划对变量的值很敏感，而导致重用执行计划会遇到性能问题。

18.4.2. 本地变量的影响 802

· 有参数嗅探的情况，运用本地变量作出来的执行计划是一种比较中庸的方法，不是最快的，也不是最慢的。它对语句性能的影响，一般不会有参数嗅探那么严重。很多时候，它还是解决参数嗅探的一个候选方案。

18.4.3. parameter sniffing的解决方案 804

· 参数嗅探的问题发生的频率并不高，它只会发生在一些表格里的数据分布很不均匀，或者用户带入的参数值很不均匀的情况下。例如，查询一个时间段数据的存储过程，如果大部分用户都只查1天的数据，SQL缓存的也是这样的执行计划，那对于那些要查一年的数据，就有碰到“参数嗅探”问题的风险了。如果系统里大部分用户都要查一年的数据，可是SQL碰巧缓存了一个只查一天数据的存储过程，那大部分用户都会遇到“参数嗅探”的问题，这个对性能的影响就大了。

· 有什么办法能够缓解，或者避免参数嗅探问题呢？在SQL2005以后，可以有很多种方法可供选择：

o （1）用exec()的方式运行动态SQL

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

使用exec()的方式产生动态编译，在查询语句执行之前，都能看见SP: CacheInsert事件。SQL做了动态编译，根据变量的值，正确地估计出每一步的返回结果集大小。所以这个执行计划与传统usp产生的是完全不一样的。执行结果很快，这种方法的好处，是彻底避免参数嗅探问题。但缺点是要修改存储过程的定义，而且放弃了存储过程一次编译，多次运行的优点，在编译性能上有所损失。

o （2）使用本地变量local variable

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

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

o （3）在语句里使用query hint，指定执行计划

ü 在select, insert, update, delete语句的最后，可以加一个"option(<query_hint>)"的子句对SQL将要生成的执行计划进行指导。当DBA知道问题所在以后，可以通过加hint的方式，引导SQL生成一个比较安全的，对所有可能的变量值都不差的执行计划。

ü 现在SQL的query hint还是很强大的，有十几种hint。为了避免参数嗅探的问题，有下面几种常见的query hint使用方法：

Ø OPTION(RECOMPILE)

CREATE PROC NoSniff_QueryHint_Recompile ( @i INT )

SELECT COUNT(b.[SalesOrderID]) ,

SUM(p.[Weight])

FROM [dbo].[SalesOrderHeader_test] a

INNER JOIN [dbo].[SalesOrderDetail_test] b ON a.[SalesOrderID] = b.[SalesOrderID]

INNER JOIN [Production].[Product] p ON b.[ProductID] = p.[ProductID]

WHERE a.[SalesOrderID] = @i

OPTION ( RECOMPILE )

Recompile这个查询提示告诉SQL，语句在每一次存储过程运行的时候，都要重新编译一下。这样能够使SQL根据当前变量的值，选一个最好的执行计划。

在SQL Trace里我们能够看到,语句运行之前,都会有一个SQL:StmtRecompile的事件发生,而使用的执行计划，就是最准确的那种。

Ø WITH RECOMPILE - 和这种方法类似，是在存储过程的定义里直接指定“recompile”，也能达到避免参数嗅探的效果。

CREATE PROC NoSniff_SPCreate_Recompile(@i INT)

WITH RECOMPILE

在SQL Trace里,我们能看到,存储过程在执行的时候已经找不到前面的执行计划。SP:CacheMiss,所以要生成新的。而使用的执行计划，就是最准确的那种。

这两种“Recompile”提示的差别是，如果在语句层次指定OPTION(RECOMPILE)，那存储过程级别的计划重用还是会有的。只是在运行到那句话的时候，才会发生重编译。如果存储过程里有If-else之类的逻辑，使得发生问题的语句没有执行到，那重编译就不会发生。所以，这是一种问题精确定位后，比较精细的一种调优方法。如果在存储过程级别指定WITH RECOMPILE，那整个存储过程在每次执行的时候都要重编译，这个重复工作量就比较大了。但是，如果问题没有精确定位，可以用这种方法快速缓解问题。

Ø 指定join运算 { LOOP | MERGE | HASH } JOIN

很多时候，参数嗅探问题是由于SQL对一个该用merge join/hash join的情况误用了nested loops join。确定了问题后，当然可以用查询提示，指定语句里所有join方法。但是这种方法一般很不推荐，因为不是所有的join，SQL都能够根据你给的提示做出来执行计划的。

更常见的是，在特定的那个join上使用join hint。这种方法成功几率要高得多。这种方法的好处，是保持了存储过程一次编译，后续多次使用的特性，节省编译时间。但是缺点也很明显，使用这样的方法生成执行计划，不一定就是一个好的执行计划。而且表格里的数据量变化以后，现在合适的join方式将来可能就不合适，到时候还会造成性能问题，所以使用的时候要很小心.

CREATE PROC NoSniff_QueryHint_JoinHint ( @i INT )

SELECT COUNT(b.[SalesOrderID]) ,

SUM(p.[Weight])

FROM [dbo].[SalesOrderHeader_test] a

INNER JOIN [dbo].[SalesOrderDetail_test] b ON a.[SalesOrderID] = b.[SalesOrderID]

INNER HASH JOIN [Production].[Product] p ON b.[ProductID] = p.[ProductID]

WHERE a.[SalesOrderID] = @i

Ø OPTIMIZE FOR (@variable_name = literal_constant [...n]) 。

当确认了参数嗅探问题后，发现，根据某些变量值生成的执行计划，快和慢会相差很大，而根据另外一些变量生成的执行计划，性能在好和坏的时候，相差并不很大。例如当变量等于50000的时候，它用最好的nested loops执行计划，用时十几毫秒，用hash join的那个执行计划，也不过300多毫秒。但是变量等于75124的时候，hash join执行计划需要500多毫秒，用nested loops的时候，要用4000多。所以Hash join是一个比较“安全”的执行计划。如果SQL总是使用75124这个值做执行计划，会对大部分查询都比较安全。使用OPTIMIZE FOR这个查询指导，就能够让SQL做到这一点。这是SQL2005以后的一个新功能。

CREATE PROC NoSniff_QueryHint_OptimizeFor ( @i INT )

SELECT COUNT(b.[SalesOrderID]) ,

SUM(p.[Weight])

FROM [dbo].[SalesOrderHeader_test] a

INNER JOIN [dbo].[SalesOrderDetail_test] b ON a.[SalesOrderID] = b.[SalesOrderID]

INNER HASH JOIN [Production].[Product] p ON b.[ProductID] = p.[ProductID]

WHERE a.[SalesOrderID] = @i

OPTION ( OPTIMIZE FOR ( @i=75124 ) )

Ø Plan Guide

如果不能修改存储过程定义或修改调用sp_executesql的应用程序，SQL2005以后可以使用Plan Guide。当SQL Server运行某一个语句的时候，会使用指定的执行计划。这样就不需要去修改存储过程或应用了。

EXEC [sys].[sp_create_plan_guide]

@name = N'Guide1',

@stmt = N'SELECT COUNT(b.[SalesOrderID]),SUM(p.[Weight])

FROM [dbo].[SalesOrderHeader_test] a

INNER JOIN [dbo].[SalesOrderDetail_test] b

ON a.[SalesOrderID]=b.[SalesOrderID]

INNER JOIN [Production].[Product] p

ON b.[ProductID]=p.[ProductID]

WHERE a.[SalesOrderID]=@i',

@type = N'OBJECT',

@module_or_batch = N'Sniff',

@params = NULL,

@hints = N'option(optimize for(@i=75124))';

o 通过sys.dm_exec_procedure_stats 找出相关有问题的usp，对其运用sp_recompile重新编译。

ü sys.dm_exec_procedure_stats （2008+） - Returns aggregate performance statistics for cached stored procedures. The view returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the corresponding row is eliminated from this view. At that time, a Performance Statistics SQL trace event is raised similar to sys.dm_exec_query_stats.

ü 业务要求：

Ø 系统24/7全负荷运行

Ø 客户可能在不同的usp中遇到Parameter Sniffering的问题。

Ø 因为牵涉到非常多的usp,客户不能对所有的usp进行修改。

Ø 单次稍慢OK，但后续的执行要能够立即得到改善。

Ø 要能够防止问题的再次发生。

Ø 所有的动作要自动完成，不要人工干预。

ü 解决方案（还需要进一步说明，最好呈现完整的Scripts）

SELECT p.name AS [SPName] ,

qs.last_elapsed_time / 1000 AS [LastExecTime(ms)] ,

( qs.total_elapsed_time / qs.execution_count ) / 1000 AS [AVGExecTime(ms)] ,

qs.min_elapsed_time / 1000 AS [MinExecTime(ms)] ,

qs.max_elapsed_time / 1000 AS [MaxExecTime(ms)] ,

( qs.total_worker_time / qs.execution_count ) / 1000 AS [AVGCPUTime(ms)] ,

qs.execution_count AS [ExecCount] ,

qs.cached_time AS [LastCachedTime] ,

qs.last_execution_time AS [LastExecTime] ,

( qs.total_logical_writes + qs.total_logical_reads )

/ qs.execution_count AS [AVGLogicalIOs] ,

qs.min_logical_reads AS MinLogicalReads ,

qs.max_logical_reads AS MaxLogicalReads ,

qs.min_logical_writes AS MinLogicalWrites ,

qs.max_logical_writes AS MaxogicalWrites

FROM sys.dm_exec_procedure_stats qs ,

sys.procedures p

WHERE qs.object_id = p.object_id

ORDER BY [LastExecTime(ms)]

Ø 通过SQL Agent定期执行上面的脚本可以获得所有的存储过程的执行概况，比如我们只关心最后一次执行时间最长的50个语句（稍作修改）。

Ø 获得语句后我们可以对比最后执行时间和最大执行时间（或平均执行时间）。

Ø 只要最后的执行时间超过我们的预期值（如所有语句要在5秒内完成），我们就可以通过sp_recompile来指示SQL Server重新编译该存储过程。

Ø 以上逻辑可以通过T-SQL脚本实现。通过SQL Agent的Jobs来定期调用这段脚本，就可以自动对所有突然变慢的语句进行强制重编译，让它们获得新的执行计划。

ü 缺陷

Ø 一旦发生参数嗅探，那么那一次执行的语句因为我们还没有感知到参数嗅探，所以不会重新编译，那次执行的性能也就会会非常的差，但是后续的执行应该会得到改善。

Ø 需要客户对自己系统所执行的存储过程有一定的了解，知道怎样让TSQL语句去判断，什么样的语句执行是不正常的。

· 本节小结

方法	是否修改存储过程	是否每次运行都要重编译	执行计划准确度
用exec()方式运行动态SQL	需要	是	很准确
使用本地变量local variable	需要	否	一般
query hint+"recompile"	需要	是	很准确
query hint指定join运算	需要	否	很一般
query hint optimize for	需要	否	比较准确
Plan Guide	不需要	否	比较准确

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

当一个语句出现性能问题，而且已经排除了系统资源瓶颈、阻塞与死锁、物理I/O、编译和重编译、参数嗅探这些因素以后，那所能做的，要不就是调整数据库设计，要不就是修改语句本身。调整数据库设计基本是两个思路：调整索引，或者使用 Plan Guide。这两个方法都不用修改语句本身。

18.5.1. 调整索引 814

· 索引设计的优劣很大程度上决定了数据库的性能。所以做语句调优，调整索引是必备的功课。什么样的索引才是好的索引呢？这个问题已经被广泛地讨论。

o 聚集索引

ü 如果一张表经常用某种顺序检索数据，那按照这个顺序建立聚集索引无疑是最理想的。

ü 聚集索引最好要建立在重复值很少的字段上。但选择什么样的字段，值重复不重复是一个标准，表格的常见检索方式也是一个重要的标准。例如一个Employee表格里，有姓名字段，也有工号字段。一般来讲，姓名可能偶尔会有重复，但是工号不会重复。如果我们对Employee表的检索，通常只用姓名，很少使用工号那就应该选姓名作为聚集索引。

o 非聚集索引

ü 一个聚集索引是无法覆盖所有要求的，需要有非聚集索引来辅助。

ü 建立非聚集索引的字段，不一定非要是很少不重复的。像性别、职务这样的字段，肯定会有很多重复记录，但是为了提高检索性能，也可以在上面建立非聚集索引。

ü 但是非聚集索引里面没有存储记录的所有信息。如果查询要使用的字段没有完全包含在非聚集索引里，那SQL就要做Bookmark lookup。这会有额外的开销。

o Covering Index（复合索引/涵盖索引）-

ü An index that contains all information required to resolve the query is known as a "Covering Index"; it completely covers the query（未必是涵盖所有的columns，只要能一次到位、不需要额外的步骤解决query就行）。

ü 好处：

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

Ø 如果查询用到的字段索引都能够覆盖，那就不用做书签查找，能节省时间

ü 注意点：

Ø （1）要慎重选择索引的第一个字段，最好选择一个重复记录最少的字段。这是因为索引上的统计信息只保存第一个字段的数据直方图。

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

Ø （3）Furthermore, indexes are limited to 16 key columns or 900 bytes, whichever comes first. And some data types cannot be used as index keys, varchar (max) for instance.

o Index with Included Columns（具有包含性列的索引）

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

ü 如果建立一个covering index在（Title+[BirthDate]）上，数据会按照（Title+[BirthDate]）的方式排序。

ü 在SQL2005以后，建立一个有包含性列（Included Columns）的索引。这样的索引，既可以节省Bookmark Lookup的消耗，也不需要做不必要的排序，是Covering Index的一种替代方式。

o Indexed View（索引视图）

ü 视图只是一个逻辑定义，它里面并没有存储任何数据。对它的查询会转向对它所基于的表格的查询。

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

ü 但是，SQL对索引视图有限制。不是所有的视图都能建索引。视图要符合一定的先决条件。

o Primary Key（主键）

ü 主键是一个约束（constraint），它依附在一个索引上，这个索引可以是聚集索引，也可以是非聚集索引。所以在一个（或一组）字段上有主键，只能说明它上面有个索引，但不一定就是聚集索引。而一个聚集索引里，是可以有重复值的。只要它没有被同时设为主键。

ü 所以，如果没有指定主键是个聚集索引，可能表格还是会以堆的方式管理，效率低下。

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

o 当确认TotalSubtreeCost这一列是准确的以后，应该找对cost贡献最多的子句。如果它用的是Table Scan，或者Index Scan，请比较它返回的行数（即该查询返回的行数）和表格实际行数（即不带WHERE返回的所有行数）。如果返回行数远小于实际行数，那就说明SQL没有合适的索引供它做seek。这时候加索引就是一个比较好的选择。例如下面这个查询，在ProductID上加一个索引是一个比较自然的想法。

--带WHERE,返回,422 rows,不带WHERE，全表是,213,172.

SELECT *

FROM [dbo].[SalesOrderDetail_test]

WHERE [ProductID]=777

o Missing Index – 单一语句的情形

ü 从SQL2005以后，在SQL对任何一句语句做编译的时候，都会去评估一下，这句话是不是缺少什么索引的支持，如果它认为是，它还会预估，如果有这麽一个索引，它的性能能提高多少。

ü 对单条语句，这个信息可以通过SET SHOWPLAN_XML ON或者SQL Trace里的Performance-Showplan XML Statistics Profiles这个事件得到。

ü 在SQL Trace里Performance-Showplan XML Statistics Profiles这个事件的内容会以一个执行计划图显示，我们想要的信息看不到。你需要选择这个事件，点击右键，选择Extract Event Data，把数据输出到一个.SQLPlan结尾的文件里，用记事本打开（默认使用SSMS以图形方式打开）。在XML格式的文本里，如果你能找到<MissingIndexes>这个Tag，就说明SQL对你现在的数据库设计有些意见了。

ü 从SQL Server 2008开始，如果选择输出Estimated Execution Plan或Actual Execution Plan，SSMS会直接呈现Missing Indexes及其相应的改善。

ü 总之，DBA需要去确认一下建议。因为这个建议完全是根据这个语句本身给出的，没有考虑对其它语句的影响，也没有考虑维护索引的成本，所以是很片面的。其准确性，也要再确认一下。

o sys.dm_db_missing_index_details – 所有运行过得语句

ü 记录了当前数据库下所有的missing index的信息。它针对的是SQL从启动以来所有运行的语句，而不是针对某一个查询。DBA可以看看，哪些表格SQL是最有“意见”的

SELECT * FROM sys. [dm_db_missing_index_details]

o   Database Engine Tuning Advisor（DTA）数据库引擎优化顾问

ü      注意一： 虽然DTA接受一个SQL Trace文件作为工作语句的输入，但是这种方法不太有效。比较好的方法，是先找出你想要优化的语句，把它们存成一个.sql文件，作为输入。

ü      注意二： 不要在生产数据库上直接运行DTA。一是因为增加负担，二是做tunning时会使用一个temp objects,以测试其建议的有效性。如果运行中出错，这些temp objects就不会被自动删除。

ü      注意三：DTA给出的建议具有片面性。要经过整体评估。才能在生产数据库上实施。

18.5.2. 计划指南 821

· 使用这个方法要满足几个前提：

o （1）问题语句固定，且应用程序就一直使用这种格式 - 在指定执行计划指南的时候，需要告诉SQL完整的语句。当SQL发现任何一个用户将要运行同样的一句话时，就会应用计划指南。如果问题语句是动态生成的，每一次都不一样的话，那计划指南可能就不那么有用了。

o （2）语句的性能问题可以通过选用一个固定的执行计划得到解决 - 存在一个“万能”的执行计划，它对任何参数值、任何用户，其执行效率都是稳定可接受的，而且也不会随着数据量的变化，执行效率变得不可接受。如果你找不到这样一个执行计划，那就没有办法使用这个方法。

o （3）使用这种方法后，需要有人不断监视语句的性能 - 如果数据量或数据分布发生很大的变化，可能先前好的执行计划现在反而会导致更大的问题。需要DBA能够知道计划指南设置的合理，在必要的时候做相应的调整。

· 创建计划指南的语句是：

sp_create_plan_guide

[ @name = ] N'plan_guide_name'

, [ @stmt = ] N'statement_text'

, [ @type = ] N'{ OBJECT | SQL | TEMPLATE }'

, [ @module_or_batch = ]

{

N'[ schema_name. ] object_name'

| N'batch_text'

| NULL

}

, [ @params = ] { N'@parameter_name data_type [ ,...n ]' | NULL }

, [ @hints = ] { N'OPTION ( query_hint [ ,...n ] )'

N'XML_showplan'

| NULL

}

o 简单来说，用户可以给一个batch里的一句话定义计划指南，也可以给一个存储过程里的一句话定义(@module_or_batch)。这句话可以有参数，也可以没有参数(@params)。计划指南可以是一个Query Hint，也可以直接是一个执行计划(@hints)

· 例一：对单个固定语句定义计划指南

--假设有这麽一张表格：

USE [AdventureWorks]

CREATE TABLE table1(

NAME NVARCHAR(),

id INT

)

--有下面这个不带参数的语句（注意，它没有以回车符结束）

USE [AdventureWorks]

INSERT [dbo].[table1]

SELECT a.[name],a.[id]

FROM [sys].[sysobjects] a

INNER JOIN [sys].[sysindexes] b

ON a.id=b.[id]

WHERE b.[indid]=2

--默认，这个查询将会使用merge join。如果用户想要规定，这个join

--必须使用nested loops，除了直接在查询里加query hint以外，

--使用计划指南也可以达到同样效果。创建计划指南的语句应该是：

USE [AdventureWorks]

EXEC [sys].[sp_create_plan_guide]

@name=N'Guide1',

@stmt=N'INSERT [dbo].[table1]

SELECT a.[name],a.[id]

FROM [sys].[sysobjects] a

INNER JOIN [sys].[sysindexes] b

ON a.id=b.[id]

WHERE b.[indid]=2',

@type=N'SQL',

@module_or_batch=N'INSERT [dbo].[table1]

SELECT a.[name],a.[id]

FROM [sys].[sysobjects] a

INNER JOIN [sys].[sysindexes] b

ON a.id=b.[id]

WHERE b.[indid]=2',

@params=NULL,

@hints=N'option(loop join)'

--创建计划指南时，@stmt和@module_or_batch参数所带的字符串，必须

--和语句本身一模一样，不能少一个空格，也不能多一个回车。SQL在这里

--做的是精确匹配

--计划指南创建完毕后，再运行这句话

USE [AdventureWorks]

SET STATISTICS XML ON

EXEC [sys].[sp_executesql] N'INSERT [dbo].[table1]

SELECT a.[name],a.[id]

FROM [sys].[sysobjects] a

INNER JOIN [sys].[sysindexes] b

ON a.id=b.[id]

WHERE b.[indid]=2'

--在以XML格式输出的执行计划里,就能看到执行计划是根据计划指南生成的

--就在XML的开头

PlanGuideDB="AdventureWorks" PlanGuideName="Guide2"

· 例二：对批处理里一个带有参数的语句定义计划指南

--一般来讲,用户发过来的批处理会不止一句话，而且语句里也会有参数。

--假设有下面这个批处理，计划指南如何写？

USE [AdventureWorks]

DECLARE @id INT

SET @id = 2

DECLARE @Table TABLE

(

NAME NVARCHAR(256) ,

id INT

)

INSERT INTO @Table

SELECT a.[name] ,

a.[id]

FROM [sys].[sysobjects] a

INNER JOIN [sys].[sysindexes] b ON a.[id] = b.[id]

WHERE b.[indid] = @id

--注意在@stmt参数里，只需要代入语句本身。但是在@module_or_batch里，要代入

--整个批处理的每一句话。另外，语句的参数，却不用写在@params里。

USE [AdventureWorks]

EXEC [sys].[sp_create_plan_guide]

@name = N'Guide2',

@stmt = N'INSERT INTO @Table

SELECT a.[name],a.[id]

FROM [sys].[sysobjects] a

INNER JOIN [sys].[sysindexes] b

ON a.[id]=b.[id]

WHERE b.[indid]= @id',

@type = N'SQL',

@module_or_batch = N'DECLARE @id INT

SET @id=2

DECLARE @Table TABLE(NAME NVARCHAR(256),id INT)

INSERT INTO @Table

SELECT a.[name],a.[id]

FROM [sys].[sysobjects] a

INNER JOIN [sys].[sysindexes] b

ON a.[id]=b.[id]

WHERE b.[indid]= @id',

@params = NULL,

@hints = N'option(loop join)'

· 例三：使用带参数的方式调用批处理语句，给其中一句指定计划指南

--这与上例实质上是一样的，但用户使用了sp_executesql的方法调用这句话

EXEC [sys].[sp_executesql] N'DECLARE @Table TABLE(NAME NVARCHAR(256),id INT)

INSERT INTO @Table

SELECT a.[name],a.[id]

FROM [sys].[sysobjects] a

INNER JOIN [sys].[sysindexes] b

ON a.[id]=b.[id]

WHERE b.[indid]= @id', N'@id int', @id = 2

--而这个时候，计划指南要这样写

EXEC [sys].[sp_create_plan_guide]

@name = N'Guide3',

@stmt = N'INSERT INTO @Table

SELECT a.[name],a.[id]

FROM [sys].[sysobjects] a

INNER JOIN [sys].[sysindexes] b

ON a.[id]=b.[id]

WHERE b.[indid]= @id',

@type = N'SQL',

@module_or_batch = N'DECLARE @Table TABLE(NAME NVARCHAR(256),id INT)

INSERT INTO @Table

SELECT a.[name],a.[id]

FROM [sys].[sysobjects] a

INNER JOIN [sys].[sysindexes] b

ON a.[id]=b.[id]

WHERE b.[indid]= @id',

@params = N'@id int',

@hints = N'option(loop join)'

--用批处理的时候，要把整个批处理都抄在[sp_create_plan_guide]后面，较麻烦

--如果是存储过程，就好办多了

· 例四、对一个存储过程里的一条语句定义计划指南

--假设存储过程是这样的

CREATE PROC Demo_Plan ( @id INT )

DECLARE @Table TABLE ( NAME VARCHAR(256), id INT )

INSERT INTO @Table

SELECT a.[name] ,

a.[id]

FROM [sys].[sysobjects] a

INNER JOIN [sys].[sysindexes] b ON a.[id] = b.[id]

WHERE b.[indid] = @id

--那么计划指南就可以这样定义

EXEC [sys].[sp_create_plan_guide]

@name = N'Guide4',

@stmt = N'INSERT INTO @Table

SELECT a.[name],a.[id]

FROM [sys].[sysobjects] a

INNER JOIN [sys].[sysindexes] b

ON a.[id]=b.[id]

WHERE b.[indid]=@id',

@type = N'Object',

@module_or_batch = N'Demo_Plan',

@params = NULL,

@hints = N'option(loop join)'

--在上面这些例子里，用的都是Query Hint，并没有直接指定执行计划。 --能不能直接指定SQL在跑这句话的时候，就固定地使用某个执行计划呢？

--这个也是支持的。

· 例五：通过[sp_create_plan_guide]指定某个执行计划

--[sp_create_plan_guide]的@hints参数，在SQL 2008里可以直接带入

--一个XML格式的执行计划（SQL 2005要使用N'OPTION(use plan...)'的方法）

--首先我们创建一个usp。里面insert语句的join，默认会使用merge join的方式

CREATE PROC TestPlan

DECLARE @id INT

SET @id = 2

DECLARE @Table TABLE ( NAME VARCHAR(256), id INT )

INSERT INTO @Table

SELECT a.[name] ,

a.[id]

FROM [sys].[sysobjects] a

INNER JOIN [sys].[sysindexes] b ON a.id = b.[id]

WHERE b.[indid] = @id

--然后我们搞一个使用nested loops的执行计划，把它作为这个存储过程里

--insert语句的计划指南

USE [AdventureWorks]

DBCC freeproccache--清空所有缓存的执行计划

--使用query hint运行语句，让SQL生成一个nested loops的执行计划

DECLARE @id INT

SET @id = 2

DECLARE @Table TABLE

(

NAME NVARCHAR(256) ,

id INT

)

INSERT INTO @Table

SELECT a.[name] ,

a.[id]

FROM [sys].[sysobjects] a

INNER JOIN [sys].[sysindexes] b ON a.[id] = b.[id]

WHERE b.[indid] = @id

OPTION ( LOOP JOIN )

----从缓存里把这个执行计划取出来

DECLARE @xml_showplan NVARCHAR(MAX)

SET @xml_showplan = ( SELECT [query_plan]

FROM sys.[dm_exec_query_stats] AS qs

CROSS APPLY sys.[dm_exec_sql_text](qs.[sql_handle])

AS st

CROSS APPLY sys.[dm_exec_text_query_plan](qs.[plan_handle], DEFAULT, DEFAULT)

AS qp

WHERE st.text LIKE N'declare @id int%'

AND SUBSTRING(st.[text],

( qs.[statement_start_offset]

/ 2 ) + 1,

( ( CASE statement_end_offset

WHEN -1

THEN DATALENGTH(st.text)

ELSE qs.statement_end_offset

END

- qs.statement_start_offset

/ 2 ) + 1 )) LIKE 'INSERT into @table%'

);

--使用执行计划的内容直接定义计划指南

EXEC [sys].[sp_create_plan_guide]

@name = N'Guide5',

@stmt = N'insert into @table

select a.name,a.id from sysobjects a

inner join sysindexes b

on a.id=b.id

where b.indid=@id',

@type = N'Object',

@module_or_batch = N'TestPlan',

@params = NULL,

@hints = @xml_showplan

--以后再运行存储过程，就能够使用到nested loops的执行计划了。在SQL2008的SQL Trace里，

--新加入了两个事件：Performance-Plan Guide Successful和Performance-Plan Guide Unsuccessful

--可以用来跟踪语句的执行有没有正确地使用计划指南

--如果想要关闭，或者删除某个计划指南，可以调用[sp_control_plan_guide]系统存储过程

--例如：删除guide3这个计划指南

USE [AdventureWorks]

EXEC [sys].[sp_control_plan_guide] N'drop',N'Guide3'

--关闭当前数据库里的所有计划指南

EXEC [sys].[sp_control_plan_guide] N'disable all'

· 本节小结：计划指南这个功能，在急着调整执行计划，数据库和应用设计又不能修改的时候，能起到应急的作用。但是和使用query hint的局限性一样，计划指南更多用作短期的解决方案，长期来讲可能会有风险。数据变化后，计划指南可能就不再准确。

18.6. 调整语句设计提高性能 829

在有些情况下，一个语句的写法决定了它天生是一条复杂的语句，SQL很难使用最优的方法来运行它。这时候调整SQL SERVER，可能效果都不会很明显。既然这个问题更多的是由语句本身导致的话，那调整语句设计也许是更好的解决方法。语句优化的主要方向有：

18.6.1. 筛选条件与计算字段 829

· 筛选条件的写法是有讲究的。最好能够使用SARG的运算符包括=、>、<、>=、<=、in、between、有时还包括like（如在前缀匹配时，例如like 'john%'）

· SARG可以包括由and联接的多个条件。SARG不但可以是匹配特定值的查询，还可以是匹配一定范围的值的查询。

· 但对于不能使用SARG运算符的表达式，SQL对它们很难使用比较优化的做法，很可能就不使用索引。非SARG运算符包括 not、<>、not exists、not in、not like和内部函数，例如convert、upper等。下面的查询，就不会使用在Production.Product.Name字段上的索引。

o WHERE [Name] LIKE'%Deca%' --不是前缀匹配，所以使用索引扫描

o WHERE [Name] NOT LIKE'%Deca'--使用了NOT

o WHERE LEFT([Name],4)='Deca'--使用了内部函数

o WHERE [Name]+'_end'='Decal 1_end'--先进行了计算，后比较，也不行。

· 如果一个程序里有许多语句都要先进行计算，后再比较，可以考虑在表格里加一个persisted computed column字段，然后在这个新字段上建立索引。

· 对于一些表格里有时间字段,而语句经常按年、月查询，也可以使用这种方法引入计算字段，事先把计算的值存储在索引数据结构里。不过，不是所有的计算字段上都能加索引。例如那些Non-deterministic columns像根据employee的生日算年龄等。对这样的语句,查询的修改可能得换一个思路.比如,想找年龄大于30岁的员工,如果写成WHERE DATEDIFF(yy,[BirthDate],GETDATE())>30就不能使用索引，但如果写成WHERE [BirthDate] <DATEADD(yy,-30,GETDATE())，SQL就有办法使用索引了。

18.6.2. 会在运行前改变值的变量 832

· 在谈到参数嗅探的时候，提到过SQL在编译的时候，对存储过程代入的变量，SQL是知道它的值的，也会根据它的值对语句进行优化。但是如果在语句使用它之前，被其它语句修改过，那SQL生成的执行计划就不准了。

· 案例

--例如，下面这个存储过程，@date是它代入的参数。SQL会根据参数的值，生成执行计划

USE [AdventureWorks]

CREATE PROCEDURE GetRecentSales ( @date DATETIME )

BEGIN

SELECT SUM(d.[OrderQty])

FROM [dbo].[SalesOrderHeader_test] h ,

[dbo].[SalesOrderDetail_test] d

WHERE h.[SalesOrderID] = d.[SalesOrderID]

AND h.[OrderDate] > @date

END

EXEC [sys].[sp_recompile] @objname = N'GetRecentSales'

DBCC FREEPROCCACHE

SET STATISTICS PROFILE ON

--预估结果集很小，会使用nested loops

EXEC GetRecentSales NULL

EXEC [sys].[sp_recompile] @objname = N'GetRecentSales'

DECLARE @date DATETIME

SET @date = DATEADD(mm, -3, ( SELECT MAX([OrderDate])

FROM [dbo].[SalesOrderHeader_test]

))

SET STATISTICS PROFILE ON

--预估结果集比较大，会使用hash match

EXEC GetRecentSales @date

--但是如果我们把存储过程改成下面这个样子：

USE [AdventureWorks]

ALTER PROC GetRecentSales ( @date DATETIME )

BEGIN

IF @date IS NULL--如果是null值，会代入一个新的日期

SET @date = DATEADD(mm, -3,

( SELECT MAX([OrderDate])

FROM [dbo].[SalesOrderHeader_test]

))

SELECT SUM(d.[OrderQty])

FROM [dbo].[SalesOrderHeader_test] h ,

[dbo].[SalesOrderDetail_test] d

WHERE h.[SalesOrderID] = d.[SalesOrderID]

AND h.[OrderDate] > @date

END

EXEC [sys].[sp_recompile] @objname = N'GetRecentSales'

DBCC FREEPROCCACHE

SET STATISTICS PROFILE ON

--我们再用null值来运行,会发现SQL没办法感知到值发生了变化,还是使用了

--nested loop完成了查询。这个执行计划不是最优的。

EXEC GetRecentSales NULL

· 解决方法一：在使用变量的语句后面加一个option(recompile)的query hint。这样当SQL运行到这句话的时候，会重编译可能出问题的语句。在那个时候，就能根据修改过的值生成更精确的执行计划了。

USE [AdventureWorks]

ALTER PROC GetRecentSales ( @date DATETIME )

BEGIN

IF @date IS NULL--如果是null值，会代入一个新的日期

SET @date = DATEADD(mm, -3,

( SELECT MAX([OrderDate])

FROM [dbo].[SalesOrderHeader_test]

))

SELECT SUM(d.[OrderQty])

FROM [dbo].[SalesOrderHeader_test] h ,

[dbo].[SalesOrderDetail_test] d

WHERE h.[SalesOrderID] = d.[SalesOrderID]

AND h.[OrderDate] > @date

OPTION ( RECOMPILE )

END

· 解决方法二：把可能出问题的语句单独做成一个子存储过程,让原来的存储过程调用子存储过程,而不是语句本身。这样做的好处,是可以省下语句重编译的时间。

USE [AdventureWorks]

CREATE PROCEDURE GetRecentSalesHelper ( @date DATETIME )

BEGIN

SELECT SUM(d.[OrderQty])

FROM [dbo].[SalesOrderHeader_test] h ,

[dbo].[SalesOrderDetail_test] d

WHERE h.[SalesOrderID] = d.[SalesOrderID]

AND h.[OrderDate] > @date

END

ALTER PROC GetRecentSales ( @date DATETIME )

BEGIN

IF @date IS NULL

SET @date = DATEADD(mm, -3,

( SELECT MAX([OrderDate])

FROM [dbo].[SalesOrderHeader_test]

) )

EXEC GetRecentSalesHelper @date

END

18.6.3. 临时表和表变量 834

· SQL会像对普通表一样，在临时表上维护统计信息，用户也可以在上面建立索引。而表变量上，既不能建立索引，也不会有统计信息。SQL在做执行计划的时候，总是认为表变量里的数据量只有很少的几行，大都会选择Nested Loops。和表变量很不相同的是，SQL会在select语句之前，根据需要触发了一个自动创建统计信息auto stats created事件。建立了统计信息以后，SQL就知道临时表里有很多数据了。join的方式就改变了，性能就会好很多。

· 但表变量可以作为存储过程的返回参数，而临时表不行。

· 所以表变量的好处是，它的维护成本很低，大量并发使用时对系统的负担比临时表要低。但是缺点是没有统计信息，存放大量的数据时性能很难保证。所以，表变量比较适合存放一些很小（几十行或更小）的结果集。

· 临时表的好处是，它的功能和普通用户表接近，能够为大数据集做优化。但是缺点是维护成本高。大量并发使用临时表，会对系统带来比较重的负荷。所以临时表比较适合存放一些大的结果集。

18.6.4. 尽可能限定语句的复杂度 836

如果语句非常复杂，会给为调优带来了很大麻烦。这里列举出一些容易产生复杂语句的情形。要小心使用。

· （1）动态语句

一些应用为了实现客户端的灵活性，会根据用户的选择，动态拼出TSQL语句，发给SQL运行。这样在功能上来讲比较强大，但是在复杂度控制上就有可能会出问题。如果用户选择的条件太多，或者根据条件返回的记录太多，就有可能会造成问题。而有些能够过滤大量数据，或者在索引上的条件如果没有被选上，就有可能造成在大表上的table scan。最好在程序里有动态语句复杂度的控制机制，限制选择的条件，限制返回记录的数量

· （2）表格联接的数量

为了支持复杂的业务逻辑，一个应用往往会有成百上千的表格，一些查询往往会联接十几张甚至几十张表。应用设计的时候对这样的查询要很慎重。如果表格很大，十几张表做联接，肯定不会有好的性能。如果应用是支持OLAP系统，那可能还好。如果应用是一个OLTP系统，这样的设计失败的风险可能会很大。有时候可能需要降低数据库范式级别，多保存一些冗余数据列，以减少表格联接的数量

· （3）视图和存储过程的深度

视图和存储过程能够抽象出一些业务逻辑，简化设计，是很推荐的做法。但是如果在引用视图和存储过程时不加注意，视图套视图，存储过程嵌存储过程，最后嵌套上四五层，那复杂度累积起来，可能会超出你想象。对SQL的优化，也是很严重的考验。所以在引用它们的时候，也要考虑累积的复杂度

· （4）不必要的排序和计算

对一个大结果集做排序，或者求唯一值，都是比较昂贵的计算，会占用大量系统资源。如果用户对结果集排序或唯一性的要求不高，可以适当去掉这些计算

· （5）超大结果集申请和返回

如果根据用户选择的过滤条件，SQL会返回十几万条记录，那应用层该如何处理？如果一次性返回给应用层，那应用层要缓存和处理这么多记录，自己的性能会受到很大的挑战。如果一次只取一部分记录，其它记录由SQL代为缓存（一般是应用服务器端游标），那不但会给SQL的内存使用带来负担，而且容易产生阻塞问题。如果应用层处理得不好，甚至会产生内存泄漏的问题。所以程序设计的时候，要确保应用只会申请合适的、有必要的结果集。

· （6）用多个简单语句替代一个复杂语句

如果一个复杂的语句有很多张表要联接，要做很多计算，很多时候，要根据表和表的逻辑关系，知道某一张表和另一张表如果先做联接，可能会过滤掉更多数据。得到的小的结果集再做其它联接，会更快。类似的，有些计算可以先做，也可以后做，人在了解了表格的逻辑之后，会知道是先做好还是后做好。可惜SQL作为一个计算机程序，在这方面没有人那么聪明。当语句太复杂的时候，它有可能看不出来了。为了提高性能，对这种特别复杂的语句，可以把一句话拆成两句，甚至三句分步做完，中间结果集，可以以临时表的形式存放。这样做对程序员来讲做了很多事，但是对SQL来讲，大大简化了复杂度。很多时候对性能也会有帮助。

18.7. 小结 838

SQL Server 2012实施与管理实战指南摘要 - 第 18 章