在計畫指南中使用 INDEX 和 FORCESEEK 查詢提示

發行項
01/19/2011

您可以指定 INDEX 和 FORCESEEK 資料表提示當做查詢提示。將這些提示指定為查詢提示時，其行為模式就與內嵌資料表或檢視表提示相同。

INDEX 提示會強制查詢最佳化工具只使用指定的索引來存取查詢中參考之資料表或檢視表內的資料。FORCESEEK 提示會強制最佳化工具只使用索引搜尋作業，以存取參考之資料表或檢視表內的資料。這些提示可用於計畫指南的 OPTION 子句，以便影響查詢的最佳化。當查詢與計畫指南相符時，將查詢編譯和最佳化之前，會先將計畫指南中所指定的 OPTION 子句加入查詢中。如需有關計畫指南的詳細資訊，請參閱＜了解計畫指南＞。

警告

不當使用查詢提示的計畫指南可能會造成編譯、執行或效能上的問題。計畫指南應該只能由資深的開發人員與資料庫管理員使用。

當 INDEX 和 FORCESEEK 資料表提示指定為查詢提示時，對於下列物件是有效的：

資料表
檢視表
索引檢視表
通用資料表運算式 (此提示必須指定於結果集擴展此通用資料表運算式的 SELECT 陳述式內)。
動態管理檢視表
具名子查詢

不能針對資料表值函式、資料表變數或 OPENROWSET 陳述式來指定資料表提示。

若要針對索引檢視表指定索引提示，也必須在 OPTION 子句中指定 NOEXPAND 提示，否則會忽略此索引提示。如需詳細資訊，請參閱＜解析檢視上的索引＞。

如需用於將 INDEX 和 FORCESEEK 提示指定為查詢提示之語法的詳細資訊，請參閱＜查詢提示 (Transact-SQL)＞。

最佳作法

以下是建議的最佳作法：

只有在計畫指南的內容中或是在測試計畫指南陳述式時的特定查詢中，才能使用 INDEX 和 FORCESEEK 提示當做查詢提示。如果是所有其他特定的查詢，請指定這些提示當做資料表提示。
在使用 FORCESEEK 提示之前，請確定資料庫上的統計資料是最新且正確的。

藉由最新的統計資料，最佳化工具將可正確評估不同查詢計畫的成本並選擇高品質的計畫。因此，我們建議您最好針對每一個使用者資料庫將 AUTO_CREATE_STATISTICS 和 AUTO_UPDATE_STATISTICS 設定為 ON (預設值)。另外，您也可以使用 UPDATE STATISTICS 陳述式，手動更新資料表或檢視表上的統計資料。
請勿搭配 FORCESEEK 使用 INDEX 提示，這是不必要的。也就是說，FORCESEEK 本身就會產生充足的計畫，如果同時也使用 INDEX 提示則可能會過度限制最佳化工具的選擇。此外，如果您變更資料表的實體結構描述來刪除提示中所指定的索引，則 INDEX 提示將會造成查詢失敗。對照之下，只要 FORCESEEK 提示套用所在的資料表上至少有一個可使用的索引，查詢就會編譯 (即使您變更索引結構)。
請勿搭配 FORCESEEK 提示使用 INDEX 提示 INDEX (0)。INDEX (0) 會強制掃描基底資料表。搭配 FORCESEEK 使用時，不會找到任何計畫，而且會傳回 8622 錯誤。
請勿搭配 FORCESEEK 提示使用 USE PLAN 查詢提示。如果您這樣做的話，會忽略 FORCESEEK 提示。

搭配其他資料表提示使用 INDEX 和 FORCESEEK 提示

