[SQL SERVER][Memo]篩選索引(2)
很久以前我有介紹過篩選索引，今年SQL PASS TW 3月份分享查詢調校不求人時也有大概提到一下，
總結來說:篩選索引可以節省硬碟空間、節省記憶體資源、節省CPU資源並降低索引維護成本，
進而提高查詢效能，不過水能載舟，亦能覆舟，誤用索引比不用索引來的更慘，
所以篩選索引要用到位，而不是到處濫用。
今天朋友問我一個查詢效能問題，他依照查詢語法建立相關正確索引，
但是查詢一些小資料時，效能卻沒有如預期來的快，
我之前在SQL PASS TW 3月份分享有提到，建立索引第一條件就是要考慮選擇性高低(越高越好)，
下面我簡單模擬該問題，並利用篩選索引來解決我這朋友的問題。
選擇性
可以看到id=11 選擇性非常低(共634筆)，但其他值選擇性相當高，
這時如果依欄位 id 建立非叢集索引將會影響整體統計值的估算，
當查詢小資料時，進而影響執行計畫(可能會有欺騙情況)，
但是我的系統需求就是要查詢ID=@ID 的資料，又不能不執行該SQL，
這時我會建議額外建立篩選索引(排除選擇性低的值)，
那麼查詢效能才會如你預期，下面我測試比較過後，你應該可以知道差異在那裏。
1.建立一般索引
create index idx1 on roles(id)
include(name)
select name
from roles
where id=11
select name
from roles
where id=12
統計資料。
執行計畫1:可以看到實際資料列數目和估計的資料列數目一樣，
這裡表示該執行計畫成本是OK。
執行計畫2:實際和估計的資料列數目相差快211倍，
導致該執行計畫整體成本和執行計畫1一模一樣(所以該執行計畫成本不可信)。
2.建立篩選索引(排除選擇性低的值)
create index idx2 on roles(id)
include(name)
where id<>11
select name
from roles
where id=11
select name
from roles
where id=12
邏輯讀取比使用一般索引少了2。
可以看到這次查詢ID=12不會被 idx1 索引統計值影響，
因為篩選索引過濾掉ID<>11，所以可以讓其他ID 高選擇性值得到更好的查詢效能，
又回到之前我提的，這樣的篩選索引結構整個搬到記憶體中，
也比一般非叢集索引來的更省。