查询提示 (Transact-SQL)

  • 项目

查询提示指定应在整个查询中使用指示的提示。 它们影响语句中的所有运算符。 如果主查询中涉及 UNION，则只有最后一个涉及 UNION 运算符的查询可以包含 OPTION 子句。 查询提示作为 OPTION 子句的一部分指定。 如果一个或多个查询提示导致查询优化器不能生成有效计划，则引发 8622 错误。

备注

由于 SQL Server 查询优化器通常会为查询选择最佳执行计划，因此我们建议资深开发人员和数据库管理员只有在不得已时才可使用提示。

适用于：

DELETE

INSERT

SELECT

UPDATE

MERGE

适用范围：SQL Server（SQL Server 2008 至当前版本），Windows Azure SQL Database（初始版本至当前版本）。

主题链接图标 Transact-SQL 语法约定

语法

<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 
  | SNAPSHOT
  | 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

    适用于：SQL Server 2012 到 SQL Server 2014。

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

  • MAXDOP number

    适用于：SQL Server 2008 到 SQL Server 2014。

    对于指定了 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 | SNAPSHOT | 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 子句会导致查询的语义发生变化，该查询将失败。

示例

A.使用 MERGE JOIN

下面的示例指定查询中的 JOIN 操作由 MERGE JOIN 执行。 该示例使用 AdventureWorks2012 数据库。

SELECT * 
FROM Sales.Customer AS c
INNER JOIN Sales.CustomerAddress AS ca ON c.CustomerID = ca.CustomerID
WHERE TerritoryID = 5
OPTION (MERGE JOIN);
GO

B.使用 OPTIMIZE FOR

下面的示例指示查询优化器对局部变量 @city\_name 使用值 'Seattle' ，并在优化查询时使用统计数据来确定局部变量 @postal\_code 的值。 该示例使用 AdventureWorks2012 数据库。

DECLARE @city_name nvarchar(30);
DECLARE @postal_code nvarchar(15);
SET @city_name = 'Ascheim';
SET @postal_code = 86171;
SELECT * FROM Person.Address
WHERE City = @city_name AND PostalCode = @postal_code
OPTION ( OPTIMIZE FOR (@city_name = 'Seattle', @postal_code UNKNOWN) );

C.使用 MAXRECURSION

可以使用 MAXRECURSION 来防止不合理的递归公用表表达式进入无限循环。 下面的示例特意创建了一个无限循环，然后使用 MAXRECURSION 提示将递归级别限制为两级。 该示例使用 AdventureWorks2012 数据库。

--Creates an infinite loop
WITH cte (CustomerID, PersonID, StoreID) AS
(
    SELECT CustomerID, PersonID, StoreID
    FROM Sales.Customer
    WHERE PersonID IS NOT NULL
  UNION ALL
    SELECT cte.CustomerID, cte.PersonID, cte.StoreID
    FROM cte 
    JOIN  Sales.Customer AS e 
        ON cte.PersonID = e.CustomerID
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT CustomerID, PersonID, StoreID
FROM cte
OPTION (MAXRECURSION 2);
GO

更正代码错误之后就不再需要 MAXRECURSION。

D.使用 MERGE UNION

下面的示例使用 MERGE UNION 查询提示。 该示例使用 AdventureWorks2012 数据库。

SELECT *
FROM HumanResources.Employee AS e1
UNION
SELECT *
FROM HumanResources.Employee AS e2
OPTION (MERGE UNION);

E.使用 HASH GROUP 和 FAST

下面的示例使用 HASH GROUP 和 FAST 查询提示。 该示例使用 AdventureWorks2012 数据库。

SELECT ProductID, OrderQty, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (HASH GROUP, FAST 10);
GO

F.使用 MAXDOP

下面的示例使用 MAXDOP 查询提示。 该示例使用 AdventureWorks2012 数据库。

适用于：SQL Server 2008 到 SQL Server 2014。

 
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (MAXDOP 2);

G.使用 INDEX

以下示例使用 INDEX 提示。 第一个示例指定了一个索引。 第二个示例为单个表引用指定多个索引。 在这两个示例中，由于对使用别名的表应用了 INDEX 提示，因此 TABLE HINT 子句还必须将相同的别名指定为公开的对象名称。 该示例使用 AdventureWorks2012 数据库。

EXEC sp_create_plan_guide 
    @name = N'Guide1', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e 
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID
              WHERE e.ManagerID = 2;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT(e, INDEX (IX_Employee_ManagerID)))';
