[SQL SERVER][Memo]認識快取儲存
SQL Server中的計畫快取區是由4種獨立記憶體區域組成，即是快取儲存(Cache Stores)，
我們可以透過 sys.dm_os_memory_cache_counters 的DMV，
查看執行階段各種快取項目記憶體使用情形。
1.Object Plans (CACHESTORE_OBJCP)
包含 stored procedures, functions, and triggers
2.SQL Plans (CACHESTORE_SQLCP)
包含 adhoc cached plans, autoparameterized plans, and prepared plans
3.Bound Trees (CACHESTORE_PHDR)
Bound Trees是由SQL Server的Algebrizer分析結構語句後所得，用於 views, constraints, and defaults。
4.Extended Stored Procedures (CACHESTORE_XPROC)
預設系統的Stored Procedures ，如 sp_executeSql and sp_tracecreate..等。
定義為使用DLL而不是使用TSQL陳述語句。
快取結構僅包含所執行的函數和DLL名稱。
查詢4種快取項目記憶體使用情形
select name,type,single_pages_kb,multi_pages_kb,entries_count
from sys.dm_os_memory_cache_counters
where type in ('CACHESTORE_SQLCP', 'CACHESTORE_OBJCP', 'CACHESTORE_PHDR', 'CACHESTORE_XPROC')
order by single_pages_kb desc
查詢使用頻率最高的陳述句
SELECT st.text, cp.plan_handle, cp.usecounts, cp.size_in_bytes,
cp.cacheobjtype, cp.objtype
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
ORDER BY cp.usecounts DESC 查詢前10個目前連接最耗時陳述句
SELECT TOP 10 SUBSTRING(text, (statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1
THEN DATALENGTH(text)
ELSE statement_end_offset
END - statement_start_offset)/2) + 1) AS query_text, *
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY total_elapsed_time DESC
查詢前10個CPU最耗時陳述句
SELECT TOP 10 SUBSTRING(text, (statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1
THEN DATALENGTH(text)
ELSE statement_end_offset
END - statement_start_offset)/2) + 1) AS query_text, *
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
ORDER BY total_elapsed_time/execution_count DESC;
查詢快取相關成本
SELECT text, objtype, refcounts, usecounts, size_in_bytes,
disk_ios_count, context_switches_count,
pages_allocated_count, original_cost, current_cost
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
JOIN sys.dm_os_memory_cache_entries e
ON p.memory_object_address = e.memory_object_address
WHERE cacheobjtype = 'Compiled Plan'
AND type in ('CACHESTORE_SQLCP', 'CACHESTORE_OBJCP')
--and text='select * from AP_LOG'
ORDER BY objtype desc, usecounts DESC;
參考