搜尋本站文章

2017-11-01

[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

沒有留言:

張貼留言