淺談SQL Server 2008中的Hints(提示)

作者：邀月 2012-04-16 09:55:38

SQL Server并不是萬能的，有時候，我們通過查詢執行計劃、表統計信息、支撐的索引及其他因素，研究性能之后，發現查詢優化器選擇的執行計劃沒有達到預期的效果，或者說，查詢優化器做出了錯誤的選擇。此時我們可能需要使用Hints（提示）來覆蓋SQL Server查詢優化器決定的過程。

SQL Server 2008中SQL應用系列--目錄索引

SQL Server的系統查詢過程負責在SELECT查詢執行時候產生查詢執行計劃。SQL Server會“智能”地選擇一個高效計劃來取代低效的一個。大多數時候，SQL Server會把這份工作干得很棒。但正如有些人所擔憂的，SQL Server并不是萬能的，有時候，我們通過查詢執行計劃、表統計信息、支撐的索引及其他因素，研究性能之后，發現查詢優化器選擇的執行計劃沒有達到預期的效果，或者說，查詢優化器做出了錯誤的選擇。此時我們可能需要使用Hints（提示）來覆蓋SQL Server查詢優化器決定的過程。

看MSDN（http://msdn.microsoft.com/en-us/library/ms187713%28v=sql.105%29.aspx），Hints(提示)是指定的強制選項或策略，由 SQL Server 查詢處理器針對 SELECT、INSERT、UPDATE 或 DELETE 語句執行。提示將覆蓋查詢優化器可能為查詢選擇的任何執行計劃。

使用Hints之前，我有義務提醒一下各位讀者：

SQL Server絕大多數情況下會做出正確的選擇，即便使用的Hints短期內有效，但隨著數據庫內容的更改，使用的查詢計劃反而可能更高效，但此時因為Hints更霸道，SQL Server并不會“自作主張”地使用優化器。
SQL Server發布補丁后，有效的Hints也可能會改變。

本文將要的介紹的內容包括：SQL Server三種不同類型的Hints：聯接提示（Join Hints）、查詢提示（Query Hints）、表提示（Table Hints），包括SQL Server2008中引入的提示ForceSeek，可以用它將索引查找來替換索引掃描。

1、使用聯接提示（Join Hints）

官方解釋：聯接提示用于指定查詢優化器在兩個表之間強制執行聯接策略。（http://msdn.microsoft.com/zh-cn/library/ms173815%28v=sql.105%29.aspx）

用法：<join_hint> ::= { LOOP | HASH | MERGE | REMOTE }

聯結提示會強制查詢優化器來使用你命令的方式聯結表，通過內部JOIN操作來實現。可用的聯結提示：

邀月工作室

代碼演示：

/********* 使用Hints聯結 ***************/  
/********* 3w@live.cn 邀月 ************/  
 
USE TestDb2  
GO  
IF NOT OBJECT_ID('HintsDemo','U') IS NULL 
DROP TABLE HintsDemo  
GO  
IF NOT OBJECT_ID('HintsDemo2','U') IS NULL 
DROP TABLE HintsDemo2  
GO  
----創建測試數據表  
CREATE TABLE dbo.HintsDemo (HID int ,HTitle Nvarchar(50))  
GO  
CREATE TABLE dbo.HintsDemo2 (HID2 int ,HID int)  
GO  
----插入20條數據  
INSERT HintsDemo(HID,HTitle)  
VALUES ( cast(rand()*10 AS INT),replicate('X',cast(rand()*25 AS INT)) )  
GO 20--重復該語句20次,邀月注  
 
INSERT HintsDemo2  
SELECT TOP 10 cast(rand()*10 AS INT), HID from HintsDemo  
GO 2--重復該語句2次，邀月注  
 
--此時兩個表各有20條記錄  
SELECT * FROM HintsDemo  
SELECT * FROM HintsDemo2  
 
SET SHOWPLAN_XML ON 
GO  
 
SELECT h.HID,h.HTitle,d.HID2  
FROM HintsDemo h  
INNER JOIN HintsDemo2 d ON 
h.HID = d.HID  
GO  
 
SET SHOWPLAN_XML OFF 
GO

邀月工作室

下面使用Hash Join

SET SHOWPLAN_XML ON 
GO  
 
SELECT h.HID,h.HTitle,d.HID2  
FROM HintsDemo h  
Inner HASH JOIN HintsDemo2 d ON 
h.HID = d.HID  
GO  
 
