SQL Server2005强制查询计划
[b]摘要[/b]
本篇文章解释了USE PLAN查询提示和SQL Server 2005中引入的计划指南功能。本文还演示了怎样个别或一起地使用它们，为各种各样的场景，强制指定的查询执行计划。
[b]目录[/b]
简介
目标读者
SQL Server查询优化
USE PLAN查询提示
USE PLAN查询计划的一般应用场景
USE PLAN查询提示的限制
计划指南
创建和管理计划指南
sp_create_plan_guide
sp_control_plan_guide
sys.plan_guides目录视图
计划指南的一般应用场景
计划指南 限制
最佳实践
支持的版本
小结
附录
[b]简介[/b]
在过去的几年里，Microsoft® SQL Server™越来越多的应用于工业领域并逐渐减少它的TCO。TCO的减少，是内建在Microsoft® SQL Server™里的众多自校正机制的直接结果。这些机制能够自动完成一些工作，而这些工作以前只能由一些有经验的数据库管理员来完成。这些机制的一个实现形式就是基于成本的优化程序（CBO），它被用作动态地生成查询执行计划。CBO探查多个系统级别的资源状态，并使用许多复杂的启发式算法，为已知的查询和底层的表和索引结构，创建最优的查询计划。这种机制对于绝大多数用户查询都表现的非常好，但是也有时候，一些有经验的用户基于一些以前的知识或基于对未来应用的了解，需要强制一个特殊的查询计划。
强制查询执行计划，到一个有限的范围，在以前版本的SQL Server中，就已经可以通过用各种查询提示、联接提示和索引提示实现。但是，这种操作通常要进行反复试验并十分乏味。SQL Server 2005引入了一种新的查询提示，叫做USE PLAN，它引导优化器基于指定的XML查询计划生成查询计划。
计划指南是SQL Server 2005中的新功能，它提供了一种方法，向成批的SQL语句、存储过程（SP）等中注入查询提示。但是，我们并不需要对查询本身做任何的修改。
本篇文章结合典型的应用场景、限制和推荐的最佳实践，说明了USE PLAN查询提示和计划指南功能。本文只呈现了这个功能的概观，并不打算作为一个综合性的参考文档提供给读者。相关详细信息，请查阅SQL Server 2005联机丛书。附录中也包含了一个现实中端到端场景，在这个场景中，一个已经部署的应用程序的查询性能的问题，通过使用这些新功能而得到解决。
[b]目标读者[/b]
本文主要提供给以下读者：
• 希望提高查询性能的数据库管理员 (DBAs)
• 希望容易的检验出不同的查询计划选项的应用程序测试员
[b]SQL Server查询优化[/b]
SQL Server用一个复杂的基于成本的查询优化机制，这种机制在指定查询执行计划前考虑各种因素。在经过编译之后，SQL Server引擎会将查询计划缓存，以免同样的查询再次执行时还要重复相同的工作。查询计划根据基础表中特定的数据而被优化。因此，SQL Server引擎会一直监视基础表的变化，并且，当它发现数据已经发生巨大改变，并且由于这种改变必须要对查询计划进行再优化时，它将对其进行重新编译。
这种机制对于大多数查询都表现的非常良好。但是，比如一个非常不齐整的数据集，巨大的数据表中的陈旧数据，或当查找最佳查询计划时优化器超时等等，这些情况都会导致优化器生成的查询执行计划并非最优的。
[b]USE PLAN查询提示[/b]
Microsoft® SQL Server™ 2005引入了一种新的查询提示，叫做USE PLAN，它可以用来引导查询优化器选择一个指定的XML查询计划。这种强大的功能，使用户能够对一个查询执行的影响进行完全控制。
早期版本的SQL Server提供了查询提示的功能，像FORCE ORDER，LOOP JOIN，和KEEP PLAN，它会以一个特定的方式帮助优化查询。但是，没有一个足够强大，能够影响优化器一贯地选择一个特定的查询计划，特别是当被引用的表的行数，数据，索引和其它环境属性发生改变时。USE PLAN查询提示弥补了这个缺陷，并使用户能够完全控制查询计划的稳定性。
USE PLAN查询提示在OPTION从句中指定，并跟着一个XML showplan。下面的示例指定USE PLAN查询提示来影响一个联接类型的简单查询，这个查询由两个表之间的一个联接组成：
下面是原来的查询：
SELECT count(*) AS Total
FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
WHERE h.SalesOrderID = d.SalesOrderID
GO
下面是指定USE PLAN查询提示的相同查询：
SELECT count(*) AS Total
FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
WHERE h.SalesOrderID = d.SalesOrderID
OPTION (USE PLAN N'
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="0.5" Build="9.00.1187.07">
<BatchSequence>
<Batch>
<Statements>
…
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
')
GO
在这个例子中，USE PLAN提示和<xml showplan>通过跟在原来的SELECT查询后面的OPTION从句指定。出于可读性的目的，100行以上的XML查询计划用省略号代替。虽然这并不是一个有实际意义的示例，但是这个功能的真正能力在于，它能够为包括多个表的联接，包含谓词和聚合从句的复杂查询，强制查询计划。
[b]USE PLAN查询计划的一般应用场景[/b]
SQL Server对大多数查询生成最优查询。但有时候，特别是一些复杂的应用程序，一些特定的查询需要用户干预和一些手动的调节。下面是这样的一些典型示例：
• 一个复杂的查询，包括了多个表，编译或重编译的查询计划有时不是最优的。这种状态可能是由在其中的基础表中过时或丢失的数据所引起的。这也可能是由查询中复杂的结构造成的，比如这种查询可能引起优化器错误的估计中间查询结果的大小。
• 在一些案例中，在早期的版本中查询优化器选择的查询计划，优于升级后的版本的查询计划。其中，升级可能是通过一个Hotfix(QFE)，service pack，或一个全新版本的升级。
[b]USE PLAN查询提示的限制[/b]
USE PLAN查询提示有下列限制：
• 只有SELECT和SELECT INTO语句的查询计划可以被强制。这些语句可以引用表和视图。可是，这些表或视图可以被索引和分割。UPDATE、DELETE或INSERT语句的查询计划不能被强制。
• 对于优化器的正常搜索策略生成的查询计划，您只能用USE PLAN对其强制。这些计划是典型的左深度二叉树（但不总是），其中每个联接的一个孩子总是一个叶子结点（表扫描，索引扫描，或索引搜索）。图1说明了一个可以强制的示例。
[img]http://298853.blog.51cto.com/album/288853/119967769502.jpg[/img]
图1：查询执行计划示例
您不能够强制任意一颗矮树（至少一个JOIN节点的两个子节点都有JOIN节点的一棵JOIN树）。用过一组括如括号的在查询和OPTION(FORCE ORDER)提示的FROM从句中的JOIN操作，您可以用一个矮结构创建计划。但是，尝试强制这样的一个查询计划将会引发错误。由于这些创建一个有效计划的局限性和复杂性，创建查询计划最常用并且可靠的方法，就是捕捉优化器声称的查询计划，并在相同的查询上强制这个查询计划。
• 用联接提示、查询提示(OPTION从句)和索引提示重写查询，是提高SQL Server自动生成的查询计划的性能的一个好方法。在许多情况下，您可以成功的在原有查询的基础上强制计划。关于怎样应用这个技术的更多信息，请查阅SQL Server 2005联机丛书主题“Plan Forcing Scenario: Create a Plan Guide to Force a Plan Obtained from a Rewritten Query.”。
有时候，使用联接提示、FORCE ORDER、或SET FORCEPLAN ON所产生的计划，可能与使用USE PLAN提示产生的计划不匹配。通常，如果你用FORCE ORDER提示创建了一个查询并为查询取得XML showplan，然后尝试用USE PLAN强制同样的查询并删除FORCE ORDER提示，这时SQL Server可能不会找到一个查询计划。这是因为FORCE ORDER提示覆盖了优化器典型的搜索策略。相似的，如果计划是用SET FORCEPLAN产生的，那么就不能为查询强制一个计划。
• 应用USE PLAN提示，如果一个查询包含一个大于8KB的XML计划，那么这个查询的查询计划将不会被缓存。计划指南可以帮助更好的指定查询提示。
[b]计划指南[/b]
有时候，应用程序性能调解需要查询计划通过一个或多个查询提示影响，这些查询计划可能是一个特定的查询或一组查询所生成的。虽然这对于用户可以访问程序代码的情况来说非常简单，但是通常用户不能访问代码，并且要修改的查询代码被嵌入在一个第三方的应用程序中。这样，改变实际上不可能实现。
计划指南功能为这种场景提供了一个理想的解决方案，并且使用户可以向原有的查询中注入提示。这种机制使用一个内部的查找系统表，该表基于sys.plan_guides目录视图中的信息，将原来的查询映射到一个代替的查询或模板。下面的图2描述了设计查询映射处理的操作流程。
[img]http://298853.blog.51cto.com/album/288853/119967779165.jpg[/img]
图2： 计划指南匹配流程图
每个SQL查询语句首先比较优化器的缓存计划存储，并检查匹配。如果存在一个匹配的，那么缓存的查询计划被用作执行查询。如果不存在，那么再检查在现有数据库的计划指南中有没有与查询所匹配的计划。如果一个有效的计划指南存在，并匹配语句和上下文（batch，SP或其它模块），那么原来匹配的语句被计划指南中的一个所替代。在这之后，查询计划被编译和缓存，然后执行查询。
应用计划指南功能单独的指定以下查询提示，或和其它可用的提示一起指定
• {HASH | ORDER} GROUP
• {CONCAT | HASH | MERGE} UNION
• {LOOP | MERGE | HASH} JOIN
• FAST number_rows
• FORCE ORDER
• MAXDOP number_of_processors
• OPTIMIZE FOR ( @variable_name = literal_constant ) [ ,…n ]
• RECOMPILE
• ROBUST PLAN
• KEEP PLAN
• KEEPFIXED PLAN
• EXPAND VIEWS
• MAXRECURSION number
• USE PLAN <xmlplan>
计划指南功能本质上由两个存储过程（可以用来创建、删除、启用和禁用计划指南）和一个新的元数据视图（描述存储计划指南）组成。下面的小节描述了设计创建计划指南的存储过程，并介绍了几个应用它们的场景。
[b]创建和管理计划指南[/b]
通过使用下面的两个系统存储过程来创建和管理计划指南：
• sp_create_plan_guide
• sp_control_plan_guide
[b]sp_create_plan_guide[/b]
The sp_create_plan_guide存储过程用作创建一个计划指南。下面是它的命令格式：
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 ] )' | NULL }
其中：
@name 为navrchar(128) 类型并指定计划指南的名称。计划指南名称是数据库范围内的并且作用域为当前数据库。
@stmt为navrchar(max)类型并包含一个T-SQL语句或批处理。
@type为nvarchar(60)类型并指定实体的类型，计划指南将依此来匹配。下面是@type的有效值：
• OBJECT：用来指定statement_text是在T-SQL存储过程，标量函数，对语句表值函数或T-SQL DML 触发器的上下文中出现
• SQL：用来指定所给的statement_text在一个独立的语句或批处理的上下文中出现，其中语句或批处理可能通过任何方式提交给数据库。
• TEMPLATE：用来指出计划指南应用于所有查询，每个查询都按statement_text所指示的进行参数化。
@module_or_batch为nvarchar(max)类型并指定模块名称或批处理的征文。如果@module_or_batch为NULL（默认值）并且@type = 'SQL'，那么@module_or_batch被设为@stmt。
@params是nvarchar(max)类型并表示一个字符串，这个字符串为一个T-SQL批处理包含所有的参数定义，而这些参数需要用计划指南来匹配。如果@type=’SQL’， @module_or_batch和@params的值一起唯一标识了一个传入的T-SQL批处理。@params中提供的参数必须与sp_executesql提供的SQL批处理提供的参数完全一致，且每个字符都要匹配。当您为参数化的动态SQL定义一个计划指南时，您应当提供一个@params参数。如果您为@params指定了一个非空值，那么@type的值必须是'SQL' 或 ‘TEMPLATE’。每个参数定义由一个参数名称和一个数据类型组成。n是一个占位符，并指出附加的参数定义。如果@module_or_batch中的T-SQL语句或批处理不包含参数，那么@params必须为NULL。这是默认的规定。
@hints为nvarchar(max)类型并指定OPTION从句文本附加到匹配@stmt的一个输入查询上。在语法上，它必须和SELECT语句中的OPTION从句相同，或者为NULL，它表示没有OPTION从句。它可以包含任意合法的查询提示序列。
传入sp_create_plan_guide的所有参数必须是指定类型的常量，或是可以隐式转换到这种类型的变量，并且它们必须符合存储过程的标准调用约定。参数中的常量字符串必须是Unicode字符串，并用符号N’…’来指定。所有的参数或指定为‘@name=value”格式或直接是“value”格式，因为混合这两种方法会导致sp_create_plan_guide执行过程中的错误。
下面是为一个简单的SQL语句生成计划指南的示例：
sp_create_plan_guide
@name = N'PlanGuide1',
@stmt = N'SELECT COUNT(*) AS Total
FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
WHERE h.SalesOrderID = d.SalesOrderID and h.OrderDate BETWEEN ''1/1/2000'' AND ''1/1/2005''
',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (MERGE JOIN)'
GO
当创建计划指南时，您必须要小心指定参数@stmt，并且参数@params的参数名称和值就是从程序中获得的。这可以通过从SQL Profiler中捕捉批处理或语句文本来实现，就像SQL Server联机丛书中提到的那样。单引号注明的文字值，比如‘1/1/2000’，应当再用一个单引号标明，使它和外层的单引号区分开来，就像上面的示例中所写的那样。
[b]sp_control_plan_guide[/b]
sp_control_plan_guide存储过程用来启用、禁用、或删除一个计划指南。下面是该命令的格式：
sp_control_plan_guide [ @operation = ] N'<control_option>' [ , [ @name = ] N'plan_guide_name' ]
<control option>可以是：
DROP – 用来删除plan_guide_name 指定的计划指南。
DROP ALL – 用来删除当前数据库中所有的计划指南。
DISABLE - 用来禁用plan_guide_name 指定的计划指南。
DISABLE ALL – 用来禁用当前数据库中所有的计划指南。
ENABLE - 用来启用plan_guide_name 指定的计划指南。
ENABLE ALL – 用来启用当前数据库中所有的计划指南。
例如：
sp_control_plan_guide N'DROP', N'PlanGuide1'
为了在OBJECT 类型的计划指南（由@type = 'OBJECT'指定）中执行sp_control_plan_guide，您至少需要拥有计划指南所引用的对象上的ALTER权限，对于所有其它的计划指南，您必须拥有ALTER DATABASE权限。试图删除或修改计划指南所引用的函数、存储过程或DML触发器，都将会导致一个错误。
[b]sys.plan_guides目录视图[/b]
所有的计划指南都存储在sys.plan_guides数据库系统目录视图中。图3中显示了测试数据库中所有的计划指南的一个列表。
[img]http://298853.blog.51cto.com/album/288853/119967795622.jpg[/img]
图3：测试数据库中计划指南的列表
通过查询这个系统视图，可以访问到计划指南的一些详细信息，比如他的创建时间，修改日期是否已被启用以及查询文本。
[b]计划指南的一般应用场景[/b]
本小节展示了一些应用场景，其中计划指南可以被用来影响优化器生成的查询执行计划。
• 参数化查询
参数化T-SQL查询是众所周知的数据库编程的最佳实践。他允许查询执行计划被重用，并且对于仅仅参数不同的相同查询的调用，不必再为其进行重新编译。但有时候，因为被缓存的查询计划是相对于不具有代表性的参数值所作的优化，因此这种参数化查询运行的性能可能很差。
在这些情况下OPTIMIZE FOR 或 RECOMPILE查询提示可以用来解决这些问题。OPTIMIZE FOR指示SQL Server对OPTIMIZE FOR 从句中指定的参数值做出优化。当参数只有一个最优值，并且DBA很容易的知道时，这种选项会非常有用。RECOMPILE指示服务器在查询执行完成后删除查询计划，并强制查询优化器在相同的查询连续的执行 时对其进行重新编译。当查询拥有大量的不同的参数值，并频繁执行时，RECOMPILE提示将会非常有用。
下面是一个使用计划指南的示例，其中计划指南强制查询计划为值@Country=’US’做优化：
sp_create_plan_guide N'PlanGuide2',
N'SELECT *
FROM Sales.SalesOrderHeader h, Sales.Customer c, Sales.SalesTerritory t
WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID
AND CountryRegionCode = @Country',
N'OBJECT',
N'Sales.GetSalesOrderByCountry',
NULL,
N'OPTION (OPTIMIZE FOR(@Country = N''US''))'
• 强制查询计划
另外一个和计划指南一起常用的提示是前面讨论过的USE PLAN查询提示。对于一些特定的查询，您可能知道一个比优化器更好的查询计划，这时就可能会用到这个提示。如果它是优化器选择过程中考虑到的查询计划之中的一个，那么当执行该查询时，USE PLAN强制SQL Server 使用该查询计划，并在提示语法中显示指定它的名称。这就意味着任意一个随便写的或者手动修改的查询计划不能通过USE PLAN强制。
• 删除或代替原有的查询提示
虽然这并不是一个常用的或值得推荐的方法，但有时候需要应用程序直接指定查询提示。在下面的示例中，查询应用像这样的一个查询提示来强制一个嵌套循环联接：
SELECT t1.a, t2.b FROM Tab1 t1, Tab1 t2 WHERE t1.a = t2.a
OPTION(LOOP JOIN)
GO
当这些查询提示引发问题并需要删除时，就需要在创建计划指南时指定参数@hint为NULL。下面的示例中，就删除了前面应用程序的T-SQL语句中嵌入的循环联接提示：
sp_create_plan_guide
@name = N'PlanGuide1',
@stmt = N'SELECT t1.a, t2.b FROM Tab1 t1, Tab1 t2 WHERE t1.a = t2.a
OPTION(LOOP JOIN)
',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = NULL
GO
如果想用其它的提示来替换应用程序提供的JOIN循环提示，那么可以通过@hint选项来指定所需要的联接类型。
例如：要强制一个合并联接，在创建计划指南时可以指定@hint = N’OPTION (MERGE JOIN)’。
• 强制一个非并行执行计划
当运行在一个多处理器系统中时，SQL Server 基于预先规定的标准，在运行时决定一个查询通过并行还是非并行的查询计划来执行。这时SQL Server 的默认的行为，并且有助于提高复杂查询的执行速度。有时候，DBA喜欢一贯的用非并行的查询计划来强制一个特殊查询的执行。这可以通过用MAXDOP=1提示创建计划指南来实现，如下所示：
sp_create_plan_guide
@name = N'PlanGuide3',
@stmt = N'SELECT TOP 1 * FROM Sales.SalesOrderHeader h ORDER BY OrderDate DESC
',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (MAXDOP 1)'
• 强制联接类型
计划指南也可以用来强制一个特殊查询中的联接类型。下面的计划指南示例中，计划指南强制两个表间的合并联接：
sp_create_plan_guide
@name = N'PlanGuide4',
@stmt = N'SELECT FirstName, LastName, City, VacationHours
FROM HumanResources.Employee e, Person.Contact c, Person.Address a, HumanResources.EmployeeAddress ea
WHERE e.EmployeeID = ea.EmployeeID
AND ea.AddressID = a.AddressID
AND e.ContactID = c.ContactID
',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (HASH JOIN)'
GO
这将强制优化器用一个hash match 类型的联接代替所有的联接操作符 。
• 参数化控制
SQL Server 2005引入了两个新的选项来强制查询的参数化：FORCED PARAMETERIZATION 和 SIMPLE PARAMETERIZATION。 FORCED PARAMETERIZATION选项强制所有查询的参数化。（其中有一些例外在SQL Server 2005联机丛书中列出。）另一方面，SIMPLE PARAMTERIZATION选项让SQL Server 查询优化器决定是否参数化查询，这两个选项均为数据库级别的选项，并且可以使用ALTER DATABASE命令启用。
在某些情况下，为一个查询指定其中的一个提示是非常有益的。例如，一个数据库可能设定默认的参数化为SIMPLE，并且一个DBA可能只希望下面的查询被强制参数化：
SELECT Product.ProductNumber, Product.ReorderPoint, SUM(ProductInventory.Quantity) AS Quantity
FROM Production.Product, Production.ProductInventory
WHERE Product.ProductID = ProductInventory.ProductID
AND Product.ProductNumber = N'BE-2908'
AND Product.ReorderPoint > 0
GROUP BY Product.ProductID, Product.ProductNumber, Product.ReorderPoint
这可以通过为查询创建一个计划指南模版来实现。要创建一个模版计划指南，首先应该使用sp_get_query_template存储过程提取模版文本和参数定义，然后使用sp_create_plan_guide存储过程创建计划指南模版。例如：
DECLARE @stmt nvarchar(max)
DECLARE @params nvarchar(max)
EXEC sp_get_query_template N'SELECT Product.ProductNumber, Product.ReorderPoint, SUM(ProductInventory.Quantity) AS Quantity
FROM Production.Product, Production.ProductInventory
WHERE Product.ProductID = ProductInventory.ProductID
AND Product.ProductNumber = N''BE-2908''
AND Product.ReorderPoint > 0
GROUP BY Product.ProductID, Product.ProductNumber, Product.ReorderPoint',
@stmt OUTPUT,
@params OUTPUT
EXEC sp_create_plan_guide N'PlanGuideTemplate1',
@stmt,
N'TEMPLATE',
NULL,
@params,
N'OPTION(PARAMETERIZATION FORCED)'
在创建完成后，计划指南 模版会用这种格式匹配所有的查询执行。不管与ProductNumber和ReorderPoint比较的文字值是什么，它都会这样做，并因此会便于查询计划的重用和查询编译次数的减少。计划指南 模版的匹配，或者通过观察两个变量确实在查询执行计划中被参数化来验证，或者通过在showplan_xml输出列表中查找‘TemplatePlanGuideDB’ 和‘TemplatePlanGuideName’来验证。
[b]计划指南 限制[/b]
本小结列出了一些使用计划指南的一些限制。
• 修改和删除底层对象
在一个函数、存储过程、或一个计划指南引用的DML触发器上创建一个计划指南之后，如果您试图修改或删除那个对象，无论它是否启用都会引发错误。
• 数据库作用域
所有的计划指南都拥有数据库级别的作用域。也就是说，计划指南的名称在整个数据库中必须是唯一的。但是，不同的数据库可以含有相同名称的计划指南。
• DML支持
计划指南可以像一个OPTION从句一样，应用于所有能够接受查询提示的语句。包括：SELECT, UPDATE, DELETE, 和 INSERT…SELECT语句。
• 查询文本匹配
为了给指定@type = 'SQL'或'TEMPLATE'的计划指南成功的匹配一个查询，batch_text和@parameter_name data_type[,…n]的值必须和应用程序提供的相应的值的格式完全一致。特别的，匹配是一个字符一个字符完成的，包括注释和空格。
• 加密对象
不能为指定WITH ENCRYPTION从句的存储过程，函数或DML 触发器创建计划指南。
• DDL 触发器
计划指南不能为DLL触发器指定，而只支持DML触发器。
[b]最佳实践[/b]
下面是一些使用USE PLAN查询提示和计划指南功能的最佳实践：
• 当其它标准的查询调节方法，如索引调节，已经广泛尝试并不能得到预期的结果时，应当使用USE PLAN查询提示和计划指南。
• 只有有经验的数据库管理员才能使用这些功能，因为他们知道强制执行计划所带来的牵连和长期的影响。在使用USE PLAN查询提示或一个计划指南强制一个查询计划后，它将会锁定并阻止优化器修改数据分配，新建索引，以及在后续的SQL Server发版本，service pack和HotFix中改良查询执行算法。
• 您应当尝试强制整个工作量中的一小部分。如果您强制了很多的查询，那么请检查是否存在与配置相关的其它问题可能会限制性能。这些可能包括不足的系统资源，不正确的数据库配置设定，索引的丢失以及其它因素。
• 我们不建议您手动编码或者修改USE PLAN查询提示中制定的XML showplan。XML showplan是一个非常长并且复杂的序列。任何修改都可能会组织它与查询优化器中生成的计划匹配，这时会导致USE PLAN提示实效。您应当在USE PLAN中使用SQL Server生成的计划。
• USE PLAN查寻提示最好不要嵌入到应用程序代码中，因为这样会使应用程序的维护和SQL Server版本的改变变得非常困难。直接将USE PLAN嵌入在查询中同样会使查询计划无法缓存。USE PLAN提示主要用来性能调节和测试，以及和计划指南功能一起使用。
• 为一个应用程序创建的计划指南应当很好的验证，并整齐的备份，因为他们构成了应用程序性能调节的主要部分。同样，您也应当保留您用来创建计划指南的脚本，并向对待其它源代码一样对待它们。
• 在创建以后，计划指南应当通过测试来保证它被用到指定的查询中。这可以通过验证Showplan XML列表(该列表由Profiler Showplan XML事件所生成，或由SET SHOWPLAN_XML ON生成)确实包含PlanGuideDB和PlanGuideName属性来实现，因为您期望的计划指南应当匹配这个查询。
[b]支持的版本[/b]
下表表明了在不同的SQL Server 2005的版本中，对USE PLAN和计划指南功能的支持：
表1
[img]http://298853.blog.51cto.com/album/288853/119967824057.jpg[/img]
[b]小结[/b]
通过引入USE PLAN查询提示和计划指南功能，SQL Server 2005较大程度地提高了用户控制优化器行为的能力。您可以单独或一起使用这些功能，并通过大量的查询提示来调优查询执行计划，维持查询计划的稳定性。
[b]附录[/b]
这篇附录提供了一个应用程序的性能调节的示例场景－AdventureWorksApplication。这个应用程序对AdventureWorks示例数据库进行操作。下面的小节中介绍了问题场景，分析过程，和解决方法。附录中的查询只是为了举例，而没有任何实际的作用。
[b]场景[/b]
AdventureWorksApplication的用户感觉到，他们的产品信息目录的搜索请求，有时候需要花费过多的时间来返回结果。但有时候，相应的时间也很快。
[b]分析[/b]
因为这种间断性的问题之前DBA没有发现过，所以确实很难应付。为了对问题做一个完全的调查，他们获得了对系统性能的整体的了解，并实现性能调节的最佳实践。首先，他们通过Windows perfmon和SQL Profiler监视数据库服务器。对于Windows perfmon，他们将所有的SQL Server对象和关键的系统对象记入一个perfmon日志文件，如network，memory，processor，和disk。同样，他们设定SQL Profiler捕捉所有的执行时间大于600毫秒的查询。其次，他们24小时监视数据库服务器，并确信在那段时间内，应用程序用户确实多次遇到问题。
在分析了perfmon捕捉下的日志后，他们发现并没有严重的系统瓶颈，并且SQL Server计数器的值相当稳定并在标准的界限内。确信没有主要资源瓶颈之后，DBA将注意力转向SQL Profiler数据。
在24小时内，SQL Profiler捕捉到了大约280次查询，其执行时间大于600毫秒。经过全面的比对和分析，DBA发现所有的280个查询只是三个查询的不同的调用。
对于这三个查询，他们发现其中一个是因为一个优化索引丢失，并因此执行全索引扫描。第二个查询涉及两个非常大的表的联接，并依靠优化器的判断，使用了一个次优索引，并导致生成了一个很差的查询执行计划。通过进一步的分析，他们发现其中一个大表的自动的更新统计选项被关闭了，可能是他们以前在做实验时不小心关闭的。
第三个查询，通过一个API服务器游标 提交并在SQL Profiler中被捕获，就像下面所示，看上去像是所有的索引都正确和表上相对较新的统计。而且，当在SQL Server Management Studio中执行时间不到50毫秒。
declare @P1 int
set @P1=-1
declare @P2 int
set @P2=0
declare @P3 int
set @P3=28688
declare @P4 int
set @P4=8193
declare @P5 int
set @P5=2560
exec sp_cursorprepexec @P1 output, @P2 output, N'@P1 char',
N'SELECT p.Name AS ProductName, v.Name AS VendorName, p.ProductLine
FROM Production.Product p, Purchasing.ProductVendor pv, Purchasing.Vendor v
WHERE p.ProductID = pv.ProductID
AND pv.VendorID = v.VendorID
AND p.ProductLine = @P1
ORDER BY p.Name, v.Name', @P3 output, @P4 output,
@P5 output, 'Z'
起初，这让人觉得十分莫名其妙。通过一些实验和分析，最终DBA发现，当查询的参数值@P1为‘M’，‘R’，或‘T’时，生成的查询计划相应的执行时间确实不到50毫秒。但是，当参数值为其它值时，查询计划将会发生巨大的变化。他们将这解释为与优化器优化查询使用的特定的值相关。
总之，他们确定当进行编译或重编译时，提供通常的值以外的值，会引起优化器为那个特定的值编译并缓存查询计划。但是，当而后的通常的值（‘M’，‘R’，或‘T’）被提供给查询时，那个查询计划将会被重用，但它并不是最优的，因此会引起性能下降。这个次优的性能问题在查询下次编译之前一直存在。基于这一点，@P1参数值的提供，将使查询可能产生性能问题，也可能没有问题。
[b]解决方法[/b]
针对第一个查询问题，DBA在表上创建了丢失的索引，并验证索引确实被优化器所选择。
对于第二个查询，他们启用了曾经偶然间关闭的自动的更新统计选项，然后手动更新了那个表上的所有索引的统计，以保证统计是最新的。这样使得优化器选择了正确的索引，并得到了令人满意的查询性能。
从分析中，DBA知道了为了保证第三个查询有一个优良的执行计划，他们必须保证当@P1参数为通常的值（‘M’，‘R’，或‘T’）时，查询必须要进行编译。所以他们使用了OPTIMIZE FOR查询提示。但是，因为查询发生在AdventureWorksApplication里，直接修改查询并添加这个提示是不可能的。
这样，他们开始求助于用OPTIMIZE FOR创建一个计划指南。下面的步骤说明了他们是如何做的：
收集查询计划
1. 启动SQL Server Profiler 跟踪。
2. 从存储过程组中选择RPC:Starting事件。
3. 引发AdventureWorksApplication执行该查询。
4. 定位查询时产生的RPC:Starting事件。
5. 收集查询：右击包含查询的RPC:Starting事件并选择Extract Event Data选项。
6. 将事件数据保存到文件CursorQuery.sql中。
7. 在SQL Server Management Studio中打开CursorQuery.sql。
8. 通过查找且替换的快速替换，将所有的单引号(')替换为双引号('')。
9. 将sp_cursorperpexec调用的查询部分复制到系统缓冲器中。
10. 保存CursorQuery.sql。
[b]创建查询指南[/b]
• 通过执行下面的sp_create_plan_guide语句并指定OPTIME FOR查询提示来创建一个计划指南：
sp_create_plan_guide
@name = N'AppendixPlanGuide',
@stmt = N'SELECT p.Name AS ProductName, v.Name AS VendorName, p.ProductLine
FROM Production.Product p, Purchasing.ProductVendor pv, Purchasing.Vendor v
WHERE p.ProductID = pv.ProductID
AND pv.VendorID = v.VendorID
AND p.ProductLine = @P1
ORDER BY p.Name, v.Name',
@type = N'SQL',
@module_or_batch = NULL,
@params= N'@P1 char',
@hint = N'OPTION (OPTIMIZE FOR(@P1 = N''M''))'
参数@stmt提供的文本从系统缓冲器中传入，它是在step 9 收集查询计划过程中载入的。
执行查询并验证计划指南被使用
1. 使AdventureWorksApplication 再次执行查询，并通过选择SQL Server Profiler中的Performance组下面的Showplan XML Statistics Profile事件来收集它的XML执行计划。
2. 收集查询计划：右击与查询相符的Showplan XML Statistics Profile事件，并选择提取事件数据选项。
3. 将事件数据保存在为文件Showplan.SQLPlan到你的桌面上。
4. 将文件名Showplan.SQLPlan改为Showplan.xml。
5. 用IE打开Showplan.xml文件。您会发现XML showplan输出中包含‘PlanGuideDB’和‘PlanGuideName’标记，如下所示：
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.0" Build="9.00.1282.00">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple PlanGuideDB="AdventureWorks" PlanGuideName="AppendixPlanGuide"> …
…
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
[b]版权所有[/b]
这是一份初版文件，可能会于本软件产品正式发行之前依实况进行必要的修订。
本文件中所包含的信息代表 Microsoft Corporation 于发行日前针对该问题的观点。由于 Microsoft 必须反应市场条件的变更，因此不应解释为 Microsoft 的承诺。在发行日之后，Microsoft 不保证文件中任何信息的正确性。
此白皮书仅供参考使用。MICROSOFT 对于本文件中各项信息，不作任何明示、暗示或法定的保证。
使用者必须遵守所有适用的版权法律规定。在不影响版权各项权利的情况下，若无 Microsoft Corporation 的明确书面许可，本文件中的任何部份均不得因任何目的，以各种可能的形式或方式 (电子、机械、影印、录制或其它方式) 进行复制、储存或导入至检索系统或传送。
本文件中可能述及 Microsoft 的专利、专利应用程序、商标、版权或其它智能财产权。除非取得 Microsoft 明确书面授权声明，否则本文件并未授予这些专利、商标、版权或其它智能财产的授权。
除非另有说明，本文档中用作示例的公司、组织、产品、域名、电子邮件地址、徽标、人员、地点和事件均是虚构的，并不是有意或者不应推断为与任何真实的公司、组织、产品、域名、电子邮件地址、徽标、人员、地点或事件有关联。
2005 Microsoft Corporation。保留所有权利。
Microsoft、ActiveX、和 SQL Server 是 Microsoft Corporation 在美国和/或其它国家/地区的注册商标或商标。
本文档中提到的真实公司和产品的名称均是各自所有者的商标。 |