[SQL] 統計資訊老舊

進行 TOP 10 高成本語法校正時，發現這段 T-SQL 語法有異常高的 Logical Read，特地把它抓出來了解一下。

SELECT 
	.......
FROM 人事考核 AS C 
    JOIN 人事考核明細 AS D ON 
    JOIN 考核項目 AS I ON .....
    JOIN 員工基本資料表 AS T1 ON C.檢核人員工編號 = T1.員工編號
    JOIN 員工基本資料表 AS T2 ON D.員工編號 = T2.員工編號
    JOIN 組織表 AS O ON T2.部門編號 = O.部門編號
WHERE LEFT(C.考核月份,3) = '101'

執行上述語法並搭配 SET STATISTICS IO NO 、SET STATISTICS TIME ON 和 包含實際執行計畫功能，可以抓出每個 Table Logical Read 和 Query Optimizer（簡稱 QO）產生執行計畫的過程。

Logical Read 分析

發現 [組織表]、[員工基本資料表] 和 [考核項目] 產生大量的 Logical Read。

SQL Server 剖析與編譯時間:
CPU 時間 = 0 ms，經過時間 = 94 ms。

(10300 個資料列受到影響)
資料表 '組織鰾'。掃描計數 0，邏輯讀取 20600，實體讀取 0，讀取前讀取 0。
資料表 '員工基本資料表'。掃描計數 0，邏輯讀取 20762，實體讀取 0，讀取前讀取 0。
資料表 '考核項目'。掃描計數 0，邏輯讀取 20600，實體讀取 0，讀取前讀取 0。
資料表 '人事考核明細'。掃描計數 81，邏輯讀取 614，實體讀取 0，讀取前讀取 0。
資料表 '人事考核'。掃描計數 1，邏輯讀取 41，實體讀取 0，讀取前讀取 0。

SQL Server 執行次數:
CPU 時間 = 93 ms，經過時間 = 313 ms。

執行計畫圖一

發現 [人事考核] 是利用 Index Scan 抓取資料，可能是因為 WHERE 條件內有下 LEFT() 轉換，造成 QO 無法正確使用 Index。

執行計畫圖二

檢視［執行計畫圖一］中標示數字 operator 明細（和［執行計畫圖二］標示數字相對應），發現 Estimated Rows（估計資料列）和 Actual Rows（實際資料列），有很大的差異，之所以會特別注意這三個明細，主因是 1、2 是高成本 operator，而 3 [人事考核明細] 是資料筆數最多的 Table（21744 筆）。

 

從 Estimated Rows 和 Actual Rows 差距過大這點推測，大概是因為統計資訊老舊導致 QO 沒有建立最佳執行計畫來執行，利用下述語法來查詢統計資訊更新時間。

SELECT 
    T.Name AS TableName , 
    S.Name AS StatName ,
    STATS_DATE(S.[object_id],S.stats_id) AS stats_update_date
FROM sys.tables AS T
    JOIN sys.stats AS S ON T.[object_id] = S.[object_id]
WHERE S.auto_created = 0
   AND T.Name IN ('員工基本資料表','人事考核','人事考核明細') -- 輸入要查詢的 TableName

從下圖查詢結果就可以看出 [員工基本資料表] 和 [人事考核] 這兩個 Table 的統計資訊都還是 2011-09-28，現在都已經是 2012-11-07。

更新統計資料

UPDATE STATISTICS 人事考核 WITH FULLSCAN
UPDATE STATISTICS 員工基本資料表 WITH FULLSCAN

更新後執行 T-SQL 的 Logical Read 分析

SQL Server 剖析與編譯時間:
CPU 時間 = 0 ms，經過時間 = 0 ms。

(10300 個資料列受到影響)
資料表 'Worktable'。掃描計數 0，邏輯讀取 0，實體讀取 0，讀取前讀取 0。
資料表 '人事考核明細'。掃描計數 1，邏輯讀取 153，實體讀取 0，讀取前讀取 0。
資料表 '考核項目'。掃描計數 1，邏輯讀取 2，實體讀取 0，讀取前讀取 0。
資料表 '人事考核'。掃描計數 1，邏輯讀取 41，實體讀取 0，讀取前讀取 0。
資料表 '員工基本資料表'。掃描計數 2，邏輯讀取 34，實體讀取 0，讀取前讀取 0。
資料表 '組織圖'。掃描計數 1，邏輯讀取 2，實體讀取 0，讀取前讀取 0。

SQL Server 執行次數:
CPU 時間 = 47 ms，經過時間 = 219 ms。

更新後執行 T-SQL 的執行計畫

自動更新統計資訊的觸發是有其條件，從本例來看 [人事考核] 和 [員工基本資料表] 這兩個 Table 的資料筆數分別是 147 和 201，根本無法觸發自動更新統計資訊，也因此導致 QO 使用錯誤統計資訊來產生執行計畫，難怪兩者是高成本的 operator，可見自動搭配手動更新統計資訊是有其必要性的；另從舊新執行計畫中可以發現，原本都是 Index Seek，更新後全部都變成 Index Scan，剛好可以打破 Index Seek 效能一定會比 Index Scan 好的迷思，本例的三個關鍵 Table 總資料量算是極少，但 T-SQL 要抓取大量資料（[人事考核明細] 一半資料量 10300 / 21744），因此 Index Scan 效能反而比 Index Seek 更好。

這個問題可以在 Client 端 SQL Server 重現，紀錄一下這次 Turning 經驗，雖然說新執行計畫看起來還不是很好（SSMS 還有建議的 missing index），實務上這 T-SQL 使用的機會，也是少得可憐，在先天不良（[人事考核明細] PK 是由 5 個 column 組成，實在是太寬）且後天失調（T-SQL 不符合 SARG 原則）的情況下算是不錯。