SET SHOWPLAN_XML OFF 
GO

邀月工作室

2、使用查詢提示（Query Hints）

官方解釋：在查詢語句的持續時間內，查詢提示優先于查詢優化器的默認行為。您可以使用查詢提示指定受影響的表的鎖定方法、一個或多個索引、一個查詢處理操作（如表掃描或索引查找）或其他選項。查詢提示應用于整個查詢。（http://msdn.microsoft.com/zh-cn/library/ms181714%28v=sql.105%29.aspx）

它的參數比較復雜：

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

一般情況下，我們可以在存儲過程中使用ReCompile查詢提示，這樣可以實現SQL語句級的重編譯，而不是整個存儲過程（批處理語句）的重編譯。我們通過一個示例來說明。

/********* 使用查詢提示 ***************/  
/********* 3w@live.cn 邀月 ************/  
DECLARE @HintsTitleDemo nvarchar(50) = '0E2FAB59-9A22-4E14-B7BE-33AB500E3B9E' 
 
SELECT HID,HTitle  
FROM HintsDemo  
WHERE HTitle = @HintsTitleDemo  
ORDER BY HID  
/*  
HID HTitle  
6 0E2FAB59-9A22-4E14-B7BE-33AB500E3B9E  
*/  
 
--使用DMV查詢查看內存中的統計計劃是否可以重用  
SELECT cacheobjtype, objtype, usecounts  
FROM sys.dm_exec_cached_plans  
CROSS APPLY sys.dm_exec_sql_text(plan_handle)  
WHERE text LIKE 'DECLARE @HintsTitleDemo%'

邀月工作室

--先清除過程緩存,！！！請不要在生產環境中使用下句  
DBCC FREEPROCCACHE  
 
/* DBCC execution completed. If DBCC printed error messages, contact your system administrator.*/  
DECLARE @HintsTitleDemo nvarchar(50) = '0E2FAB59-9A22-4E14-B7BE-33AB500E3B9E' 
 
SELECT HID,HTitle  
FROM HintsDemo  
WHERE HTitle = @HintsTitleDemo  
ORDER BY HID  
OPTION (RECOMPILE)--強制重新編譯

邀月工作室

再次友情提醒，絕大多數情況下，SQL Server 會做出較優的選擇，極端情況下，我們才需要干預它的查詢計劃，以覆蓋SQL Server的選擇。

3、表提示（Table Hints）

官方解釋：通過指定鎖定方法、一個或多個索引、查詢處理操作（如表掃描或索引查找）或其他選項，表提示可在數據操作語言 (DML) 語句執行期間覆蓋查詢優化器的默認行為。表提示在 DML 語句的 FROM 子句中指定，僅影響在該子句中引用的表或視圖。（http://msdn.microsoft.com/zh-cn/library/ms187373%28v=sql.105%29.aspx）

表提示與查詢提示類似，用于覆蓋Select、INSERT、UPDATE和DELETE的默認行為。可以為一個表設置多個表提示，并使用逗號分開，只要它們不改屬于同個分組即可。

WITH  ( <table_hint> [ [, ]...n ] )  
 
<table_hint> ::=  [ NOEXPAND ] {     INDEX  ( index_value [ ,...n ] ) | INDEX =  ( index_value )    | FORCESEEK [( index_value ( index_column_name  [ ,... ] ) ) ]   | FORCESCAN   | FORCESEEK   | HOLDLOCK   | NOLOCK   
  | NOWAIT   | PAGLOCK   | READCOMMITTED   | READCOMMITTEDLOCK   | READPAST   | READUNCOMMITTED   | REPEATABLEREAD   | ROWLOCK   | SERIALIZABLE   
  | SPATIAL_WINDOW_MAX_CELLS = integer   | TABLOCK   | TABLOCKX   | UPDLOCK   | XLOCK   
}   
 
<table_hint_limited> ::=  
{     KEEPIDENTITY   | KEEPDEFAULTS   | HOLDLOCK   | IGNORE_CONSTRAINTS   | IGNORE_TRIGGERS   | NOLOCK   | NOWAIT   | PAGLOCK   | READCOMMITTED   | READCOMMITTEDLOCK   | READPAST   | REPEATABLEREAD   | ROWLOCK   | SERIALIZABLE   | TABLOCK   | TABLOCKX   | UPDLOCK   | XLOCK   
}

