SQL Server的查询优化器详解

SQL Server的查询优化器在select查询执行的时候产生一个高效的查询执行计划。如果优化器不能选择最优的计划，那么就需要检查查询计划、统计信息、支持的索引等，而通过使用提示可以改变优化器选择查询计划的工程，使优化器生成一个更好的执行计划。

1、联接提示

<join_hint> ::= { LOOP | HASH | MERGE | REMOTE }

LOOP | HASH | MERGE

指定查询中的联接应使用循环、哈希或合并。使用 LOOP |HASH | MERGE JOIN 在两个表之间强制执行特定联接。不能同时将 LOOP 与 RIGHT（或 FULL）指定为联接类型。

REMOTE

指定联接操作在右表处执行。这在左表是本地表而右表是远程表的情况下很有用。只在左表的行数少于右表行数时才能使用 REMOTE。

如果右表为本地表，则联接在本地执行。如果两个表均为远程表但来自不同的数据源，则 REMOTE 将使联接在右表处执行。如果两个表均为远程表且来自相同数据源，则不需要使用 REMOTE。

如果使用 COLLATE 子句将联接谓词中比较的值中的一个值转换成了不同的排序规则，则不能使用 REMOTE。REMOTE 只可用于 INNER JOIN 操作。

 
 --没有提示的查询 
select * 
from Production.Product p  
innerjoin production.productreview r 
       on r.productid = p.productid 
 
 
--Loop适合：当一个表很小另一个表很大、在关联的列上有索引时 
select * 
from Production.Product p  
inner loop join production.productreview r  
              on r.productid = p.productid 
 
 
--merge适合：按照关联列排序的中等或者大的表        
select * 
from Production.Product p 
inner merge join production.productreview r 
              on r.productid = p.productid 
 
--hash适合：没有排序的大的表 
select * 
from Production.Product p 
inner hash join production.productreview r 
             on r.productid = p.productid     
              

--没有提示的查询
select *
from Production.Product p 
inner join production.productreview r
       on r.productid = p.productid


--Loop适合：当一个表很小另一个表很大、在关联的列上有索引时
select *
from Production.Product p 
inner loop join production.productreview r 
              on r.productid = p.productid


--merge适合：按照关联列排序的中等或者大的表       
select *
from Production.Product p
inner merge join production.productreview r
              on r.productid = p.productid

--hash适合：没有排序的大的表
select *
from Production.Product p
inner hash join production.productreview r
             on r.productid = p.productid

2、查询提示与表提示

          

<query_hint > ::= 
{ { HASH | ORDER } GROUP 
  | { CONCAT | HASH | MERGE } UNION 
  | { LOOP | MERGE | HASH } JOIN 
  | EXPAND VIEWS 
  | FAST number_rows 
  | FORCE ORDER 
  | IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX
  | KEEP PLAN 
  | KEEPFIXED PLAN
  | MAXDOP number_of_processors 
  | MAXRECURSION number 
  | OPTIMIZE FOR ( @variable_name { UNKNOWN | = literal_constant } [ , ...n ] )
  | OPTIMIZE FOR UNKNOWN
  | PARAMETERIZATION { SIMPLE | FORCED }
  | RECOMPILE
  | ROBUST PLAN 
  | USE PLAN N'xml_plan'
  | TABLE HINT ( exposed_object_name [ , <table_hint> [ [, ]...n ] ] )
}

