[SQL Server] Plan Cache concepts - 認識 Plan Cache

Cache Stores

SQL Server 的 Plan Cache 由多個記憶體區塊所組成，可以由 sys.dm_os_memory_cache_counters 查詢得知。
但只有 4 個 記憶體區塊是包含 Query Plan，稱為：cache stores。

Object Plans (CACHESTORE_OBJCP)	Object Plans 包含：stored procedures、functions 與 triggers。
SQL Plans (CACHESTORE_SQLCP)	SQL Plans 包含：adhoc cached plans、autoparameterized plans 與 prepared plans。
Bound Trees (CACHESTORE_PHDR)	Bound Trees 是 SQL Server algebrizer 分析所產生的結構，用於 views、constraints 與 defaults。 查詢樹狀結構(query tree) 是由 剖析器(parser) 與 Algebrizer 所建構出來。
Extended Stored Procedures (CACHESTORE_XPROC)	Extended Procs (Xprocs) 是由 DLL 所定義的 系統預存程序，例如：sp_executesql、sp_tracecreate等。 在 Cache 結構裡，僅包含執行的函數名稱、 DLL 名稱。

---

SQL Plans 包含

• adhoc cached plans
• autoparameterized plans
• prepared plans
• SQL Server 6.5 之前版本，只有 Stored Procedure 有配置 Plan Cache。
• SQL Server 7.0 開始，Adhoc Query 與 Dynamic SQL Query 也有配置 Plan Cache。

---

-- 01_Buffer Pool Manager - Plan Cache, Data Cache, Buffer Pool

---

-- Memory Cache Counters: current usage of the cache objects

-- View each cached query execution plan

-- Views memory for the cache entries

---

範例程式碼

20171101_Plan Cache concepts

---

參考資料

Plan Cache Internals
https://msdn.microsoft.com/en-us/library/cc293624.aspx

Memory configuration and sizing considerations in SQL Server 2012 and later versions
https://support.microsoft.com/en-us/help/2663912/memory-configuration-and-sizing-considerations-in-sql-server-2012-and

Buffer Pool Extension and Resource Governor for IO
https://mva.microsoft.com/en-us/training-courses/mission-critical-performance-with-sql-server-2014-jump-start-8820?l=TEoTJZx2_7504984382

Plan Caching and Recompilation in SQL Server 2012
https://msdn.microsoft.com/en-us/library/dn148262.aspx

Plan Caching in SQL Server 2008
https://technet.microsoft.com/en-us/library/ee343986(v=sql.100).aspx

Plan cache and optimizing for adhoc workloads
https://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/

SQL Server Memory Pressure
http://fard-solutions.com/sql-server-memory-pressure/

A Guide to Query Processing for Memory-Optimized Tables
https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/a-guide-to-query-processing-for-memory-optimized-tables