這里就不舉例了，看MSDN（http://msdn.microsoft.com/zh-cn/library/ms187373%28v=sql.105%29.aspx）即可。

值得注意的是NOlOCK選項，如下語句：

--不鎖定執行查詢  
SELECT HID,HTitle  
FROM HintsDemo  
WITH (NOLOCK)  
WHERE HID = 4  
 
/*  
HID HTitle  
4 E6DA3DB2-3D41-47B4-B4E3-DDA90918434C  
4 1C4C9211-EB1C-42B5-A08A-558DC73462B4  
4 667C9985-3B0A-4767-AED9-82FEE623433D  
*/

NOLOCK表提示讓查詢在不在被影響的行或數據是放置共享鎖－－允許你在不被阻塞或不阻塞其他查詢的情況下讀取（但會遇到“臟讀”問題）。

最后，我們來介紹一個SQL Server 2008引入的FORCESEEK表提示，它可以用來將索引掃描替換為索引查找。會有一些原因導致SQL Server產生不良的查詢計劃。例如表數據經常變化并且信息憶不再準確，或帶有拙劣where子句的查詢沒有為查詢優化器過程提供有用的或足夠的信息。

如果為了單獨查找指定數據，而在檢索一行之前對整個非常大的表進行了整表掃描，由此帶來的I/O開銷當然是不能接受的。假定上面的測試表非常大。

/********* 使用ForceSeek強制覆蓋SCAN ***************/  
/********* 3w@live.cn 邀月 ************/  
 
--創建一個測試主鍵  
ALTER table dbo.HintsDemo  
add PKID int Primary key Identity(101,1)  
GO  
 
--此時,SQL Server為我們自動創建一個聚集索引[PK__HintsDem__5E0282723D61619B]  
SELECT * FROM dbo.HintsDemo  
--創建一個非聚集索引  
CREATE NONClustered Index idx_ForceSeekDemo  
ON Testdb2.dbo.HintsDemo(HTitle)  
 
----select *,Row_Number() over(Partition By [HID] Order by [HID] Asc) As RowID  
---- from HintsDemo;  
 
SET SHOWPLAN_XML ON 
GO  
 
SELECT DISTINCT HTitle from HintsDemo  
WHERE HID BETWEEN 8 AND 10 and HTitle ='141466E4-E8CC-4219-A9AF-7C0D2B86A668' 
GO  
 
SET SHOWPLAN_XML OFF

邀月工作室

注意本例使用With(ForceSeek)未必最優，只是提供了一個修改系統訪問數據的方式，邀月注

SET SHOWPLAN_XML ON 
GO  
--注意本例使用With(ForceSeek)未必最優，只是提供了一個修改系統訪問數據的方式,邀月注  
SELECT DISTINCT HTitle from HintsDemo WITH (FORCESEEK)  
WHERE HID BETWEEN 8 AND 10 and HTitle ='141466E4-E8CC-4219-A9AF-7C0D2B86A668' 
GO  
 
SET SHOWPLAN_XML OFF

邀月工作室

你也可以更進一步指定使用哪個索引

SELECT DISTINCT HTitle from HintsDemo WITH (FORCESEEK,INDEX(idx_ForceSeekDemo))  
WHERE HID BETWEEN 8 AND 10 and HTitle ='141466E4-E8CC-4219-A9AF-7C0D2B86A668' 
GO

對于本例，如果你需要得更好的性能，可以考慮使用SQL Server引入的指定行集索引功能，比如你只關注某房價表中均價在5000-6000元的樓盤信息，那么可以專門為這個區間建立索引。這個屬于題外話，不在本文討論之列。

小結：本文簡要介紹SQL Server三種不同類型的聯接提示（Join Hints）、查詢提示（Query Hints）、表提示（Table Hints），包括SQL Server2008中引入的提示ForceSeek，可以用它將索引查找來替換索引掃描。再次提醒一下讀者:提示有風險，使用需謹慎！

原文鏈接：http://www.cnblogs.com/downmoon/archive/2012/04/13/2445694.html

【編輯推薦】

SQL Server 2008新增的變更數據捕獲和更改跟蹤
SQL Server 2008中增強的匯總技巧
從SQL Server刪除大數據說開去
SQL Server 2012的12個激動人心的功能
SQL Server數據庫恢復案例分享

責任編輯：林師授來源：邀月的博客

SQL Server