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 'http://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执行计划缓存中蕴含大量有价值信息,从中统计查询语句编译性能消耗就是其中有价值信息之一。这篇文章提供了一种非常简单的方法来统计查询语句编译带来的各个性能指标开销。

时间: 2024-09-20 17:14:28

RDS SQL Server - 专题分享 - 巧用执行计划缓存之执行计划编译的相关文章

RDS SQL Server - 专题分享 - 巧用执行计划缓存之索引缺失

title: RDS SQL Server - 专题分享 - 巧用执行计划缓存之索引缺失 author: 风移 摘要 执行计划缓存是MSSQL Server内存管理十分重要的部分,同样如何巧用执行计划缓存来解决我们平时遇到的一系列问题也是一个值得深入研究的专题.这篇文章是如何巧用执行计划缓存的开篇,分享如何使用执行计划缓存来分析索引缺失(Missing Indexes). 问题引入 缺失索引是SQL Server CPU使用率居高不下的第一大杀手,也是SQL Server数据库非常大的潜在风险点

RDS SQL Server - 专题分享 - 巧用执行计划缓存之Single-used plans

背景引入 执行计划缓存是SQL Server内存管理中非常重要的特性,这篇系列文章我们探讨执行计划缓存设计中遇到的single-used plans问题,以及如何发现.如何定性和定量分析single-used plans带来的影响,最后我们使用两种方法来解决这个问题. 什么是Single-used Plans 要解释清楚什么是Single-used Plans,首先需要解释SQL语句执行计划缓存是什么?SQL Server执行每一条SQL语句之前,会从执行计划缓存内存中查看是否存在本条语句的执行

RDS SQL Server - 专题分享 - 巧用执行计划缓存之Table Scan

背景引入 执行计划中的Table Scan或者是Clustered Index Scan会导致非常低下的查询性能,尤其是对于大表或者超大表.执行计划缓存是SQL Server内存管理中非常重要的特性,这篇系列文章我们探讨如何从执行计划缓存的角度来发现RDS SQL数据库引擎中的Table Scan行为,以及与之相应SQL查询语句详细信息. 问题分析 其实,我们大家都知道,Table Scan或者Clustered Index Scan是关系型数据库查询性能很差的一种表扫描查询方式,如果在数据库引

RDS SQL Server - 专题分享 - 巧用执行计划缓存之数据类型隐式转换

摘要 SQL Server数据库基表数据类型隐式转换,会导致Index Scan或者Clustered Index Scan的问题,这篇文章分享如何巧用执行计划缓存来发现数据类型隐式转换的查询语句,从而可以有针对性的优化查询,解决高CPU使用率的问题. 问题引入 测试环境 为了更好的展示从执行计划缓存缓存中找出导致数据类型转化的查询语句,我们先建立测试环境. -- Create testing database IF DB_ID('TestDb') IS NULL CREATE DATABASE

RDS SQL Server - 专题分享 - 巧用执行计划缓存之Key Lookup

背景引入 执行计划缓存是SQL Server内存管理中非常重要的特性,这篇文章是巧用执行计划缓存系列文章之四,探讨什么是Key Lookup操作,如何从执行计划缓存中发现Key Lookup问题,以及如何解决这个问题. 什么是Key Lookup Key Lookup操作是指执行计划通过表的索引查找字段列的书签查找方式.Key Lookup发生在当查询语句使用Index Seek(或者Index Scan)的同时,又需要查找Index中没有完全包含的额外字段列,这时SQL Server必须回过头

RDS SQL Server - 最佳实践 - 高CPU使用率系列之索引缺失

摘要 CPU高使用率往往会导致SQL Server服务响应缓慢,查询超时,甚至服务挂起僵死,可以说CPU高使用率是数据库这种后台进程服务的第一大杀手.本系列文章之一的"索引缺失"就是CPU高使用率的最常见的原因之一. 问题引入 "鸟啊,我们平时在服务阿里云RDS SQL Server客户的过程中,遇到最多的一个问题就是,客户反馈RDS SQL Server数据库CPU使用率很高(有时超过90%,甚至到100%),导致查询缓慢甚至超时,这类问题要如何解决啊?".老鸟已

RDS SQL Server死锁(Deadlock)系列之二使用Profiler捕获死锁

问题引入 不管是RDS SQL Server还是自建SQL Server数据库,死锁的确是一个非常头疼的问题,上一篇文章我们已经谈到了使用DBCC捕获死锁.这篇文章是以阿里云RDS客户遇到的死锁问题为背景,分享死锁文章系列之二使用Profiler捕获死锁. Profiler捕获死锁 使用Profiler工具的Deadlock graph事件,可以非常方便直观的捕获死锁信息.方法是: 开启MSSQL Profiler:开始 -> 运行 -> 键入profiler 新建Deadlock Gra

RDS SQL Server死锁(Deadlock)系列之一使用DBCC捕获死锁

问题引入 在日常运维阿里云RDS SQL Server产品过程中,经常会被客户问道:"应用程序被死锁报错啦?影响很大,到底是哪个进程导致了死锁发生的啊?怎么解决啊?怎么办呀?".从客户一连串的问题中,我们深刻体会到了死锁问题的紧迫性和影响之大.授人予鱼而不如授人予渔,RDS SQL Server死锁系列文章就是为了帮助客人彻底解决死锁问题为初衷而诞生的.本篇文章是系列文章的开篇,主要是讨论如何使用DBCC来捕获死锁信息,内容包括: DBCC捕获死锁 死锁测试 死锁分析 解决方法

RDS SQL Server - 最佳实践 - 高CPU使用率系列之二索引碎片

摘要 上一篇文章分析了高CPU使用率的原因之一是索引缺失,接下来本系列文章之二的"索引碎片"是CPU高使用率的又一常见的原因.解决索引碎片问题是解决SQL Server服务响应缓慢,查询超时的又一利器. 问题引入 "鸟哥,我上一篇文章分享了因为索引缺失导致CPU高使用率的话题,反响不错.接下来,我打算分享索引碎片导致CPU高使用率的话题.",菜鸟主动找到老鸟汇报工作. 上一篇文章详情参见链接:RDS SQL Server - 最佳实践 - 高CPU使用率系列之索引缺