278
RDS SQL Server - 專題分享 - 巧用執行計劃緩存之執行計劃編譯
背景引入
執行計劃緩存是SQL Server內存管理中非常重要的特性，這篇文章是巧用執行計劃緩存係列文章之五，探討如何從執行計劃緩存中獲取查詢語句執行計劃編譯的性能消耗，比如：
編譯時間消耗
編譯CPU消耗
編譯內存消耗
緩存大小消耗
等等一係列非常有價值的統計信息。
什麼是執行計劃編譯
SQL查詢語句在提交到SQL Server主機服務之後，數據查詢訪問動作發生之前，SQL Server的編譯器需要將查詢語句進行編譯，然後查詢優化器生成最優執行計劃。而這個編譯和最優執行計劃選擇的過程，往往比較消耗係統性能，因此，SQL Server會將最優的執行計劃存儲在執行計劃緩存中，以供將來類似的查詢語句（相同的語句或者已經參數化的查詢）直接從內存中獲取執行計劃，而避免重新編譯，以此來節約係統性能開銷，提高查詢語句執行效率。
詳情參加如下圖所示：
備注：
圖片來自於SQL Server架構----查詢的生命周期（上）。
執行計劃編譯消耗統計
解釋了什麼是執行計劃編譯，以及明白了查詢語句編譯過程比較消耗性能，那麼我們如何定量的分析查詢語句對性能的消耗呢？比如：
查詢語句對編譯時間的開銷
查詢語句對CPU開銷
查詢語句編譯過程的內存開銷
查詢語句對執行計劃緩存占用大小
要得到這些統計信息，我們完全可以通過分析執行計劃緩存來得到，詳情參見如下代碼。這段代碼可以獲取前麵我們提到的所有性能指標，甚至更多，我們可以修改默認的排序字段來獲取不同性能指標的TOP查詢語句，以及相應的性能開銷。
use master
GO
SET NOCOUNT ON
IF OBJECT_ID('tempdb..#temp', 'U') IS NOT NULL
DROP TABLE #temp
DECLARE
@sql NVARCHAR(MAX)
,@orderCol SYSNAME
,@TOPN INT
;
SELECT
@sql = N'SELECT TOP(@TOPN) * FROM #temp ORDER BY '
,@TOPN = 20
,@orderCol = '' -- by default CPU: cpu/memory/duration/cachesize/
;WITH XMLNAMESPACES (DEFAULT 'https://schemas.microsoft.com/sqlserver/2004/07/showplan'),
DataInfo AS (
SELECT
T.c.value('xs:hexBinary(substring((@QueryHash)[1],3))', 'varbinary(max)') AS QueryHash,
T.c.value('xs:hexBinary(substring((@QueryPlanHash)[1],3))', 'varbinary(max)') AS QueryPlanHash,
T.c.value('(QueryPlan/@CachedPlanSize)[1]', 'int') AS CachedPlanSize_KB,
T.c.value('(QueryPlan/@CompileTime)[1]', 'int') AS CompileTime_ms,
T.c.value('(QueryPlan/@CompileCPU)[1]', 'int') AS CompileCPU_ms,
T.c.value('(QueryPlan/@CompileMemory)[1]', 'int') AS CompileMemory_KB,
qp.query_plan
FROM sys.dm_exec_cached_plans AS cp WITH(NOLOCK)
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY qp.query_plan.nodes('ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS T(c)
)
SELECT
CompileTime_ms,
CompileCPU_ms,
CompileMemory_KB,
CachedPlanSize_KB,
qs.execution_count,
CAST(qs.total_elapsed_time*1.0/1000 AS decimal(12,2))AS duration_ms,
CAST(qs.total_worker_time*1.0/1000 AS decimal(12,2)) as cputime_ms,
CAST((qs.total_elapsed_time*1.0/qs.execution_count)/1000 AS decimal(12,2)) AS avg_duration_ms,
CAST((qs.total_worker_time*1.0/qs.execution_count)/1000 AS decimal(12,2)) AS avg_cputime_ms,
CAST(qs.max_elapsed_time*1.0/1000 AS decimal(12,2)) AS max_duration_ms,
CAST(qs.max_worker_time*1.0/1000 AS decimal(12,2)) AS max_cputime_ms,
SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
(CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset) / 2 + 1) AS StmtText,
query_hash,
query_plan_hash
INTO #temp
FROM DataInfo AS tab
INNER JOIN sys.dm_exec_query_stats AS qs WITH(NOLOCK)
ON tab.QueryHash = qs.query_hash
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
SET
@sql = @sql +
CASE @orderCol
WHEN 'cpu' THEN ' CompileCPU_ms DESC'
WHEN 'memory' THEN ' CompileMemory_KB DESC'
WHEN 'duration' THEN ' CompileTime_ms DESC'
WHEN 'cachesize' THEN ' CachedPlanSize_KB DESC'
ELSE ' CompileCPU_ms DESC'
END
;
EXEC sys.sp_executesql @sql
,N'@TOPN INT'
,@TOPN = @TOPN;
以上查詢是獲取查詢語句編譯對CPU消耗最多的TOP 20查詢語句，以及相關的性能指標。如下截圖：
把前四個字段數據繪製成一張圖表，如下所示：
最後總結
SQL Server執行計劃緩存中蘊含大量有價值信息，從中統計查詢語句編譯性能消耗就是其中有價值信息之一。這篇文章提供了一種非常簡單的方法來統計查詢語句編譯帶來的各個性能指標開銷。
最後更新：2017-08-13 22:33:06