查詢提示 (Transact-SQL)
查詢提示會指定所指出的提示應該用於整個查詢。 查詢提示會影響陳述式中的所有運算子。 如果主要查詢涉及 UNION,只有最後一個包含 UNION 作業的查詢可以有 OPTION 子句。 查詢提示指定在 OPTION 子句中。 如果一個或多個查詢提示造成查詢最佳化工具不會產生有效的計畫,就會產生 8622 錯誤。
注意 |
---|
由於 SQL Server 查詢最佳化工具通常會選取最好的查詢執行計畫,因此,我們建議資深的開發人員和資料庫管理員將它當做最後的解決辦法。 |
適用於:
<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 }
除非是在陳述式內使用 SELECT 子句,否則無法在 INSERT 陳述式中指定查詢提示。
您只能在最上層查詢中指定查詢提示,不能在子查詢中指定查詢提示。 當資料表提示指定為查詢提示時,您可以在最上層查詢或子查詢中指定此提示。不過,在 TABLE HINT 子句中針對 exposed_object_name 所指定的值必須與查詢或子查詢中的公開名稱完全相符。
將資料表提示指定為查詢提示
建議您只在計畫指南內容中使用 INDEX、FORCESCAN 或 FORCESEEK 資料表提示為查詢提示。 當您無法修改原始的查詢 (例如,因為它是協力廠商應用程式) 時,計畫指南就很有用。 在計畫指南中指定的查詢提示會先加入至查詢,然後再進行編譯和最佳化。 若為特定查詢,請在測試計畫指南陳述式時才使用 TABLE HINT 子句。 如果是所有其他特定的查詢,我們建議您將這些提示指定成資料表提示。
將 INDEX、FORCESCAN 和 FORCESEEK 資料表提示指定為查詢提示適用於下列物件:
-
資料表
-
檢視表
-
索引檢視
-
通用資料表運算式 (此提示必須指定於結果集擴展此通用資料表運算式的 SELECT 陳述式內)
-
動態管理檢視
-
具名子查詢
對於不具任何現有資料表提示的查詢,也可將 INDEX、FORCESCAN 和 FORCESEEK 資料表提示指定為查詢提示,或以其取代查詢中現有的 INDEX、FORCESCAN 或 FORCESEEK 提示。 除非查詢已有指定資料表提示的 WITH 子句,否則不可使用 INDEX、FORCESCAN 和 FORCESEEK 以外的資料表提示做為查詢提示。 在此情況下,也必須在 OPTION 子句中使用 TABLE HINT 將相符的提示指定為查詢提示,以保留此查詢的語意。 例如,如果此查詢包含資料表提示 NOLOCK,則計畫指南的 @hints 參數中的 OPTION 子句也必須包含 NOLOCK 提示。 請參閱<範例 K>。 如果在 OPTION 子句中使用 TABLE HINT 指定 INDEX、FORCESCAN 或 FORCESEEK 以外的資料表提示,但卻沒有相符的查詢提示 (反之亦然),將會引發錯誤 8702 (指出 OPTION 子句可能會造成查詢語意變更) 導致查詢失敗。
A.使用 MERGE JOIN
下列範例指定由 MERGE JOIN 來執行查詢中的 JOIN 作業。
B.使用 OPTIMIZE FOR
下列範例會指示查詢最佳化工具在最佳化查詢時,將 'Seattle' 值用於區域變數 @city_name 及使用統計資料判斷區域變數 @postal_code 的值。
USE AdventureWorks2012; GO 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) ); GO
C.使用 MAXRECURSION
您可以利用 MAXRECURSION 來防止形式不良的遞迴通用資料表運算式進入無限迴圈。 下列範例會刻意建立無限迴圈,然後利用 MAXRECURSION 提示,將遞迴層級限制為 2。
USE AdventureWorks2012; GO --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 查詢提示。
E.使用 HASH GROUP 與 FAST
下列範例使用 HASH GROUP 和 FAST 查詢提示。
F.使用 MAXDOP
下列範例使用 MAXDOP 查詢提示。
G.使用 INDEX
下列範例使用 INDEX 提示。 第一個範例會指定單一索引。 第二個範例會針對單一資料表參考指定多個索引。 在這兩個範例中,由於 INDEX 提示會套用在使用別名的資料表上,因此 TABLE HINT 子句也必須與公開物件名稱指定相同的別名。
USE AdventureWorks2012; GO EXEC sp_create_plan_guide @name = N'Guide1', @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 e.OrganizationLevel = 2;', @type = N'SQL', @module_or_batch = NULL, @params = NULL, @hints = N'OPTION (TABLE HINT(e, INDEX (IX_Employee_OrganizationLevel_OrganizationNode)))'; GO EXEC sp_create_plan_guide @name = N'Guide2', @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 e.OrganizationLevel = 2;', @type = N'SQL', @module_or_batch = NULL, @params = NULL, @hints = N'OPTION (TABLE HINT(e, INDEX(PK_Employee_BusinessEntityID, IX_Employee_OrganizationLevel_OrganizationNode)))'; GO
H.使用 FORCESEEK
下列範例使用 FORCESEEK 資料表提示。 由於 INDEX 提示會套用在使用兩部分名稱的資料表上,因此 TABLE HINT 子句也必須與公開物件名稱指定相同的兩部分名稱。
USE AdventureWorks2012; 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
I.使用多個資料表提示
下列範例會將 INDEX 提示套用到某個資料表,並將 FORCESEEK 提示套用到另一個資料表。
USE AdventureWorks2012; 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
J.使用 TABLE HINT 覆寫現有的資料表提示
下列範例會示範如何使用 TABLE HINT 提示,而不指定提示來覆寫查詢之 FROM 子句中所指定的 INDEX 資料表提示行為。
USE AdventureWorks2012; 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
K.指定影響語意的資料表提示
下列範例在查詢中包含兩個資料表提示:影響語意的 NOLOCK 以及不會影響語意的 INDEX。 為了保留查詢的語意,會在計畫指南的 OPTIONS 子句中指定 NOLOCK 提示。 除了 NOLOCK 提示以外,當編譯及最佳化陳述式時,也會指定 INDEX 和 FORCESEEK 提示,並用它們來取代查詢中不會影響語意的 INDEX 提示。
USE AdventureWorks2012; 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 AdventureWorks2012; 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