GO
EXEC sp_create_plan_guide 
    @name = N'Guide2', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID
              WHERE e.ManagerID = 2;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT(e, INDEX(PK_Employee_EmployeeID, IX_Employee_ManagerID)))';
GO

H.使用 FORCESEEK

下面的示例使用 FORCESEEK 表提示。 由于对使用由两部分组成的名称的表应用了 INDEX 提示，因此 TABLE HINT 子句还必须将相同的由两部分组成的名称指定为公开的对象名称。 该示例使用 AdventureWorks2012 数据库。

EXEC sp_create_plan_guide 
    @name = N'Guide3', 
    @stmt = N'SELECT c.LastName, c.FirstName, HumanResources.Employee.Title
              FROM HumanResources.Employee
              JOIN Person.Contact AS c ON HumanResources.Employee.ContactID = c.ContactID
              WHERE HumanResources.Employee.ManagerID = 3
              ORDER BY c.LastName, c.FirstName;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT( HumanResources.Employee, FORCESEEK))';
GO

I.使用多个表提示

下面的示例将 INDEX 提示应用到一个表，将 FORCESEEK 提示应用到另一个表。 该示例使用 AdventureWorks2012 数据库。

EXEC sp_create_plan_guide 
    @name = N'Guide4', 
    @stmt = N'SELECT e.ManagerID, c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e 
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID
              WHERE e.ManagerID = 3;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT ( e, INDEX( IX_Employee_ManagerID ) ) 
                       , TABLE HINT ( c, FORCESEEK) )';

J.使用 TABLE HINT 覆盖现有的表提示

下面的示例演示如何在不指定提示的情况下使用 TABLE HINT 提示覆盖在查询的 FROM 子句中指定的 INDEX 表提示的行为。 该示例使用 AdventureWorks2012 数据库。

EXEC sp_create_plan_guide 
    @name = N'Guide5', 
    @stmt = N'SELECT e.ManagerID, c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e WITH (INDEX (IX_Employee_ManagerID))
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID
              WHERE e.ManagerID = 3;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT(e))';
GO

K.指定语义影响的表提示

下面的示例在查询中包含两个表提示：NOLOCK 和 INDEX，其中前者为影响语义的提示，后者为不影响语义的提示。 若要保留查询的语义，应在计划指南的 OPTIONS 子句中指定 NOLOCK 提示。 除 NOLOCK 提示外，还指定了 INDEX 和 FORCESEEK 提示，它们在编译和优化语句时将替换查询中不影响语义的 INDEX 提示。 该示例使用 AdventureWorks2012 数据库。

EXEC sp_create_plan_guide 
    @name = N'Guide6', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e 
                   WITH (NOLOCK, INDEX (PK_Employee_EmployeeID))
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID
              WHERE e.ManagerID = 3;',
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT ( e, INDEX( IX_Employee_ManagerID) , NOLOCK, FORCESEEK ))';
GO

下面的示例演示另一种保留查询语义并使优化器能够选择并非在表提示中指定的索引的方法。 此方法是这样实现的：在 OPTIONS 子句中指定 NOLOCK 提示（因为它影响语义），并指定只有表引用而没有 INDEX 提示的 TABLE HINT 关键字。 该示例使用 AdventureWorks2012 数据库。

EXEC sp_create_plan_guide 
    @name = N'Guide7', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e 
                   WITH (NOLOCK, INDEX (PK_Employee_EmployeeID))
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID
              WHERE e.ManagerID = 2;',
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT ( e, NOLOCK))';

请参阅

参考

提示 (Transact-SQL)

sp_create_plan_guide (Transact-SQL)

sp_control_plan_guide (Transact-SQL)