<table_hint> ::=
[ NOEXPAND ] { 
    INDEX ( index_value [ ,...n ] ) | INDEX = ( index_value )
  | FORCESEEK [( index_value ( index_column_name [,... ] ) ) ]
  | FORCESCAN
  | HOLDLOCK 
  | NOLOCK 
  | NOWAIT
  | PAGLOCK 
  | READCOMMITTED 
  | READCOMMITTEDLOCK 
  | READPAST 
  | READUNCOMMITTED 
  | REPEATABLEREAD 
  | ROWLOCK 
  | SERIALIZABLE 
  | SPATIAL_WINDOW_MAX_CELLS = integer
  | TABLOCK 
  | TABLOCKX 
  | UPDLOCK 
  | XLOCK

查询提示的参数

{ HASH | ORDER } GROUP

指定在查询的 GROUP BY 或 DISTINCT 子句中所说明的聚合应使用哈希或排列。

{ MERGE | HASH | CONCAT } UNION

指定所有 UNION 运算由合并、哈希或串联 UNION 集执行。如果指定了多个 UNION 提示，查询优化器就会从这些指定的提示中选择开销最少的策略。

{ LOOP | MERGE | HASH } JOIN

指定整个查询中的所有联接操作由 LOOP JOIN、MERGE JOIN 或 HASH JOIN 执行。如果指定了多个联接提示，则优化器从允许的联接策略中选择开销最少的联接策略。

如果在同一查询中的 FROM 子句中还为一对特定的表指定了联接提示，则尽管仍须遵守查询提示，但该联接提示将优先联接这两个表。因此，这对表的联接提示可能只限制选择查询提示中允许的联接方法。有关详细信息，请参阅联接提示 (Transact-SQL)。

EXPAND VIEWS

指定展开索引视图，而且查询优化器不将任何索引视图看作是查询中任何部分的替代。当视图名称由查询文本中的视图定义替换时，视图将展开。

实际上，该查询提示不允许在查询计划中直接使用索引视图和直接在索引视图上使用索引。

只有在查询的 SELECT 部分中直接引用视图，而且指定了 WITH (NOEXPAND) 或 WITH (NOEXPAND, INDEX(index_value [,...n ] ) ) 时，才不展开索引视图。有关查询提示 WITH (NOEXPAND) 的详细信息，请参阅FROM。

只有语句的 SELECT 部分中的视图（包括 INSERT、UPDATE、MERGE 和 DELETE 语句中的视图）才受提示影响。

FAST number_rows

指定对查询进行优化，以便快速检索第一个 number_rows.。该值是非负整数。在返回第一个number_rows 后，查询继续执行并生成完整的结果集。

FORCE ORDER

指定在查询优化过程中保持由查询语法指示的联接顺序。使用 FORCE ORDER 不会影响查询优化器可能的角色逆转行为。

注意
在 MERGE 语句中，如果未指定 WHEN SOURCE NOT MATCHED 子句，则按照默认的联接次序，先访问源表再访问目标表。如果指定 FORCE ORDER，则保留此默认行为。

KEEP PLAN

强制查询优化器对查询放宽估计的重新编译阈值。当通过运行 UPDATE、DELETE、MERGE 或 INSERT 语句对表进行的索引列更改数目达到估计数目时，会自动重新编译查询，该估计数目即为估计的重新编译阈值。指定 KEEP PLAN 可确保当表有多个更新时不会频繁地对查询进行重新编译。

KEEPFIXED PLAN

强制查询优化器不因统计信息的更改而重新编译查询。指定 KEEPFIXED PLAN 可确保仅当更改基础表的架构或在那些表上执行 sp_recompile 时才重新编译查询。

IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX

可防止查询使用非聚集的 xVelocity 内存优化的列存储索引。如果查询包含避免使用 columnstore 索引的查询提示以及有关使用 columnstore 索引的索引提示，则这些提示将发生冲突，查询将返回错误。

MAXDOP number

对于指定了 max degree of parallelism 配置选项的查询，会覆盖sp_configure 和资源调控器的该选项。MAXDOP 查询提示可以超出使用 sp_configure 配置的值。如果 MAXDOP 超出使用资源调控器配置的值，则数据库引擎会使用资源调控器 MAXDOP 值（如ALTER WORKLOAD GROUP (Transact-SQL) 中所述）。当使用 MAXDOP 查询提示时，所有和max degree of parallelism配置选项一起使用的语义规则均适用。有关详细信息，请参阅配置 max degree of parallelism 服务器配置选项。

注意
如果 MAXDOP 设置为零，服务器将选择最大并行度。

MAXRECURSION number

指定该查询允许的最大递归数。 number 是介于 0 至 32767 之间的非负整数。如果指定 0，则没有限制。如果未指定此选项，则对服务器的默认限制为 100。

当在查询执行期间达到指定或默认的 MAXRECURSION 数量限制时，将结束查询并返回错误。

由于此错误，该语句的所有结果都被回滚。如果该语句为 SELECT 语句，则可能会返回部分结果或不返回结果。所返回的任何部分结果都可能无法包括超过指定最大递归级别的递归级别上的所有行。

有关详细信息，请参阅 WITH common_table_expression (Transact-SQL)。

OPTIMIZE FOR ( @variable_name { UNKNOWN | = literal_constant } [ , ... n] )

在编译和优化查询时指示查询优化器对局部变量使用特定值。仅在查询优化期间使用该值，在查询执行期间不使用该值。

@variable_name: 在查询中使用的局部变量的名称，可以为其分配用于 OPTIMIZE FOR 查询提示的值。
UNKNOWN: 指定查询优化器在查询优化期间使用统计数据而不是初始值来确定局部变量的值。
literal_constant: 要分配给 @variable_name 并用于 OPTIMIZE FOR 查询提示的文字常量值。literal_constant 只在查询优化期间使用，在查询执行期间不用作@variable_name的值。literal_constant 可以是任意可用文字常量表示的 SQL Server 系统数据类型。literal_constant 的数据类型必须可隐式转换为查询中@variable_name所引用的数据类型。

OPTIMIZE FOR 可以抵消优化器的默认参数检测行为，也可在创建计划指南时使用。有关详细信息，请参阅重新编译存储过程。

OPTIMIZE FOR UNKNOWN

指示查询优化器在编译和优化查询时使用所有局部变量的统计数据而不是初始值，包括使用强制参数化创建的参数。

如果在同一查询提示中使用 OPTIMIZE FOR @variable_name = literal_constant 和 OPTIMIZE FOR UNKNOWN，则查询优化器对特定值使用指定的 literal_constant，而对其余变量值使用 UNKNOWN。这些值仅用于查询优化期间，而不会用于查询执行期间。

PARAMETERIZATION { SIMPLE | FORCED }

指定在编译查询时 SQL Server 查询优化器应用于此查询的参数化规则。

重要提示
PARAMETERIZATION 查询提示只能在计划指南中指定。不能直接在查询中指定该查询提示。

SIMPLE 用于指示查询优化器尝试进行简单参数化。 FORCED 用于指示优化器尝试进行强制参数化。 PARAMETERIZATION 查询提示用于覆盖计划指南中 PARAMETERIZATION 数据库 SET 选项的当前设置。有关详细信息，请参阅使用计划指南指定查询参数化行为。

RECOMPILE

指示 SQL Server 数据库引擎在执行为查询生成的计划后将其丢弃，从而在下次执行同一查询时强制查询优化器重新编译查询计划。如果未指定 RECOMPILE，数据库引擎将缓存查询计划并重新使用它们。在编译查询计划时，RECOMPILE 查询提示将使用查询中任意本地变量的当前值，如果查询位于存储过程中，这些当前值将传递给任意参数。

在只须重新编译存储过程中的一部分查询，而不是重新编译整个存储过程时，RECOMPILE 是创建使用 WITH RECOMPILE 子句的存储过程的很有用的替代方法。有关详细信息，请参阅重新编译存储过程。在创建计划指南时，RECOMPILE 也很有用。

ROBUST PLAN

强制查询优化器尝试一个计划，该计划可能以性能为代价获得最大可能的行大小。处理查询时，中间表和运算符可能需要存储和处理比输入行宽的行。在有些情况下，行可能很宽，以致某个运算符无法处理行。如果发生这种情况，数据库引擎将在查询执行过程中生成错误。通过使用 ROBUST PLAN，可以指示查询优化器不考虑可能会遇到该问题的所有查询计划。

如果不能使用这样的计划，查询优化器将返回错误而不是延迟对查询执行的错误检测。行可以包含可变长度列；数据库引擎允许将行大小定义为超过数据库引擎处理能力的最大可能的大小。通常，应用程序存储实际大小在数据库引擎处理能力范围内的行，而不管最大可能大小。如果数据库引擎遇到过长的行，则返回执行错误。

USE PLAN N ' xml_plan '

强制查询优化器对查询使用由 'xml_plan' 指定的现有查询计划。不能使用 INSERT、UPDATE、MERGE 或 DELETE 语句来指定 USE PLAN。

TABLE HINT ( exposed_object_name [ , <table_hint> [ [ , ]... n] ] )

将指定的表提示应用到与 exposed_object_name 对应的表或视图。我们建议仅在计划指南的上下文中将表提示用作查询提示。

exposed_object_name 可以为以下引用之一：

当对查询的 FROM 子句中的表或视图使用别名时，exposed_object_name 就是别名。
如果不使用别名，exposed_object_name 与 FROM 子句中引用的表或视图完全匹配。例如，如果使用由两部分组成的名称引用了表或视图，则exposed_object_name 就是这个由两部分组成的名称。

如果指定了 exposed_object_name 但未指定表提示，则将忽略在查询中指定为对象表提示的一部分的任何索引，并由查询优化器来决定索引的使用。当您无法修改原始查询时，可以使用此方法来消除 INDEX 表提示的影响。请参阅示例 J。

<table_hint> ::= { [ NOEXPAND ] { INDEX ( index_value [ ,... n] ) | INDEX = ( index_value ) | FORCESEEK [ ( index_value ( index_column_name [ ,...] )) ]| FORCESCAN | HOLDLOCK | NOLOCK | NOWAIT | PAGLOCK | READCOMMITTED | READCOMMITTEDLOCK | READPAST | READUNCOMMITTED | REPEATABLEREAD | ROWLOCK | SERIALIZABLE |SPATIAL_WINDOW_MAX_CELLS | TABLOCK | TABLOCKX | UPDLOCK | XLOCK }

要作为查询提示应用于与 exposed_object_name对应的表或视图的表提示。有关这些提示的说明，请参阅表提示 (Transact-SQL)。

不允许将非 INDEX、FORCESCAN 和 FORCESEEK 的表提示用作查询提示，除非该查询已经具有一个指定该表提示的 WITH 子句。有关详细信息，请参阅备注。

注意
指定带参数的 FORCESEEK 限制优化器可以考虑的计划数大于指定不带参数的 FORCESEEK 时的计划数。这可能导致在更多情况下出现“无法生成计划”错误。在未来的版本中，对优化器进行内部修改后可允许考虑更多计划。

注释

只有在 INSERT 语句中使用了 SELECT 子句时，才能在该语句中指定查询提示。

只能在*查询中指定查询提示，不能在子查询指定。将表提示指定为查询提示时，可以在*查询或子查询中指定此提示，但为 TABLE HINT 子句中的 exposed_object_name 指定的值必须与该查询或子查询中公开的名称完全匹配。

将表提示指定为查询提示

我们建议仅在计划指南的上下文中将 INDEX、FORCESCAN 或 FORCESEEK 表提示用作查询提示。当您无法修改原始查询时（例如，由于它是第三方应用程序），计划指南将很有用。计划指南中指定的查询提示在查询编译和优化前添加到查询中。对于即席查询，仅在测试计划指南语句时才应使用 TABLE HINT 子句。对于所有其他即席查询，建议仅将这些提示指定为表提示。

如果将 INDEX、FORCESCAN 和 FORCESEEK 表提示指定为查询提示，它们会对以下对象有效：

表
视图
索引视图
公用表表达式（必须在其结果集填充公用表表达式的 SELECT 语句中指定提示）
动态管理视图
命名子查询

可以为没有任何现有表提示的查询指定 INDEX、FORCESCAN 和 FORCESEEK 表提示作为查询提示，这些提示也可用于分别替换查询中的现有 INDEX、FORCESCAN 或 FORCESEEK 提示。不允许将非 INDEX、FORCESCAN 和 FORCESEEK 的表提示用作查询提示，除非该查询已经具有一个指定该表提示的 WITH 子句。这种情况下，还必须使用 OPTION 子句中的 TABLE HINT 来将匹配的提示指定为查询提示，以保留查询的语义。例如，如果查询包含表提示 NOLOCK，则计划指南的@hints 参数中的 OPTION 子句必须也包含 NOLOCK 提示。请参见示例 K。当通过使用 OPTION 子句中的 TABLE HINT 指定了非 INDEX、FORCESCAN 或 FORCESEEK 的表提示，而未指定匹配的查询提示时，或指定了后者而未指定前者，则会引发错误 8702，表示 OPTION 子句会导致查询的语义发生变化，该查询将失败。

 
 declare @a table (v varchar(100),vid int) 
 
insertinto @a 
select'abc'as v,1 as vid 
unionall 
select'cde',123 
unionall 
select'cd',2  
unionall 
select'abc',12 
unionall 
select'def',123 
unionall 
select'cde',12 
unionall 
select'def',2 
unionall 
select'cde',1 
unionall 
select'cdef',1 
 
 
--1.group 
--1.1hash 
select v, 
       COUNT(vid) 
from @a 
groupby v 
option(hash group)      
 
--1.2order 
select v, 
       COUNT(vid) 
from @a 
groupby v 
option(ordergroup)  
 
--1.3可以用在distinct中 
selectdistinct *   
from @a 
option(ordergroup)  
 
 
 
--2.union 
--2.1hash 
select'abc'as v,1 as vid 
union  
select'cd',123 
union 
select'cd',2  
union 
select'abc',1 
option(hash union)    
 
--2.2merge 
select'abc'as v,1 as vid 
union  
select'cd',123 
union 
select'cd',2  
union 
select'abc',1 
option(merge union)   
 
--2.3concat 
select'abc'as v,1 as vid 
union  
select'cd',123 
union 
select'cd',2  
union 
select'abc',1 
option(concat union)  
  
  
--3.join 
--3.1hash 
select * 
from 
(  
    select'abc'as v,1 as vid 
    unionall 
    select'cd',2 
)a 
innerjoin 
( 
    select'cd'as v,2 as vid 
    unionall 
    select'abc',1 
)b 
on a.vid = b.vid  
option(hash join)    
 
 
--3.2merge 
select * 
from 
(  
    select'abc'as v,1 as vid 
    unionall 
    select'cd',2 
)a 
innerjoin 
( 
    select'cd'as v,2 as vid 
    unionall 
    select'abc',1 
)b 
on a.vid = b.vid 
option(merge join)    
 
 
--3.3loop 
select * 
from 
(  
    select'abc'as v,1 as vid 
    unionall 
    select'cd',2 
)a 
innerjoin 
( 
    select'cd'as v,2 as vid 
    unionall 
    select'abc',1 
)b 
on a.vid = b.vid 
option(loop join)  
 
 
 
--4.expand views 
--建表 
select * into wc_objects 
from sys.objects  
 
--添加主键约束 
altertable wc_objects 
addconstraint pk_wc_objectid primarykey(object_id) 
 
--创建视图 
createview select_wc_objects 
with schemabinding 
as 
 
selectname, 
       object_id, 
       type, 
       type_desc 
from dbo.wc_objects 
go 
 
 
--创建唯一的聚集索引作为视图索引(也就是视图对应的索引) 
createunique clustered index uni_select_wc_objects 
on select_wc_objects(object_id) 
 
--这里会展开视图,直接引用底层的原始表wc_objects 
select * 
from select_wc_objects 
 
--不会展开,直接使用唯一聚集索引uni_select_wc_objects中的数据 
select * 
from select_wc_objects with(noexpand) 
 
 
 
--5.fast n:对查询进行优化，以便快速检索前n行 
select o.OrderDate, 
       o.SalesOrderNumber 
from sales.SalesOrderHeader o 
innerjoin sales.SalesOrderDetail d 
        on o.SalesOrderID =d.SalesOrderID  
option(fast 100) --在返回前n行后，查询继续执行并生成完整的结果集 
 
 
 
--6.force order:一般不建议使用,应该由SQL Server来决定联接顺序 
SELECT * 
FROM sales.SalesOrderHeader o  
INNERJOIN sales.SalesOrderDetail m 
        ON O.salesorderid = m.salesorderid 
innerjoin production.Product e 
        on m.ProductID = e.ProductID 
option(forceorder)  --用了这个导致查询更慢 
 
 
--7.keep plan：强制查询优化器对查询放宽估计的重新编译阈值 
SELECT * 
FROM sales.SalesOrderHeader o  
INNERJOIN sales.SalesOrderDetail m 
        ON O.salesorderid = m.salesorderid 
innerjoin production.Product e 
        on m.ProductID = e.ProductID 
option(keep plan) 
 
 
--8.keepfixed plan：强制查询优化器不因统计信息的更改而重新编译查询 
--可确保只有更改基础表的架构或在那些表上执行sp_recompile时才重新编译查询 
SELECT * 
FROM sales.SalesOrderHeader o  
INNERJOIN sales.SalesOrderDetail m 
        ON O.salesorderid = m.salesorderid 
innerjoin production.Product e 
        on m.ProductID = e.ProductID 
option(keepfixed plan) 
 
 
 
/*================================= 
9.maxrecursion自动产生大量连续的数字 
==================================*/ 
WITH t   
AS 
( 
SELECT 1 AS r 
UNIONALL 
SELECT r+1  
FROM t 
WHERE r < 10000 
) 
 
SELECT *  
FROM t 
OPTION(maxrecursion 10000)  --限制最大递归数字 
 
 
 
--10.MAXDOP 
--对于指定了max degree of parallelism配置选项的查询， 
--会覆盖sp_configure 和资源调控器的该选项 
SELECT * 
FROM sales.SalesOrderHeader o  
INNERJOIN sales.SalesOrderDetail m 
        ON O.salesorderid = m.salesorderid 
innerjoin production.Product e 
        on m.ProductID = e.ProductID 
option(maxdop 2) 
 
 
 
--11.OPTIMIZE FOR 
declare @name nvarchar(50)  
declare @id int 
 
select @name ='a', 
       @id   =500 
 
SELECT * 
FROM sales.SalesOrderHeader o  
INNERJOIN sales.SalesOrderDetail m 
        ON O.salesorderid = m.salesorderid 
innerjoin production.Product e 
        on m.ProductID = e.ProductID 
where e.Namelike @name +'%'  
      and e.ProductID >= @id  
option(optimize for(@name='a',@id unknown)) 
 
 
 
--12.OPTIMIZE FOR UNKNOWN 
declare @name1 nvarchar(50)  
declare @id1 int 
 
select @name1 ='a', 
       @id1   =500; 
 
SELECT * 
FROM sales.SalesOrderHeader o  
INNERJOIN sales.SalesOrderDetail m 
        ON O.salesorderid = m.salesorderid 
innerjoin production.Product e 
        on m.ProductID = e.ProductID 
where e.Namelike @name1 +'%'  
      and e.ProductID >= @id1  
option(optimize for unknown)  
 
 
 
--12.use plan 
createtable txt(id numeric(10,0) primarykey, 
                 v varchar(20), 
                 vv int ) 
 
createindex txt_v on txt(v) 
 
insertinto txt 
select object_id, 
       type_desc, 
       schema_id 
from sys.objects 
where LEN(type_desc) < 20 
 
 
--取得xml格式的执行计划 
setstatistics xml on 
 
select *  
from txt  
where id > 1000 and 
      vv > 2 
 
setstatistics xml off 
 
select * 
from txt 
option( 
use plan 
'<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"  
Version="1.1" Build="10.50.1600.1"> 
<BatchSequence><Batch><Statements> 
<StmtSimple StatementText="SELECT * FROM [txt] WHERE [id]>@1 AND [vv]>@2"  
StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="0.00429444"  
StatementEstRows="42.2945" StatementOptmLevel="TRIVIAL" QueryHash="0xA4E0AA4B0A87F88B"  
QueryPlanHash="0x3325250D8A42F500"> 
<StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true"  
ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"/> 
<QueryPlan DegreeOfParallelism="1" CachedPlanSize="16" CompileTime="1" CompileCPU="1"  
CompileMemory="136"><RelOp NodeId="0"  
PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek"  
EstimateRows="42.2945" EstimateIO="0.00386574" EstimateCPU="0.0004287"  
AvgRowSize="34" EstimatedTotalSubtreeCost="0.00429444"  
TableCardinality="292" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"> 
<OutputList><ColumnReference Database="[test2]"  
Schema="[dbo]"Table="[txt]"Column="id"/><ColumnReference  
Database="[test2]"Schema="[dbo]"Table="[txt]"Column="v"/> 
<ColumnReference Database="[test2]"Schema="[dbo]"Table="[txt]"Column="vv"/> 
</OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0"  
ActualRows="5" ActualEndOfScans="1" ActualExecutions="1"/> 
</RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD"  
ForcedIndex="0" ForceSeek="0" NoExpandHint="0"><DefinedValues> 
<DefinedValue><ColumnReference Database="[test2]"  
Schema="[dbo]"Table="[txt]"Column="id"/> 
</DefinedValue><DefinedValue><ColumnReference  
Database="[test2]"Schema="[dbo]"Table="[txt]"  
Column="v"/></DefinedValue><DefinedValue> 
<ColumnReference Database="[test2]"Schema="[dbo]"  
Table="[txt]"Column="vv"/></DefinedValue></DefinedValues> 
<Object Database="[test2]"Schema="[dbo]"Table="[txt]"  
Index="[PK__txt__3213E83F4D1564AE]" IndexKind="Clustered"/> 
<SeekPredicates><SeekPredicateNew><SeekKeys> 
<StartRange ScanType="GT"><RangeColumns> 
<ColumnReference Database="[test2]"Schema="[dbo]"Table="[txt]"  
Column="id"/></RangeColumns><RangeExpressions><ScalarOperator  
ScalarString="CONVERT_IMPLICIT(numeric(10,0),[@1],0)"><Identifier> 
<ColumnReference Column="ConstExpr1003"><ScalarOperator> 
<Convert DataType="numeric"Precision="10" Scale="0" Style="0" Implicit="1"> 
<ScalarOperator><Identifier><ColumnReference Column="@1"/> 
</Identifier></ScalarOperator></Convert></ScalarOperator> 
</ColumnReference></Identifier></ScalarOperator> 
</RangeExpressions></StartRange></SeekKeys></SeekPredicateNew> 
</SeekPredicates><Predicate><ScalarOperator  
ScalarString="[test2].[dbo].[txt].[vv]>CONVERT_IMPLICIT(int,[@2],0)"><Compare CompareOp="GT"> 
<ScalarOperator><Identifier><ColumnReference Database="[test2]"Schema="[dbo]"  
Table="[txt]"Column="vv"/></Identifier></ScalarOperator><ScalarOperator> 
<Identifier><ColumnReference Column="ConstExpr1004"><ScalarOperator> 
<Convert DataType="int" Style="0" Implicit="1"><ScalarOperator> 
<Identifier><ColumnReference Column="@2"/></Identifier> 
</ScalarOperator></Convert></ScalarOperator></ColumnReference> 
</Identifier></ScalarOperator></Compare></ScalarOperator> 
</Predicate></IndexScan></RelOp><ParameterList> 
<ColumnReference Column="@2" ParameterCompiledValue="(2)" ParameterRuntimeValue="(2)"/> 
<ColumnReference Column="@1" ParameterCompiledValue="(1000)"  
ParameterRuntimeValue="(1000)"/></ParameterList></QueryPlan> 
</StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>') 
 
 
 
--14.PARAMETERIZATION { SIMPLE | FORCED }  
--PARAMETERIZATION查询提示只能在计划指南中指定,不能直接在查询中指定该查询提示 
--14.1运行多条类似的查询 
select * from txt where id = 8 
 
select * from txt where id = 9 
 
 
--14.2通过查询缓存的计划所对应的sql文本，发现很多都是相同的 
--而且大部分的objtype都是proc,adhoc,prepared. 
SELECT *  
FROM SYS.dm_exec_cached_plans E 
CROSS APPLY SYS.dm_exec_sql_text(E.plan_handle)EE 
WHERE EE.text LIKE'%select * from txt where id =%' 
 
 
declare @sqltext nvarchar(max) 
declare @parameter nvarchar(max) 
 
 
--14.3获取查询的参数化形式以及查询的参数,放入变量中 
exec sp_get_query_template  
    @querytext = N'select * from txt where id = 8', 
    @templatetext= @sqltext output, 
    @parameters = @parameter output 
 
 
--14.4使用模板来创建计划指南    
exec sp_create_plan_guide 
    @name = 'plan_guide_txt_template', 
    @stmt = @sqltext, 
    @type = 'template',   
    @module_or_batch = null, 
    @params = @parameter, 
    @hints = 'option(parameterization forced)'  
     
 
--14.5再次查询发现多条执行计划已经变为一条,usecounts计数增加 
SELECT *  
FROM SYS.dm_exec_cached_plans E 
CROSS APPLY SYS.dm_exec_sql_text(E.plan_handle)EE 
WHERE EE.text LIKE'%select * from txt where id =%' 
 
 
 
--15.ROBUST PLAN  
--查询优化器尝试一个计划，该计划可能以性能为代价获得最大可能的行大小 
SELECT * 
FROM sales.SalesOrderHeader o  
INNERJOIN sales.SalesOrderDetail m 
        ON O.salesorderid = m.salesorderid 
innerjoin production.Product e 
        on m.ProductID = e.ProductID 
option(ROBUST PLAN) 
 
 
--16.RECOMPILE  
SELECT * 
FROM sales.SalesOrderHeader o  
INNERJOIN sales.SalesOrderDetail m 
        ON O.salesorderid = m.salesorderid 
innerjoin production.Product e 
        on m.ProductID = e.ProductID 
option(recompile) 
 
--如果是存储过程，可以通过下面的方式来显式的重新编译 
exec 存储过程名称 with recompile 

秒客网

SQL Server的查询优化器详解

将表提示指定为查询提示

相关文章