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

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

SQL Server并不是萬(wàn)能的，有時(shí)候，我們通過(guò)查詢(xún)執(zhí)行計(jì)劃、表統(tǒng)計(jì)信息、支撐的索引及其他因素，研究性能之后，發(fā)現(xiàn)查詢(xún)優(yōu)化器選擇的執(zhí)行計(jì)劃沒(méi)有達(dá)到預(yù)期的效果，或者說(shuō)，查詢(xún)優(yōu)化器做出了錯(cuò)誤的選擇。此時(shí)我們可能需要使用Hints（提示）來(lái)覆蓋SQL Server查詢(xún)優(yōu)化器決定的過(guò)程。

SQL Server 2008中SQL應(yīng)用系列--目錄索引

SQL Server的系統(tǒng)查詢(xún)過(guò)程負(fù)責(zé)在SELECT查詢(xún)執(zhí)行時(shí)候產(chǎn)生查詢(xún)執(zhí)行計(jì)劃。SQL Server會(huì)“智能”地選擇一個(gè)高效計(jì)劃來(lái)取代低效的一個(gè)。大多數(shù)時(shí)候，SQL Server會(huì)把這份工作干得很棒。但正如有些人所擔(dān)憂(yōu)的，SQL Server并不是萬(wàn)能的，有時(shí)候，我們通過(guò)查詢(xún)執(zhí)行計(jì)劃、表統(tǒng)計(jì)信息、支撐的索引及其他因素，研究性能之后，發(fā)現(xiàn)查詢(xún)優(yōu)化器選擇的執(zhí)行計(jì)劃沒(méi)有達(dá)到預(yù)期的效果，或者說(shuō)，查詢(xún)優(yōu)化器做出了錯(cuò)誤的選擇。此時(shí)我們可能需要使用Hints（提示）來(lái)覆蓋SQL Server查詢(xún)優(yōu)化器決定的過(guò)程。

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

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

SQL Server絕大多數(shù)情況下會(huì)做出正確的選擇，即便使用的Hints短期內(nèi)有效，但隨著數(shù)據(jù)庫(kù)內(nèi)容的更改，使用的查詢(xún)計(jì)劃反而可能更高效，但此時(shí)因?yàn)镠ints更霸道，SQL Server并不會(huì)“自作主張”地使用優(yōu)化器。
SQL Server發(fā)布補(bǔ)丁后，有效的Hints也可能會(huì)改變。

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

1、使用聯(lián)接提示（Join Hints）

官方解釋?zhuān)郝?lián)接提示用于指定查詢(xún)優(yōu)化器在兩個(gè)表之間強(qiáng)制執(zhí)行聯(lián)接策略。（http://msdn.microsoft.com/zh-cn/library/ms173815%28v=sql.105%29.aspx）

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

聯(lián)結(jié)提示會(huì)強(qiáng)制查詢(xún)優(yōu)化器來(lái)使用你命令的方式聯(lián)結(jié)表，通過(guò)內(nèi)部JOIN操作來(lái)實(shí)現(xiàn)。可用的聯(lián)結(jié)提示：

邀月工作室

代碼演示：

