不同欄位順序的index差異?

最近同事一直在做SQL Server 2000的效能測試...看他很HIGH的樣子, 我一直在想 "代誌是不是搵想A卡哩啊簡單" ?.

所以我也花了時間,來看看他給我的測試
這個測試是有關index 欄位順序調整或合併, 是否有助於效能提昇
他給的測試方法是觀察Logical Reads 和Physical Reads
由這二個值的觀察,可洐生出了SQL Server:Buffer Manager - Buffer Cache Hit Ratio
按照他給的公式定義 SQL Server:Buffer Manager - Buffer Cache Hit Ratio= logical reads – physical reads)/logical read * 100%
所以數值愈大,代表資料直接由data buffer 也就是記憶體的比例愈大, 相對減少對DISK 的讀取.

問題是什麼情況下, Buffer Cache Hit Ratio 會變大?
要嗎是Logical Reads變大, 要嗎是Physical Reads變小.
但如果以同一個查詢語法來看,在不清除buffer的情況下, 理論上 Logical Reads應該是要相同才對, 而且Physical Reads應該在第一次查詢時, 會變成0. 當然, 假設的前提是, 只有一個測試連線, 以上結論才會成立吧.而且,這個counter也需在一段時間的記錄後才能取得到較正確的值, 所以在測試階段,只先針對一個使用者一個相同的SQL來測試, 這個數值一點幫助也沒有.

google了一些資訊,還是做了一下測試, 在只有我一個使用者的情況下...
1.先設定SET STATISTICS IO ON 及SET STATISTICS TIME ON, 以返回每個查詢的統計資料和執行時間
2.每個查詢前,皆先清除buffer , 包含dirty buffer也須清除(可看MSDN說明)
CHECKPOINTGo
DBCC DROPCLEANBUFFERS
Go
DBCC FREEPROCCACHE
Go

依不同的index建立進行測試,分別select 所有欄位及部份欄位來查看Logical Reads 和Physical Reads

為什麼要重建index並整合index?
原本有二個index, 其中一組是由7個欄位組成的index, 另一組則是3個欄位組成的index.
index1 = (col1, col2, col3,col4,col5,col6,col7)
index2 = (col7, col1, col3 )
當初建立這二個index的用意在於, 第一組為unique index, 第二組則為常用的insert/delete/select條件. 當時是覺得(只是自已理論上的推測,也沒有經過實測和實證....)建立index時的欄位順序應該會差別,所以就建了二組
同事測試後,覺得應該只要建一組就好, 所以最後將index改為index3 (col7, col1,col3, col2, col3,col4,col5,col6,col7) 想要看看有沒有改善.

所以, 這次的重點就在測試index調整後到底有沒有改善 Logical Reads了
因為相同的查詢,所傳回的Logical Reads應該要相同, 所以在改變了index後,觀察Logical Reads是否有降低應該就可以知道改善的程度.
測試SQL除返回欄位數不一外,其WHERE條件皆相同, 皆使用到 col7, col1, col3
不同的返回欄位數也有差, 端看其是否符合covering index的使用,可參考這篇文章Tips on Optimizing Covering Indexes

結果如下:
返回所有欄位時,
index3:
資料表 'xxxx_tab'。掃描次數 2，邏輯讀入 7842，實體讀取 3，先讀讀入 4252。
伺服器執行次數: CPU時間 = 375 ms，經過時間 = 109339 ms。

index1+index2
資料表 'xxxx_tab'。掃描次數 2，邏輯讀入 7804，實體讀取 3，先讀讀入 4258。
伺服器執行次數: CPU時間 = 422 ms，經過時間 = 109294 ms。

返回部份較少欄位(含index 部份欄位col1 + 無index的欄位)
index3
資料表 'xxxx_tab'。掃描次數 2，邏輯讀入 7842，實體讀取 2，先讀讀入 4296。
伺服器執行次數: CPU時間 = 172 ms，經過時間 = 8443 ms。

index1+index2
資料表 'xxxx_tab'。掃描次數 2，邏輯讀入 7804，實體讀取 3，先讀讀入 4258。
伺服器執行次數: CPU時間 = 219 ms，經過時間 = 3570 ms。

返回部份較多欄位(含index 部份欄位col7, col1,col3+ 無index的欄位)
index3:
資料表 'xxxx_tab'。掃描次數 2，邏輯讀入 7842，實體讀取 2，先讀讀入 4296。
伺服器執行次數: CPU時間 = 203 ms，經過時間 = 5573 ms。

index1+index2
資料表 'xxxx_tab'。掃描次數 2，邏輯讀入 7804，實體讀取 3，先讀讀入 4258。
伺服器執行次數: CPU時間 = 219 ms，經過時間 = 3570 ms。

很粗糙的測試,得到的結論是, 在記憶體配置不變的情況下,合併成一個index 會讓Logical Reads變大,Physical Reads變小....所以合併成一個index應是有幫助的
哈哈...看錯記錄, 合併成一個index會讓Logical Reads變大


最後個人的感想是,...這樣的測試資訊,十分不足,很片面, 所以tuning這款代誌金正不是笨人想A卡里啊簡單
因為我們忽略了許多因素, 光是自已一個人做測試時, 就有很多的假設前提, 更何況是尖峰刻,user可是殺紅了眼在搶resource啊.

效能調校真的很花時間, 沒有意志力就甭做DBA了, 拿來做飯後閒餘時的興趣研究,真的可以打發不少時間..