可以針對沒有任何現有資料表提示的查詢來指定 INDEX 和 FORCESEEK 提示，或是可以用這些提示來取代查詢中一或多個現有的 INDEX 或 FORCESEEK 提示。如果配合計畫指南的查詢已經有 WITH 子句指定這些資料表提示，則計畫指南的 @hints 參數中指定的提示會取代查詢中的這些提示。例如，如果此查詢包含 HumanResources.Employee 資料表的 WITH INDEX (PK_Employee_BusinessEntityID) 資料表提示，而且計畫指南中的 @hints 參數指定了 OPTION (TABLE HINT ( HumanResources.Employee, INDEX( AK_Employee_LoginID ) )，則查詢最佳化工具將會使用 K_Employee_LoginID 索引。

計畫指南中不允許使用 INDEX 和 FORCESEEK 以外的資料表提示當做查詢提示，除非查詢已經有一個指定資料表提示的 WITH 子句。在此情況下，也必須在 OPTION 子句中使用 TABLE HINT 將相符的提示指定為查詢提示，以保留此查詢的語意。例如，如果此查詢包含資料表提示 NOLOCK，則計畫指南的 @hints 參數除了 OPTION 子句中的任何 INDEX 或 FORCESEEK 資料表提示以外，也必須包含 NOLOCK 提示。請參閱本主題稍後的範例 C。如果在 OPTION 子句中使用 TABLE HINT 來指定 INDEX 或 FORCESEEK 以外的資料表提示，但是沒有相符的查詢提示 (反之亦然)，則會引發錯誤 8702，指出 OPTION 子句可能會造成查詢語意變更及導致查詢失敗。

搭配其他查詢提示使用 INDEX 和 FORCESEEK 提示

如果配合計畫指南的查詢已經有 OPTION 子句指定查詢提示，則計畫指南的 @hints 參數中指定的查詢提示會取代查詢中的這些提示。然而，如果是要配合已經有 OPTION 子句之查詢的計畫指南，當您指定要在 sp_create_plan_guide (Transact-SQL) 陳述式中符合的查詢文字時，必須要併入查詢的 OPTION 子句。如果您要將計畫指南中所指定的提示加入查詢中已存在的提示，您不應該取代它們，而是必須在計畫指南的 OPTION 子句中同時指定原始提示和其他提示。

範例

A. 使用 FORCESEEK

下列範例會在計畫指南的 @hints 參數中使用 FORCESEEK 提示。這個選項會強制最佳化工具使用索引搜尋作業，以存取 HumanResources.Employee 資料表內的資料。請注意，這樣可能會造成最佳化工具使用不是資料表提示中所指定的索引。

USE AdventureWorks2008R2;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide3', 
    @stmt = N'SELECT c.LastName, c.FirstName, HumanResources.Employee.JobTitle
              FROM HumanResources.Employee
              JOIN Person.Person AS c ON HumanResources.Employee.BusinessEntityID = c.BusinessEntityID
              WHERE HumanResources.Employee.OrganizationLevel = 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

B. 使用多個資料表提示

下列範例會將 INDEX 提示套用到某個資料表，並將 FORCESEEK 提示套用到另一個資料表。

USE AdventureWorks2008R2;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide4', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle
              FROM HumanResources.Employee AS e 
              JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID
              WHERE OrganizationLevel = 3;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT ( e, INDEX( IX_Employee_OrganizationLevel_OrganizationNode ) ) 
                       , TABLE HINT ( c, FORCESEEK) )';
GO

C. 指定影響語意的提示

下列範例在查詢中包含兩個資料表提示：影響語意的 NOLOCK 以及不會影響語意的 INDEX。為了保留查詢的語意，會在計畫指南的 OPTIONS 子句中指定 NOLOCK 提示。除了 NOLOCK 提示以外，當編譯及最佳化陳述式時，也會指定 INDEX 和 FORCESEEK 提示，並用它們來取代查詢中不會影響語意的 INDEX 提示。

USE AdventureWorks2008R2;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide6', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle
              FROM HumanResources.Employee AS e 
              JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID
              WHERE OrganizationLevel = 3;',
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT ( e, INDEX( IX_Employee_OrganizationLevel_OrganizationNode) , NOLOCK, FORCESEEK ))';
GO

下列範例示範另一個方法來保留查詢的語意，並讓最佳化工具選擇使用不是資料表提示中所指定的索引。其作法是在 OPTIONS 子句中指定 NOLOCK 提示 (因為它會影響語意)，並在指定 TABLE HINT 關鍵字時，只包含資料表參考而沒有 INDEX 提示。

USE AdventureWorks2008R2;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide7', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle
              FROM HumanResources.Employee AS e 
              JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID
              WHERE OrganizationLevel = 2;',
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT ( e, NOLOCK))';
GO

D. 使用 TABLE HINT 來覆寫現有的資料表提示

下列範例會示範如何使用 TABLE HINT，而不指定 INDEX 提示來覆寫查詢之 FROM 子句中所指定的 INDEX 資料表提示行為。這個方法可讓最佳化工具選擇使用不是資料表提示中所指定的索引。

USE AdventureWorks2008R2;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide5', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle
              FROM HumanResources.Employee AS e WITH (INDEX (IX_Employee_OrganizationLevel_OrganizationNode))
              JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID
              WHERE OrganizationLevel = 3;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT(e))';
GO