SQL Server的查询优化器详解

小鱼儿 2022-08-10 09:26 353阅读 0赞

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 操作。

1.  \--没有提示的查询 
2.  **select** \* 
3.  **from** Production.Product p  
4.  **inner**join production.productreview r 
5.         **on** r.productid = p.productid 
6.   
7.   
8.  \--Loop适合：当一个表很小另一个表很大、在关联的列上有索引时 
9.  **select** \* 
10. **from** Production.Product p  
11. **inner** loop join production.productreview r  
12.               **on** r.productid = p.productid 
13.  
14.  
15. \--merge适合：按照关联列排序的中等或者大的表        
16. **select** \* 
17. **from** Production.Product p 
18. **inner** merge join production.productreview r 
19.               **on** r.productid = p.productid 
20.  
21. \--hash适合：没有排序的大的表 
22. **select** \* 
23. **from** Production.Product p 
24. **inner** hash join production.productreview r 
25.              **on** r.productid = p.productid     
26.

--没有提示的查询
    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)][_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 不会影响查询优化器可能的角色逆转行为。

<table style="border:1px solid silver; border-collapse:collapse; word-break:break-word"> 
 <tbody> 
  <tr> 
   <th align="left" style="border:1px solid silver; border-collapse:collapse; padding:3px"> <img title="注意" src="http://i.msdn.microsoft.com/areas/global/content/clear.gif" alt="注意" style="border:0px"><strong>注意</strong></th> 
  </tr> 
  <tr> 
   <td style="font-size:13px; font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; margin:0px; word-wrap:break-word; border:1px solid silver; border-collapse:collapse; padding:3px"> <p style="margin:10px auto"><span>在 MERGE 语句中，如果未指定 WHEN SOURCE NOT MATCHED 子句，则按照默认的联接次序，先访问源表再访问目标表。</span><span>如果指定 FORCE ORDER，则保留此默认行为。</span></p> </td> 
  </tr> 
 </tbody> 
</table>

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)][ALTER WORKLOAD GROUP _Transact-SQL] 中所述）。当使用 MAXDOP 查询提示时，所有和**max degree of parallelism**配置选项一起使用的语义规则均适用。有关详细信息，请参阅[配置 max degree of parallelism 服务器配置选项][max degree of parallelism]。

<table style="border:1px solid silver; border-collapse:collapse; word-break:break-word"> 
 <tbody> 
  <tr> 
   <th align="left" style="border:1px solid silver; border-collapse:collapse; padding:3px"> <img title="注意事项" src="http://i.msdn.microsoft.com/areas/global/content/clear.gif" alt="注意事项" style="border:0px"><strong>注意</strong></th> 
  </tr> 
  <tr> 
   <td style="font-size:13px; font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; margin:0px; word-wrap:break-word; border:1px solid silver; border-collapse:collapse; padding:3px"> <p style="margin:10px auto"><span>如果 MAXDOP 设置为零，服务器将选择最大并行度。</span></p> </td> 
  </tr> 
 </tbody> 
</table>

MAXRECURSION  number

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

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

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

有关详细信息，请参阅 [WITH common\_table\_expression (Transact-SQL)][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 可以抵消优化器的默认参数检测行为，也可在创建计划指南时使用。有关详细信息，请参阅[重新编译存储过程][Link 1]。

OPTIMIZE FOR UNKNOWN

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

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

PARAMETERIZATION \{ SIMPLE | FORCED \}

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

<table style="border:1px solid silver; border-collapse:collapse; word-break:break-word"> 
 <tbody> 
  <tr> 
   <th align="left" style="border:1px solid silver; border-collapse:collapse; padding:3px"> <img title="重要说明" src="http://i.msdn.microsoft.com/areas/global/content/clear.gif" alt="重要说明" style="border:0px"><strong>重要提示</strong></th> 
  </tr> 
  <tr> 
   <td style="font-size:13px; font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; margin:0px; word-wrap:break-word; border:1px solid silver; border-collapse:collapse; padding:3px"> <p style="margin:10px auto"><span>PARAMETERIZATION 查询提示只能在计划指南中指定。</span>&nbsp;<span>不能直接在查询中指定该查询提示。</span></p> </td> 
  </tr> 
 </tbody> 
</table>

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

RECOMPILE

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

在只须重新编译存储过程中的一部分查询，而不是重新编译整个存储过程时，RECOMPILE 是创建使用 WITH RECOMPILE 子句的存储过程的很有用的替代方法。有关详细信息，请参阅[重新编译存储过程][Link 1]。在创建计划指南时，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 对应的表或视图。我们建议仅在[计划指南][Link 3]的上下文中将表提示用作查询提示。

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)][_Transact-SQL 1]。

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

