使用 FORCESEEK 表提示
From: https://msdn.microsoft.com/zh-cn/vstudio/bb510478.aspx
FORCESEEK 表提示强制查询优化器仅使用索引查找操作作为访问查询引用的表或者视图中的数据的路径。 您可使用该表提示覆盖查询优化器选择的默认计划，从而避免因低效的查询计划而导致的性能问题。 例如，如果计划中包含表扫描运算符或者索引扫描运算符，且相应的表导致执行查询期间读取十分频繁，如 STATISTICS IO 输出中所示，则强制索引查找操作可能会获得更好的查询性能。 如果优化器因基数或开销估计不准确而在编译计划时倾向于选择扫描操作，则更是如此。
FORCESEEK 适用于聚集索引查找和非聚集索引查找操作。 可以在 SELECT 语句的 FROM 子句和 UPDATE 或 DELETE 语句的 FROM <table_source> 子句中为任何表或视图指定 FORCESEEK。
.gif) 注意 |
|---|
由于 SQL Server 查询优化器通常会为查询选择最优执行计划，因此我们建议，只有在最后迫不得已的情况下才可由资深的开发人员和数据库管理员使用提示。 |
如果查询计划对表或视图使用表扫描或索引扫描运算符，FORCESEEK 表提示可能有用，但索引查找运算符可能更为高效。 请考虑以下查询和后续执行计划。
USE AdventureWorks2008R2; GO SELECT * FROM Sales.SalesOrderHeader AS h INNER JOIN Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID WHERE h.TotalDue > 100 AND (d.OrderQty > 5 OR d.LineTotal < 1000.00); GO
下列执行计划显示查询优化器选择了聚集索引扫描运算符来访问两个表中的数据。
.gif)
如下列查询所示，可通过指定 FORCESEEK 提示强制查询优化器对 Sales.SalesOrderDetail表执行查找操作。
USE AdventureWorks2008R2; GO SELECT * FROM Sales.SalesOrderHeader AS h INNER JOIN Sales.SalesOrderDetail AS d WITH (FORCESEEK) ON h.SalesOrderID = d.SalesOrderID WHERE h.TotalDue > 100 AND (d.OrderQty > 5 OR d.LineTotal < 1000.00); GO
下列执行计划显示了在查询中使用 FORCESEEK 提示的结果。 聚集索引查找操作被用来访问 Sales.SalesOrderDetail表中的数据。
.gif)
支持索引并集和交集
FORCESEEK 提示支持索引并集和交集。 此提示使查询优化器更可能使用这些技术。 为了避免延长简单查询的编译时间，通常仅在根据将列的基数和选择性考虑在内的规则时才会选择索引并集和交集。 不过，指定 FORCESEEK 提示后，会跳过此类规则，而始终将这些技术考虑在内。 例如，考虑以下查询：
SELECT * FROM T WITH(FORCESEEK) WHERE T.a = 1 AND T.b = 2;
如果表 T中的列 a和列 b具有单独的非聚集索引，则可选择索引交集计划。 也就是说，此计划包含对列 a的非聚集索引查找操作和对列 b的非聚集索引查找操作，并且在对基表执行查找操作之前对所得的索引键集求交集。
下例中选择了一个索引并集计划。 也就是说，计划包含对列 a的查找操作和对列 b的查找操作，并且在对基表执行查找操作之前对所得的索引键集求并集。
SELECT * FROM T WITH(FORCESEEK) WHERE T.a = 1 OR T.b = 2;
对使用 LIKE 或 IN 的查询应用 FORCESEEK
当查询将 IN 或 LIKE 用做搜索谓词时，查询优化器规则和不准确的基数估计也会导致优化器执行表扫描或索引扫描操作，而不是索引查找操作。
下面的示例演示将 LIKE 或 IN 用做搜索谓词时，FORCESEEK 提示如何强制查询优化器执行索引查找操作，而不是表扫描操作。 若要查看查询执行计划，请在运行此示例前单击“包括实际的执行计划”工具栏按钮。
USE tempdb; GO DROP TABLE t; GO CREATE TABLE t(i int UNIQUE, j int, vc varchar(100)); CREATE INDEX t_vc ON t(vc); GO DECLARE @p1 int, @p2 int, @p3 int, @p4 int, @p5 int; SELECT * FROM t WHERE i IN (@p1, @p2, @p3, @p4, @p5); GO DECLARE @p1 int, @p2 int, @p3 int, @p4 int, @p5 int; SELECT * FROM t WITH (FORCESEEK) WHERE i IN (@p1, @p2, @p3, @p4, @p5); GO SELECT * FROM t WHERE vc LIKE 'Test%'; GO SELECT * FROM t WITH (FORCESEEK) WHERE vc LIKE 'Test%'; GO DECLARE @vc varchar(100); SELECT * FROM t WHERE vc LIKE @vc; GO DECLARE @vc varchar(100); SELECT * FROM t WITH (FORCESEEK) where vc like @vc; GO
指定 FORCESEEK 时有无索引提示均可。 将 FORCESEEK 表提示应用到某一视图或索引视图时，FORCESEEK 提示将递归传播到该视图扩展版本中的所有表。 如果已指定索引提示，则会将其忽略。 如果并非每个基础表都至少包含一个索引，则无法找到计划并返回错误 8622。
对索引视图的引用同时使用 FORCESEEK 和 NOEXPAND 提示时，则使用此索引视图时无需事先将其展开。 FORCESEEK 提示将直接应用到索引视图，这与处理表的方式相同。
如果将 FORCESEEK 提示应用至表引用，则表引用无法参与索引视图匹配。 但是，查询中其他不受 FORCESEEK 提示影响的部分可参与索引视图匹配。 这一点与使用 INDEX 提示时索引视图匹配的行为是相似的。
以下为建议的最佳做法：
在使用 FORCESEEK 表提示之前，请确保数据库中的统计信息是最新的，且准确无误。
最新的统计信息有助于优化器实现对不同查询计划的开销的准确估算，并选择高质量的计划。 因此，建议对于每个用户数据库，都将 AUTO_CREATE_STATISTICS 和 AUTO_UPDATE_STATISTICS 设置为 ON（默认值）。 或者，您可以使用 UPDATE STATISTICS 语句手动更新表或视图的统计信息。
评估查询看其是否存在可能导致不准确基数估计或开销估计的项，如果可能则删除这些项。 例如，用参数或文字替换局部变量并限制查询中多语句表值函数和表变量的使用。 有关要查找的其他项的更多信息，请参阅 Microsoft SQL Server 2005 中查询优化器使用的统计信息。
除非在必要情况下，否则请不要将 INDEX 提示与 FORCESEEK 一起使用。 也就是说，如果单独使用 FORCESEEK 就可生成满足需要的计划，则同时使用 INDEX 提示会过分限制优化器可做的选择。 而且，在更改表的物理架构以删除 INDEX 提示中指定的索引时，此提示将导致查询失败。 相比之下，只要应用 FORCESEEK 提示的表中至少存在一个可使用的索引，则即使在您更改索引结构时查询仍能编译。
请不要将 INDEX 提示 INDEX (0) 与 FORCESEEK 提示一起使用。 INDEX (0) 强制对基表进行扫描。 在与 FORCESEEK 同时使用时，将无法找到计划并返回错误 8622。
请不要将 USE PLAN 查询提示与 FORCESEEK 提示一起使用。 否则，将忽略 FORCESEEK 提示。
任务
参考
Clustered Index Scan Showplan 运算符
Clustered Index Seek Showplan 运算符
Nonclustered Index Seek Showplan 运算符
Nonclustered Index Scan Showplan 运算符
From: https://technet.microsoft.com/en-us/library/bb510478(v=sql.105).aspx
The FORCESEEK table hint forces the query optimizer to use only an index seek operation as the access path to the data in the table or view referenced in the query. You can use this table hint to override the default plan chosen by the query optimizer to avoid performance issues caused by an inefficient query plan. For example, if a plan contains table or index scan operators, and the corresponding tables cause a high number of reads during the execution of the query, as observed in the STATISTICS IO output, forcing an index seek operation may yield better query performance. This is especially true when inaccurate cardinality or cost estimations cause the optimizer to favor scan operations at plan compilation time.
FORCESEEK applies to both clustered and nonclustered index seek operations. It can be specified for any table or view in the FROM clause of a SELECT statement and in the FROM <table_source> clause of an UPDATE or DELETE statement.
 Caution |
