DBCC FREEPROCCACHE - Remove the specific plan from the cache - 由 Plan Cache 中移除指定的 Execution Plan

DBCC FREEPROCCACHE 用於移除 Plan Cache 的項目，可用的方式有：

1. 移除全部的 cache
2. 移除指定的 plan_handle
3. 移除指定的 sql_handle
4. 移除指定的 pool_name - Resource Governor resource pool

副作用：

• 移除 Plan Cache 後，因無可重複使用的 Execution Plan，導致要重新編譯 Execution Plan，耗費系統資源。

-- Remove the specific plan from the cache - 由 Plan Cache 中移除指定的 Execution Plan

---

DBCC FREEPROCCACHE - Remove the specific plan from the cache - 由 Plan Cache 中移除指定的 Execution Plan


01. 在資料庫 Northwind，執行 4 段 SQL 查詢。


-- 021_Run Ad hoc Query

02. 若要移除 編號 8 的 Execution Plan:

• Obj Type: Adhoc
• T-SQL: SELECT * FROM Northwind.dbo.Products WHERE ProductID =2
• plan_handle: 0x06000100F8BDCD2780E2D1F3D001000001000000000000000000000000000000000000000000000000000000

-- 022_View_Plan_Cache

03. 執行 DBCC FREEPROCCACHE，並加入指定的 plan_handle。

• 就可以由 Plan Cache 中移除指定的 Execution Plan

-- 031_DBCC FREEPROCCACHE - Remove the specific plan from the cache

04. 觀察: Plan Cache:

• 確認已經移除 編號 8 的 Execution Plan.

05. 若要移除 編號 1 的 Execution Plan:

• Obj Type: Prepared
• (@1 tinyint)SELECT * FROM [Northwind].[dbo].[Products] WHERE [ProductID]=@1
• plan_handle: 0x0600010072761B2DE0E8D1F3D001000001000000000000000000000000000000000000000000000000000000

-- 032_View_Plan_Cache

06. 執行 DBCC FREEPROCCACHE，並加入指定的 plan_handle。

• 就可以由 Plan Cache 中移除指定的 Execution Plan

-- 041_DBCC FREEPROCCACHE - Remove the specific plan from the cache

04. 觀察: Plan Cache:

• 確認已經移除指定的 Execution Plan.

-- 042_View_Plan_Cache

---

DBCC FREEPROCCACHE - Clearing all plans from the plan cache - 清除全部的 Plan Cache

01. 觀察 Plan Cache

-- 001_Plan_Cache

02. 執行 DBCC FREEPROCCACHE，移除全部的 Plan Cache

-- 011_DBCC FREEPROCCACHE_All

03. 若不想看到 DBCC FREEPROCCACHE 執行回傳結果，可以搭配參數:

• WITH NO_INFOMSGS

-- 012_DBCC FREEPROCCACHE_NO_INFOMSGS

04. 觀察 Plan Cache: 已移除全部的 Plan Cache

-- 002_Plan_Cache_Clean

---

範例程式碼

20171122_DBCC FREEPROCCACHE
https://drive.google.com/drive/folders/1LarqPyGXq6Istw-eResJOlvvHo_TyHLC?usp=sharing

---

移除 Plan Cache

移除 Plan Cache 的方式有：

1. DBCC FREEPROCCACHE 
2. access check cache bucket count
3. access check cache quota
4. clr enabled
5. cost threshold for parallelism
6. cross db ownership chaining
7. index create memory
8. max degree of parallelism
9. max server memory
10. max text repl size
11. max worker threads
12. min memory per query
13. min server memory
14. query governor cost limit
15. query wait
16. remote query timeout
17. user options
18. ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE - SQL Server 2016

---

注意事項

• DBCC FREEPROCCACHE 不會清除原生編譯預存程序(供 memory-optimized tables 使用)的執行統計資料。 
• 程序快取沒有包含原生編譯預存程序的相關資訊。

---

參考資料

Eight Different Ways to Clear the SQL Server Plan Cache
https://www.sqlskills.com/blogs/glenn/eight-different-ways-to-clear-the-sql-server-plan-cache/

SQLskills 101: The Other Bad Thing About Clearing Procedure Cache
https://www.sqlskills.com/blogs/erin/sqlskills-101-the-other-bad-thing-about-clearing-procedure-cache/

DBCC FREEPROCCACHE
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-freeproccache-transact-sql