RiCo技術農場

有些查詢陳述句總是讓查詢最佳化程式使用index or table scan 操作來處理，

以前SQL 2000/2005 你可能會用 index hint 讓查詢最佳化程式選擇特定index，

來達到改善查詢效能(可能改用 index seek 操作...等)，

但現在SQL2008 多了 FORCESEEK 資料表提示這個好功能，

讓你可以強制查詢一率都使用Index Seek 操作來處理，

下面我也會貼上前幾天使用 FORCESEEK hint 解決一個查詢問題。

先來說說INDEX hint和FORCESEEK hint有什麼不同?

INDEX hint:

強制查詢最佳化程式只使用所指定的索引來存取相關資料表或檢視表。

FORCESEEK hint:

強制查詢最佳化程式只使用 Index Seek 操作來存取相關資料表或檢視表。

FORCESEEK 注意事項:

(擷取線上叢書)

問題:

開發人員向DBA反應，為什麼SQL Server總是使用 Index Scan 來處理。

這裡我大概模擬當時的Table Layout 、資料量和 前端應用程式的SQL Statement

Table Layout:

NonClusted Index Layout:

select

    ind.name, ind.index_id, ic.index_column_id, col.name,

    ind.*, ic.*, col.*

from 

    sys.indexes ind

inner join 

    sys.index_columns ic on 

      ind.object_id = ic.object_id and ind.index_id = ic.index_id

inner join

    sys.columns col on

      ic.object_id = col.object_id and ic.column_id = col.column_id 

inner join

    sys.tables t on 

      ind.object_id = t.object_id

where 

    ind.is_primary_key = 0 

    and ind.is_unique = 0 

    and ind.is_unique_constraint = 0

    and t.is_ms_shipped = 0    

    and t.name='ap_log'

order by

    t.name, ind.name, ind.index_id, ic.index_column_id

這裡我門主要看nidx_1

資料總筆數:194055

前端AP  SQL Statement 如下(前端AP使用參數化):

declare @myid varchar(10);

set @myid='RichChou' ;

select ApLogId,LogDate,LoginId,LineNumber,LogMessage

from dbo.AP_LOG

where LoginId =@myid

order by LogDate asc

CPU時間:266 ms ，經過時間:4745 ms。

預估成本:17.4167

可以看到這次返回18487 筆資料(選擇性還滿高的)，

但查詢最佳化程式認為使用Index Scan所花費的成本較Index Seek還低，

所以使用Index Scan 操作來處理，這時開發人員就會抱怨，那 nidx_1 是建立好看的嗎?

到這裡我想你應該看出問題在那了，明明選擇性夠高也有使用參數化且相關Index也建立了

但查詢最佳化程式卻選用 Index Scan的操作，

或許你可能會覺得使用Index hint來改善也可以吧，

但如同我前面所說Index hint 和FORCESEEK hint差異，

使用FORCESEEK hint 彈性會來的比較大點(而且使用了 index hint 並不見得就會使用index seek 操作)，

因為你無法確認那天你指定的with (index(nidx_1))會不會被改了名稱..等之類的靈異問題。

使用FORCESEEK

declare @myid varchar(20);

set @myid='RichChou' ;

select ApLogId,LogDate,LoginId,LineNumber,LogMessage

from dbo.AP_LOG with(FORCESEEK) 

where LoginId =@myid

order by LogDate asc

CPU時間:202 ms ，經過時間:950 ms。

預估成本:142.445

結論:

可以看到經過時間從4745(ms) 降低為 950(ms)，整整少了3795(ms)且IO也大為降低，

如果你的查詢陳述句選擇性夠高的話，不要懷疑 Index Seek 操作絕對是首選。

參考

使用 FORCESEEK 資料表提示