[SQL Server]清理計畫快取中指定的執行計畫

這週客戶網站出現了一個效能問題,很快的調查發現是特定SQL查詢慢(10秒),剛好開始發生的時間點是每週例行的重建索引,本以為是統計值未更新到新的資料分佈,但資料庫有設定自動更新統計值,客戶重建索引後,也有執行更新統計值的語法。(抓頭..傷腦筋)

進一步從執行計畫觀察,估計的資料列數目與實際也沒有偏差的情形,最後只好和客戶開單一起在安控室重新串語法測試,沒想到速度卻是出奇的快,感覺像是查詢錯用了執行計畫,於是想試試把快取執行計畫清除,以前只會清全部,這次來試試清理指定的執行計畫。

 

(1)模擬查詢語法

USE AdventureWorks2014;
GO
SELECT * FROM Person.Address;
GO

 

(2)找到指定語法執行計畫的識別碼

SELECT
    plan_handle
    ,st.text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE text LIKE N'SELECT * FROM Person.Address%';
GO

 

(3)使用執行計畫識別碼從計畫快取清除

DBCC FREEPROCCACHE (0x060009005341261C609E145A0200000001000000000000000000000000000000000000000000000000000000);
GO

清理指定執行計畫後後,速度瞬間恢復正常,至於為何發生,是否和資料分佈統計或是Block仍有待調查(已報案)。

 


其他方式

從計畫快取清除所有計畫(慎重!!!)

DBCC FREEPROCCACHE WITH NO_INFOMSGS; 

 

清除與資源集區相關聯的所有快取項目

SELECT * FROM sys.dm_resource_governor_resource_pools; 
GO 
DBCC FREEPROCCACHE ('default'); 
GO 

 


參考:

DBCC FREEPROCCACHE (Transact-SQL)