SQL Server Nuts 堅果的堅持 Day4 (Efficient Queries)
2022-10-11
SQL Server 堅果的堅持學習筆記系列，不中斷的連續學習，深入 SQL Server 核心知識 🥜
說明
無效率的查詢 (Inefficient queries) 會影響 SQL Server 的整體效能與磁碟運用，即使有充足的記憶體用以快取資料，無效的查詢仍會造成 CPU 資源耗用以及增加 Blocking 的發生。
可以藉由 sys.dm_exec_query_stats view 分析快取的執行計畫，以及取得下列資料庫物件的統計資訊，例如 sys.dm_exec_procedure_stats、sys.dm_exec_trigger_stats 及 sys.dm_exec_function_stats。然而需要注意的是統計資訊包含的不會是完整的資料，例如 runtime execution metrics in execution plans 及未被快取的查詢。
而除了 DMV 外，可以主動採用 Extended Events 及 SQL Traces 來分析，但相對於 DMV 會對 SQL Server 造成額外效能成本與影響。
而藉由 Query Store，雖然會增加部分的效能成本 (overhead)，但它不仰賴快取的執行計畫做分析，並且可以找到執行計畫執行效果衰退的問題 (Plan Regresssion)，在多數情境下是非常適合的選擇。
Plan cache–based execution statistics
使用執行計畫的快取以及統計資訊，會少計入未被快取的查詢，但它的好處在於不需要額外的設定，就可以有的現成分析。
sys.dm_exec_query_stats
快取的執行計畫，不包含實際的執行資訊，因此執行計畫相當於估計執行計畫 (estimated execution plans)。
在 SQL Server 2019 後，這個問題可以藉由啟動 the last actual execution plan for the statement 來解決，但資料庫的相容性層級必須為 150 以上，且必須主動啟用 LAST_QUERY_PLAN_STATS。
啟用後可以藉由查詢 sys.dm_exec_query_plan_stats 得到最後的實際執行計畫。
Limitations
使用執行計畫快取分析，最重要的是如果資料沒有被執行計畫快取，就無法被分析 🤨 因此可能錯過某些查詢，一般而言這個問題不大，但有時候對於這些被遺漏在計畫快取的查詢，進行優化調整對系統或者是資料庫有幫助。
此外如果是對於偶然使用的查詢、參數變化型的查詢 (ad-hoc queries)，搭配 statement-level recompile 是不會被執行計畫所快取；預存程序搭配 RECOMPILE 也不會被快取，必須使用 Query Store 或 Extended Events 來捕獲上述的查詢，進行分析。
由於執行計畫的快取是有時間性，因此若從不同的時間維度而言會有不同的分析結果，從外若是從平均值或是總值的方式，也會有不同的分析意涵。
在分析的前後，可以藉由藉由 creation_time 以及 last_execution_time 欄位，其所代表執行計畫被建立與執行的時間，搭配總值與平均值的分析，來驗證優化查詢語法的成果。
使用執行計畫快取分析最複雜的問題是，對於相同的查詢可能產生多種不同的結果，這可能源自於參數變化型的查詢 (ad-hoc queries)，或者連線端有著不同的設定 (SET Settings) ，此外在 SQL Server 2022 (資料庫相容性層級 160)，parameter-sensitive plan optimization 功能也可能造成這個問題。而處理的方式是透過 query_hash 以及 query_plan_hash 欄位，識別相同的執行計畫，來將統計值加總。
sys.dm_exec_procedure_stats
you can get execution statistics for stored procedures through the sys.dm_exec_procedure_stats view.
Extended Events
SQL Traces
Query Store
Best Practice Checklist
從實用的 DMV 找到效能問題，尤其是不當查詢所造成的效能問題。
從 sys.dm_exec_query_stats 判斷無效率的查詢。
從 sys.dm_exec_procedure_stats 判斷高成本的預存程序。
啟用 Query Store 來分析與蒐集查詢。
啟用 Trace flags T7745 及 7752 用以優化 SQL Server 在使用 Query Store 上 啟用與停止時的效能。
細說優化設定
參考資料
SQL Server Advanced Troubleshooting and Performance Tuning: Best Practices and Techniques