<table style="border:1px solid silver; border-collapse:collapse; word-break:break-word"> 
 <tbody> 
  <tr> 
   <th align="left" style="border:1px solid silver; border-collapse:collapse; padding:3px"> <img title="注意事项" src="http://i.msdn.microsoft.com/areas/global/content/clear.gif" alt="注意事项" style="border:0px"><strong>注意</strong></th> 
  </tr> 
  <tr> 
   <td style="font-size:13px; font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; margin:0px; word-wrap:break-word; border:1px solid silver; border-collapse:collapse; padding:3px"> <p style="margin:10px auto"><span>指定带参数的 FORCESEEK 限制优化器可以考虑的计划数大于指定不带参数的 FORCESEEK 时的计划数。</span><span>这可能导致在更多情况下出现“无法生成计划”错误。</span><span>在未来的版本中，对优化器进行内部修改后可允许考虑更多计划。</span></p> </td> 
  </tr> 
 </tbody> 
</table>

[注释][Link 4]

--------------------

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

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

### 将表提示指定为查询提示 ###

我们建议仅在[计划指南][Link 3]的上下文中将 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 子句会导致查询的语义发生变化，该查询将失败。

1.  **declare** @a **table** (v **varchar**(100),vid **int**) 
2.   
3.  **insert****into** @a 
4.  **select**'abc'**as** v,1 **as** vid 
5.  **union**all 
6.  **select**'cde',123 
7.  **union**all 
8.  **select**'cd',2  
9.  **union**all 
10. **select**'abc',12 
11. **union**all 
12. **select**'def',123 
13. **union**all 
14. **select**'cde',12 
15. **union**all 
16. **select**'def',2 
17. **union**all 
18. **select**'cde',1 
19. **union**all 
20. **select**'cdef',1 
21.  
22.  
23. \--1.group 
24. \--1.1hash 
25. **select** v, 
26.        COUNT(vid) 
27. **from** @a 
28. **group****by** v 
29. **option**(hash **group**)      
30.  
31. \--1.2order 
32. **select** v, 
33.        COUNT(vid) 
34. **from** @a 
35. **group****by** v 
36. **option**(**order****group**)  
37.  
38. \--1.3可以用在distinct中 
39. **select****distinct** \*   
40. **from** @a 
41. **option**(**order****group**)  
42.  
43.  
44.  
45. \--2.union 
46. \--2.1hash 
47. **select**'abc'**as** v,1 **as** vid 
48. **union**  
49. **select**'cd',123 
50. **union** 
51. **select**'cd',2  
52. **union** 
53. **select**'abc',1 
54. **option**(hash **union**)    
55.  
56. \--2.2merge 
57. **select**'abc'**as** v,1 **as** vid 
58. **union**  
59. **select**'cd',123 
60. **union** 
61. **select**'cd',2  
62. **union** 
63. **select**'abc',1 
64. **option**(merge **union**)   
65.  
66. \--2.3concat 
67. **select**'abc'**as** v,1 **as** vid 
68. **union**  
69. **select**'cd',123 
70. **union** 
71. **select**'cd',2  
72. **union** 
73. **select**'abc',1 
74. **option**(concat **union**)  
75.   
76.   
77. \--3.join 
78. \--3.1hash 
79. **select** \* 
80. **from** 
81. (  
82.     **select**'abc'**as** v,1 **as** vid 
83.     **union**all 
84.     **select**'cd',2 
85. )a 
86. **inner**join 
87. ( 
88.     **select**'cd'**as** v,2 **as** vid 
89.     **union**all 
90.     **select**'abc',1 
91. )b 
92. **on** a.vid = b.vid  
93. **option**(hash join)    
94.  
95.  
96. \--3.2merge 
97. **select** \* 
98. **from** 
99. (  
100.     **select**'abc'**as** v,1 **as** vid 
101.     **union**all 
102.     **select**'cd',2 
103. )a 
104. **inner**join 
105. ( 
106.     **select**'cd'**as** v,2 **as** vid 
107.     **union**all 
108.     **select**'abc',1 
109. )b 
110. **on** a.vid = b.vid 
111. **option**(merge join)    
112.  
113.  
114. \--3.3loop 
115. **select** \* 
116. **from** 
117. (  
118.     **select**'abc'**as** v,1 **as** vid 
119.     **union**all 
120.     **select**'cd',2 
121. )a 
122. **inner**join 
123. ( 
124.     **select**'cd'**as** v,2 **as** vid 
125.     **union**all 
126.     **select**'abc',1 
127. )b 
128. **on** a.vid = b.vid 
129. **option**(loop join)  
130.  
131.  
132.  
133. \--4.expand views 
134. \--建表 
135. **select** \* **into** wc\_objects 
136. **from** sys.objects  
137.  
138. \--添加主键约束 
139. **alter****table** wc\_objects 
140. **add****constraint** pk\_wc\_objectid **primary****key**(object\_id) 
141.  
142. \--创建视图 
143. **create****view** select\_wc\_objects 
144. **with** schemabinding 
145. **as** 
146.  
147. **select****name**, 
148.        object\_id, 
149.        type, 
150.        type\_desc 
151. **from** dbo.wc\_objects 
152. go 
153.  
154.  
155. \--创建唯一的聚集索引作为视图索引(也就是视图对应的索引) 
156. **create****unique** clustered **index** uni\_select\_wc\_objects 
157. **on** select\_wc\_objects(object\_id) 
158.  
159. \--这里会展开视图,直接引用底层的原始表wc\_objects 
160. **select** \* 
161. **from** select\_wc\_objects 
162.  
163. \--不会展开,直接使用唯一聚集索引uni\_select\_wc\_objects中的数据 
164. **select** \* 
165. **from** select\_wc\_objects **with**(noexpand) 
166.  
167.  
168.  
169. \--5.fast n:对查询进行优化，以便快速检索前n行 
170. **select** o.OrderDate, 
171.        o.SalesOrderNumber 
172. **from** sales.SalesOrderHeader o 
173. **inner**join sales.SalesOrderDetail d 
174.         **on** o.SalesOrderID =d.SalesOrderID  
175. **option**(fast 100) \--在返回前n行后，查询继续执行并生成完整的结果集 
176.  
177.  
178.  
179. \--6.force order:一般不建议使用,应该由SQL Server来决定联接顺序 
180. **SELECT** \* 
181. **FROM** sales.SalesOrderHeader o  
182. **INNER**JOIN sales.SalesOrderDetail m 
183.         **ON** O.salesorderid = m.salesorderid 
184. **inner**join production.Product e 
185.         **on** m.ProductID = e.ProductID 
186. **option**(**force****order**)  \--用了这个导致查询更慢 
187.  
188.  
189. \--7.keep plan：强制查询优化器对查询放宽估计的重新编译阈值 
190. **SELECT** \* 
191. **FROM** sales.SalesOrderHeader o  
192. **INNER**JOIN sales.SalesOrderDetail m 
193.         **ON** O.salesorderid = m.salesorderid 
194. **inner**join production.Product e 
195.         **on** m.ProductID = e.ProductID 
196. **option**(keep plan) 
197.  
198.  
199. \--8.keepfixed plan：强制查询优化器不因统计信息的更改而重新编译查询 
200. \--可确保只有更改基础表的架构或在那些表上执行sp\_recompile时才重新编译查询 
201. **SELECT** \* 
202. **FROM** sales.SalesOrderHeader o  
203. **INNER**JOIN sales.SalesOrderDetail m 
204.         **ON** O.salesorderid = m.salesorderid 
205. **inner**join production.Product e 
206.         **on** m.ProductID = e.ProductID 
207. **option**(keepfixed plan) 
208.  
209.  
210.  
211. /\*================================= 
212. 9.maxrecursion自动产生大量连续的数字 
213. ==================================\*/ 
214. **WITH** t   
215. **AS** 
216. ( 
217. **SELECT** 1 **AS** r 
218. **UNION**ALL 
219. **SELECT** r+1  
220. **FROM** t 
221. **WHERE** r < 10000 
222. ) 
223.  
224. **SELECT** \*  
225. **FROM** t 
226. **OPTION**(maxrecursion 10000)  \--限制最大递归数字 
227.  
228.  
229.  
230. \--10.MAXDOP 
231. \--对于指定了max degree of parallelism配置选项的查询， 
232. \--会覆盖sp\_configure 和资源调控器的该选项 
233. **SELECT** \* 
234. **FROM** sales.SalesOrderHeader o  
235. **INNER**JOIN sales.SalesOrderDetail m 
236.         **ON** O.salesorderid = m.salesorderid 
237. **inner**join production.Product e 
238.         **on** m.ProductID = e.ProductID 
239. **option**(maxdop 2) 
240.  
241.  
242.  
243. \--11.OPTIMIZE FOR 
244. **declare** @**name** nvarchar(50)  
245. **declare** @id **int** 
246.  
247. **select** @**name** ='a', 
248.        @id   =500 
249.  
250. **SELECT** \* 
251. **FROM** sales.SalesOrderHeader o  
252. **INNER**JOIN sales.SalesOrderDetail m 
253.         **ON** O.salesorderid = m.salesorderid 
254. **inner**join production.Product e 
255.         **on** m.ProductID = e.ProductID 
256. **where** e.**Name**like @**name** +'%'  
257.       and e.ProductID >= @id  
258. **option**(optimize **for**(@**name**='a',@id unknown)) 
259.  
260.  
261.  
262. \--12.OPTIMIZE FOR UNKNOWN 
263. **declare** @name1 nvarchar(50)  
264. **declare** @id1 **int** 
265.  
266. **select** @name1 ='a', 
267.        @id1   =500; 
268.  
269. **SELECT** \* 
270. **FROM** sales.SalesOrderHeader o  
271. **INNER**JOIN sales.SalesOrderDetail m 
272.         **ON** O.salesorderid = m.salesorderid 
273. **inner**join production.Product e 
274.         **on** m.ProductID = e.ProductID 
275. **where** e.**Name**like @name1 +'%'  
276.       and e.ProductID >= @id1  
277. **option**(optimize **for** unknown)  
278.  
279.  
280.  
281. \--12.use plan 
282. **create****table** txt(id **numeric**(10,0) **primary****key**, 
283.                  v **varchar**(20), 
284.                  vv **int** ) 
285.  
286. **create****index** txt\_v **on** txt(v) 
287.  
288. **insert****into** txt 
289. **select** object\_id, 
290.        type\_desc, 
291.        schema\_id 
292. **from** sys.objects 
293. **where** LEN(type\_desc) < 20 
294.  
295.  
296. \--取得xml格式的执行计划 
297. **set****statistics** xml **on** 
298.  
299. **select** \*  
300. **from** txt  
301. **where** id > 1000 and 
302.       vv > 2 
303.  
304. **set****statistics** xml **off** 
305.  
306. **select** \* 
307. **from** txt 
308. **option**( 
309. use plan 
310. '<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"  
311. Version="1.1" Build="10.50.1600.1"> 
312. <BatchSequence><Batch><Statements> 
313. <StmtSimple StatementText="SELECT \* FROM \[txt\] WHERE \[id\]>@1 AND \[vv\]>@2"  
314. StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="0.00429444"  
315. StatementEstRows="42.2945" StatementOptmLevel="TRIVIAL" QueryHash="0xA4E0AA4B0A87F88B"  
316. QueryPlanHash="0x3325250D8A42F500"> 
317. <StatementSetOptions QUOTED\_IDENTIFIER="true" ARITHABORT="true" CONCAT\_NULL\_YIELDS\_NULL="true"  
318. ANSI\_NULLS="true" ANSI\_PADDING="true" ANSI\_WARNINGS="true" NUMERIC\_ROUNDABORT="false"/> 
319. <QueryPlan DegreeOfParallelism="1" CachedPlanSize="16" CompileTime="1" CompileCPU="1"  
320. CompileMemory="136"><RelOp NodeId="0"  
321. PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek"  
322. EstimateRows="42.2945" EstimateIO="0.00386574" EstimateCPU="0.0004287"  
323. AvgRowSize="34" EstimatedTotalSubtreeCost="0.00429444"  
324. TableCardinality="292" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"> 
325. <OutputList><ColumnReference **Database**="\[test2\]"  
326. **Schema**="\[dbo\]"**Table**="\[txt\]"**Column**="id"/><ColumnReference  
327. **Database**="\[test2\]"**Schema**="\[dbo\]"**Table**="\[txt\]"**Column**="v"/> 
328. <ColumnReference **Database**="\[test2\]"**Schema**="\[dbo\]"**Table**="\[txt\]"**Column**="vv"/> 
329. </OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0"  
330. ActualRows="5" ActualEndOfScans="1" ActualExecutions="1"/> 
331. </RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD"  
332. ForcedIndex="0" ForceSeek="0" NoExpandHint="0"><DefinedValues> 
333. <DefinedValue><ColumnReference **Database**="\[test2\]"  
334. **Schema**="\[dbo\]"**Table**="\[txt\]"**Column**="id"/> 
335. </DefinedValue><DefinedValue><ColumnReference  
336. **Database**="\[test2\]"**Schema**="\[dbo\]"**Table**="\[txt\]"  
337. **Column**="v"/></DefinedValue><DefinedValue> 
338. <ColumnReference **Database**="\[test2\]"**Schema**="\[dbo\]"  
339. **Table**="\[txt\]"**Column**="vv"/></DefinedValue></DefinedValues> 
340. <Object **Database**="\[test2\]"**Schema**="\[dbo\]"**Table**="\[txt\]"  
341. **Index**="\[PK\_\_txt\_\_3213E83F4D1564AE\]" IndexKind="Clustered"/> 
342. <SeekPredicates><SeekPredicateNew><SeekKeys> 
343. <StartRange ScanType="GT"><RangeColumns> 
344. <ColumnReference **Database**="\[test2\]"**Schema**="\[dbo\]"**Table**="\[txt\]"  
345. **Column**="id"/></RangeColumns><RangeExpressions><ScalarOperator  
346. ScalarString="CONVERT\_IMPLICIT(numeric(10,0),\[@1\],0)"><Identifier> 
347. <ColumnReference **Column**="ConstExpr1003"><ScalarOperator> 
348. <Convert DataType="numeric"**Precision**="10" Scale="0" Style="0" Implicit="1"> 
349. <ScalarOperator><Identifier><ColumnReference **Column**="@1"/> 
350. </Identifier></ScalarOperator></Convert></ScalarOperator> 
351. </ColumnReference></Identifier></ScalarOperator> 
352. </RangeExpressions></StartRange></SeekKeys></SeekPredicateNew> 
353. </SeekPredicates><Predicate><ScalarOperator  
354. ScalarString="\[test2\].\[dbo\].\[txt\].\[vv\]>CONVERT\_IMPLICIT(int,\[@2\],0)"><Compare CompareOp="GT"> 
355. <ScalarOperator><Identifier><ColumnReference **Database**="\[test2\]"**Schema**="\[dbo\]"  
356. **Table**="\[txt\]"**Column**="vv"/></Identifier></ScalarOperator><ScalarOperator> 
357. <Identifier><ColumnReference **Column**="ConstExpr1004"><ScalarOperator> 
358. <Convert DataType="int" Style="0" Implicit="1"><ScalarOperator> 
359. <Identifier><ColumnReference **Column**="@2"/></Identifier> 
360. </ScalarOperator></Convert></ScalarOperator></ColumnReference> 
361. </Identifier></ScalarOperator></Compare></ScalarOperator> 
362. </Predicate></IndexScan></RelOp><ParameterList> 
363. <ColumnReference **Column**="@2" ParameterCompiledValue="(2)" ParameterRuntimeValue="(2)"/> 
364. <ColumnReference **Column**="@1" ParameterCompiledValue="(1000)"  
365. ParameterRuntimeValue="(1000)"/></ParameterList></QueryPlan> 
366. </StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>') 
367.  
368.  
369.  
370. \--14.PARAMETERIZATION \{ SIMPLE | FORCED \}  
371. \--PARAMETERIZATION查询提示只能在计划指南中指定,不能直接在查询中指定该查询提示 
372. \--14.1运行多条类似的查询 
373. **select** \* **from** txt **where** id = 8 
374.  
375. **select** \* **from** txt **where** id = 9 
376.  
377.  
378. \--14.2通过查询缓存的计划所对应的sql文本，发现很多都是相同的 
379. \--而且大部分的objtype都是proc,adhoc,prepared. 
380. **SELECT** \*  
381. **FROM** SYS.dm\_exec\_cached\_plans E 
382. CROSS APPLY SYS.dm\_exec\_sql\_text(E.plan\_handle)EE 
383. **WHERE** EE.text LIKE'%select \* from txt where id =%' 
384.  
385.  
386. **declare** @sqltext nvarchar(**max**) 
387. **declare** @parameter nvarchar(**max**) 
388.  
389.  
390. \--14.3获取查询的参数化形式以及查询的参数,放入变量中 
391. **exec** sp\_get\_query\_template  
392.     @querytext = N'select \* from txt where id = 8', 
393.     @templatetext= @sqltext **output**, 
394.     @parameters = @parameter **output** 
395.  
396.  
397. \--14.4使用模板来创建计划指南    
398. **exec** sp\_create\_plan\_guide 
399.     @**name** = 'plan\_guide\_txt\_template', 
400.     @stmt = @sqltext, 
401.     @type = 'template',   
402.     @module\_or\_batch = null, 
403.     @params = @parameter, 
404.     @hints = 'option(parameterization forced)'  
405.      
406.  
407. \--14.5再次查询发现多条执行计划已经变为一条,usecounts计数增加 
408. **SELECT** \*  
409. **FROM** SYS.dm\_exec\_cached\_plans E 
410. CROSS APPLY SYS.dm\_exec\_sql\_text(E.plan\_handle)EE 
411. **WHERE** EE.text LIKE'%select \* from txt where id =%' 
412.  
413.  
414.  
415. \--15.ROBUST PLAN  
416. \--查询优化器尝试一个计划，该计划可能以性能为代价获得最大可能的行大小 
417. **SELECT** \* 
418. **FROM** sales.SalesOrderHeader o  
419. **INNER**JOIN sales.SalesOrderDetail m 
420.         **ON** O.salesorderid = m.salesorderid 
421. **inner**join production.Product e 
422.         **on** m.ProductID = e.ProductID 
423. **option**(ROBUST PLAN) 
424.  
425.  
426. \--16.RECOMPILE  
427. **SELECT** \* 
428. **FROM** sales.SalesOrderHeader o  
429. **INNER**JOIN sales.SalesOrderDetail m 
430.         **ON** O.salesorderid = m.salesorderid 
431. **inner**join production.Product e 
432.         **on** m.ProductID = e.ProductID 
433. **option**(recompile) 
434.  
435. \--如果是存储过程，可以通过下面的方式来显式的重新编译 
436. **exec** 存储过程名称 **with** recompile

[_Transact-SQL]: http://msdn.microsoft.com/zh-cn/library/ms173815
[FROM]: http://msdn.microsoft.com/zh-cn/library/ms177634
[ALTER WORKLOAD GROUP _Transact-SQL]: http://msdn.microsoft.com/zh-cn/library/bb895361
[max degree of parallelism]: http://msdn.microsoft.com/zh-cn/library/ms189094
[WITH common_table_expression _Transact-SQL]: http://msdn.microsoft.com/zh-cn/library/ms175972
[Link 1]: http://msdn.microsoft.com/zh-cn/library/ms190439
[Link 2]: http://msdn.microsoft.com/zh-cn/library/ms191275
[Link 3]: http://msdn.microsoft.com/zh-cn/library/ms190417
[_Transact-SQL 1]: http://msdn.microsoft.com/zh-cn/library/ms187373
[Link 4]: http://www.cnblogs.com/Josh-or-Tom/admin/