|---|
Because the SQL Server query optimizer typically selects the best execution plan for a query, we recommend using hints only as a last resort by experienced developers and database administrators. |
Evaluating Query Plans for FORCESEEK Applicability
The FORCESEEK table hint may be useful when the query plan uses a table or index scan operator on a table or view, but an index seek operator may be more efficient. Consider the following query and subsequent execution plan.
USE AdventureWorks2008R2; GO SELECT * FROM Sales.SalesOrderHeader AS h INNER JOIN Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID WHERE h.TotalDue > 100 AND (d.OrderQty > 5 OR d.LineTotal < 1000.00); GO
The following execution plan shows that the query optimizer chose a clustered index scan operator to access the data in both tables.
You can force the query optimizer to perform a seek operation on the Sales.SalesOrderDetail table by specifying the FORCESEEK hint as shown in the following query.
USE AdventureWorks2008R2; GO SELECT * FROM Sales.SalesOrderHeader AS h INNER JOIN Sales.SalesOrderDetail AS d WITH (FORCESEEK) ON h.SalesOrderID = d.SalesOrderID WHERE h.TotalDue > 100 AND (d.OrderQty > 5 OR d.LineTotal < 1000.00); GO
The following execution plan shows the results of using the FORCESEEK hint in the query. A clustered index seek operation is used to access the data in the Sales.SalesOrderDetail table.
Index Union and Intersection Support
The FORCESEEK hint supports index unions and intersections. The hint makes the query optimizer more likely to use these techniques. To avoid slowing the compilation time of simple queries, index unions and intersections are normally only chosen according to rules that take into account the cardinality and selectivity of the columns. However, when the FORCESEEK hint is specified, such rules are bypassed and these techniques are always considered. For example, consider the following query:
SELECT * FROM T WITH(FORCESEEK) WHERE T.a = 1 AND T.b = 2;
If there are separate, nonclustered indexes on columns a and b in table T, an index intersection plan may be chosen. That is, the plan contains a nonclustered index seek operation on column a and a nonclustered index seek operation on column b, and intersects the resulting index key sets before performing a look-up operation into the base table.
In the following example, an index union plan is chosen. That is, the plan contains a seek operation on column a and a seek operation on column b, and unions the resulting index key sets, before performing a look-up operation into the base table.
SELECT * FROM T WITH(FORCESEEK) WHERE T.a = 1 OR T.b = 2;
Using FORCESEEK in Queries That Use LIKE or IN
Query optimizer rules and poor cardinality estimation can also cause the optimizer to perform a table or index scan operation rather than an index seek when a query uses IN or LIKE as search predicates.
The following example demonstrates how the FORCESEEK hint can force the query optimizer to perform an index seek operation rather than a table scan when LIKE or IN are used as search predicates. To view the query execution plans, click the Include Actual Execution Plan toolbar button before running the example.
USE tempdb; GO DROP TABLE t; GO CREATE TABLE t(i int UNIQUE, j int, vc varchar(100)); CREATE INDEX t_vc ON t(vc); GO DECLARE @p1 int, @p2 int, @p3 int, @p4 int, @p5 int; SELECT * FROM t WHERE i IN (@p1, @p2, @p3, @p4, @p5); GO DECLARE @p1 int, @p2 int, @p3 int, @p4 int, @p5 int; SELECT * FROM t WITH (FORCESEEK) WHERE i IN (@p1, @p2, @p3, @p4, @p5); GO SELECT * FROM t WHERE vc LIKE 'Test%'; GO SELECT * FROM t WITH (FORCESEEK) WHERE vc LIKE 'Test%'; GO DECLARE @vc varchar(100); SELECT * FROM t WHERE vc LIKE @vc; GO DECLARE @vc varchar(100); SELECT * FROM t WITH (FORCESEEK) where vc like @vc; GO
FORCESEEK can be specified with or without an index hint. When you apply a FORCESEEK table hint to a view or indexed view, the FORCESEEK hint is recursively propagated over all the tables in the expanded version of the view. The index hint, if specified, is ignored. If the underlying tables do not have at least one index each, no plan is found and error 8622 is returned.
When you use the FORCESEEK and NOEXPAND hints together on a reference to an indexed view, the indexed view is used without expanding it first. The FORCESEEK hint is applied directly to the indexed view, which is treated just like a table.
If you apply a FORCESEEK hint to a table reference, the table reference can not participate in indexed view matching. However, other parts of the query that are unaffected by the FORCESEEK hint can participate in indexed view matching. This is comparable to the behavior of indexed view matching when used with INDEX hints.
We recommend the following best practices:
Before using the FORCESEEK table hint, make sure that statistics on the database are current and accurate.
Up-to-date statistics allow the optimizer to accurately assess the cost of different query plans, and choose a high-quality plan. Therefore, we recommend setting the AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS to ON (the default) for every user database. Alternatively, you can manually update statistics on a table or view by using the UPDATE STATISTICS statement.
Evaluate the query for items that can cause poor cardinality or cost estimates and remove these items if possible. For example, replace local variables with parameters or literals and limit the use of multi-statement table-valued functions and table variables in the query. For more information about other items to look for, see Statistics Used by the Query Optimizer in Microsoft SQL Server 2005.
Do not unnecessarily use the INDEX hint in combination with FORCESEEK. That is, if FORCESEEK alone produces a sufficient plan, also using the INDEX hint may excessively limit the choices the optimizer has. Furthermore, an INDEX hint will cause your query fail if you change the physical schema of your table to eliminate the index specified in the hint. By contrast, as long as at least one usable index exists on the table on which the FORCESEEK hint is applied, the query will compile even as you change your index structures.
Do not use the INDEX hint INDEX (0) with the FORCESEEK hint. INDEX (0) forces a scan of the base table. When used with FORCESEEK, no plan is found and error 8622 is returned.
Do not use the USE PLAN query hint with the FORCESEEK hint. If you do, the FORCESEEK hint is ignored.
Tasks
How to: Display an Actual Execution Plan
Reference
Clustered Index Scan Showplan Operator
Clustered Index Seek Showplan Operator
Nonclustered Index Seek Showplan Operator
Nonclustered Index Scan Showplan Operator