/********* 使用Hints聯(lián)結(jié) ***************/  
/********* 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  
----創(chuàng)建測(cè)試數(shù)據(jù)表  
CREATE TABLE dbo.HintsDemo (HID int ,HTitle Nvarchar(50))  
GO  
CREATE TABLE dbo.HintsDemo2 (HID2 int ,HID int)  
GO  
----插入20條數(shù)據(jù)  
INSERT HintsDemo(HID,HTitle)  
VALUES ( cast(rand()*10 AS INT),replicate('X',cast(rand()*25 AS INT)) )  
GO 20--重復(fù)該語(yǔ)句20次,邀月注  
 
INSERT HintsDemo2  
SELECT TOP 10 cast(rand()*10 AS INT), HID from HintsDemo  
GO 2--重復(fù)該語(yǔ)句2次，邀月注  
 
--此時(shí)兩個(gè)表各有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、使用查詢(xún)提示（Query Hints）

官方解釋?zhuān)涸诓樵?xún)語(yǔ)句的持續(xù)時(shí)間內(nèi)，查詢(xún)提示優(yōu)先于查詢(xún)優(yōu)化器的默認(rèn)行為。您可以使用查詢(xún)提示指定受影響的表的鎖定方法、一個(gè)或多個(gè)索引、一個(gè)查詢(xún)處理操作（如表掃描或索引查找）或其他選項(xiàng)。查詢(xún)提示應(yīng)用于整個(gè)查詢(xún)。（http://msdn.microsoft.com/zh-cn/library/ms181714%28v=sql.105%29.aspx）

它的參數(shù)比較復(fù)雜：

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

一般情況下，我們可以在存儲(chǔ)過(guò)程中使用ReCompile查詢(xún)提示，這樣可以實(shí)現(xiàn)SQL語(yǔ)句級(jí)的重編譯，而不是整個(gè)存儲(chǔ)過(guò)程（批處理語(yǔ)句）的重編譯。我們通過(guò)一個(gè)示例來(lái)說(shuō)明。

/********* 使用查詢(xún)提示 ***************/  
/********* 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查詢(xún)查看內(nèi)存中的統(tǒng)計(jì)計(jì)劃是否可以重用  
SELECT cacheobjtype, objtype, usecounts  
FROM sys.dm_exec_cached_plans  
CROSS APPLY sys.dm_exec_sql_text(plan_handle)  
WHERE text LIKE 'DECLARE @HintsTitleDemo%'

邀月工作室

--先清除過(guò)程緩存,！！！請(qǐng)不要在生產(chǎn)環(huán)境中使用下句  
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)--強(qiáng)制重新編譯

邀月工作室

再次友情提醒，絕大多數(shù)情況下，SQL Server 會(huì)做出較優(yōu)的選擇，極端情況下，我們才需要干預(yù)它的查詢(xún)計(jì)劃，以覆蓋SQL Server的選擇。

3、表提示（Table Hints）

官方解釋?zhuān)和ㄟ^(guò)指定鎖定方法、一個(gè)或多個(gè)索引、查詢(xún)處理操作（如表掃描或索引查找）或其他選項(xiàng)，表提示可在數(shù)據(jù)操作語(yǔ)言 (DML) 語(yǔ)句執(zhí)行期間覆蓋查詢(xún)優(yōu)化器的默認(rèn)行為。表提示在 DML 語(yǔ)句的 FROM 子句中指定，僅影響在該子句中引用的表或視圖。（http://msdn.microsoft.com/zh-cn/library/ms187373%28v=sql.105%29.aspx）

表提示與查詢(xún)提示類(lèi)似，用于覆蓋Select、INSERT、UPDATE和DELETE的默認(rèn)行為。可以為一個(gè)表設(shè)置多個(gè)表提示，并使用逗號(hào)分開(kāi)，只要它們不改屬于同個(gè)分組即可。

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選項(xiàng)，如下語(yǔ)句：

--不鎖定執(zhí)行查詢(xún)  
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表提示讓查詢(xún)?cè)诓辉诒挥绊懙男谢驍?shù)據(jù)是放置共享鎖－－允許你在不被阻塞或不阻塞其他查詢(xún)的情況下讀取（但會(huì)遇到“臟讀”問(wèn)題）。

最后，我們來(lái)介紹一個(gè)SQL Server 2008引入的FORCESEEK表提示，它可以用來(lái)將索引掃描替換為索引查找。會(huì)有一些原因?qū)е耂QL Server產(chǎn)生不良的查詢(xún)計(jì)劃。例如表數(shù)據(jù)經(jīng)常變化并且信息憶不再準(zhǔn)確，或帶有拙劣where子句的查詢(xún)沒(méi)有為查詢(xún)優(yōu)化器過(guò)程提供有用的或足夠的信息。

如果為了單獨(dú)查找指定數(shù)據(jù)，而在檢索一行之前對(duì)整個(gè)非常大的表進(jìn)行了整表掃描，由此帶來(lái)的I/O開(kāi)銷(xiāo)當(dāng)然是不能接受的。假定上面的測(cè)試表非常大。

/********* 使用ForceSeek強(qiáng)制覆蓋SCAN ***************/  
/********* 3w@live.cn 邀月 ************/  
 
--創(chuàng)建一個(gè)測(cè)試主鍵  
ALTER table dbo.HintsDemo  
add PKID int Primary key Identity(101,1)  
GO  
 
--此時(shí),SQL Server為我們自動(dòng)創(chuàng)建一個(gè)聚集索引[PK__HintsDem__5E0282723D61619B]  
SELECT * FROM dbo.HintsDemo  
--創(chuàng)建一個(gè)非聚集索引  
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)未必最優(yōu)，只是提供了一個(gè)修改系統(tǒng)訪問(wèn)數(shù)據(jù)的方式，邀月注

SET SHOWPLAN_XML ON 
GO  
--注意本例使用With(ForceSeek)未必最優(yōu)，只是提供了一個(gè)修改系統(tǒng)訪問(wèn)數(shù)據(jù)的方式,邀月注  
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

邀月工作室

你也可以更進(jìn)一步指定使用哪個(gè)索引

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

對(duì)于本例，如果你需要得更好的性能，可以考慮使用SQL Server引入的指定行集索引功能，比如你只關(guān)注某房?jī)r(jià)表中均價(jià)在5000-6000元的樓盤(pán)信息，那么可以專(zhuān)門(mén)為這個(gè)區(qū)間建立索引。這個(gè)屬于題外話(huà)，不在本文討論之列。

小結(jié)：本文簡(jiǎn)要介紹SQL Server三種不同類(lèi)型的聯(lián)接提示（Join Hints）、查詢(xún)提示（Query Hints）、表提示（Table Hints），包括SQL Server2008中引入的提示ForceSeek，可以用它將索引查找來(lái)替換索引掃描。再次提醒一下讀者:提示有風(fēng)險(xiǎn)，使用需謹(jǐn)慎！

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

【編輯推薦】