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

2021-08-25 23:29:18

背景引入

执行计划缓存是SQL Server内存管理中非常重要的特性，这篇系列文章我们探讨执行计划缓存设计中遇到的single-used plans问题，以及如何发现、如何定性和定量分析single-used plans带来的影响，最后我们使用两种方法来解决这个问题。

什么是Single-used Plans

要解释清楚什么是Single-used Plans，首先需要解释SQL语句执行计划缓存是什么？SQL Server执行每一条SQL语句之前，会从执行计划缓存内存中查看是否存在本条语句的执行计划，如果不存在，会将其编译、选择最优路径、生成执行计划，然后会将执行计划存储在一块专门的内存区域中（这块内存叫着执行计划缓存），以供下次该语句执行直接从执行计划缓存中获取编译完毕的执行计划。以此来节约数据库系统对于查询语句编译、生成执行计划过程的性能开销，提高SQL语句的执行效率。
而Single-used Plans是指那些第一次执行后被缓存起来的执行计划，而后再也没有被重复利用过的执行计划缓存。其中ad hoc query（即席查询）就是典型的single-used plans中的一种。

如何发现Single-used Plans

从上一节我们可以大致知道，single-used plans仅会第一次被使用（从名字也可以很好理解到这一点），所以，实际上single-used plans是对SQL Server内存空间和CPU资源的浪费，对数据库性能有一定的损害。那么，我们如何来发现single-used plans呢？我们可以使用下面的查询语句：

USE master
GO

SELECT
    database_name = QUOTENAME(db.name),
    st.text,
    cp.objtype,
    cp.size_in_bytes,
    qp.query_plan,
    cp.cacheobjtype
FROM sys.dm_exec_cached_plans AS cp 
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
    INNER JOIN sys.databases AS db WITH(NOLOCK)
    ON st.dbid = db.database_id
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
WHERE cp.cacheobjtype = N'Compiled Plan'
AND cp.objtype IN (N'Adhoc', N'Prepared')
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC

我的测试实例展示的查询结果如下：

执行计划缓存中有多少Single-used Plans

当我们可以有效的发现single-used plans以后，我们可能又会问：到底我的SQL Server数据库实例中，有多少执行计划缓存是属于single-used plans类型呢？可以从两个维度来分析：
Single-used plans记录总数
Single-used plans总的执行计划占用的内存空间大小
可以使用以下的查询语句来回答这个问题。

USE master
GO
SELECT
    COUNT(1) AS [total_plans], 
    CAST(SUM(size_in_bytes)/1024. AS DECIMAL(18,2)) AS [total_size_in_kb], 
    CAST(SUM((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END))/1024. AS DECIMAL(12,2)) AS [total_size_single_used_in_kb], 
    SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [total_plans_count_single_used],
    AVG(usecounts) AS [avg_used_counts], 
    CAST(AVG(size_in_bytes)/1024. AS DECIMAL(12, 2)) AS [avg_used_size_kb]
FROM sys.dm_exec_cached_plans WITH(NOLOCK)

我的测试实例执行结果如下截图：

从这个结果我们可以得出如下single-used plans的数据：总的single-used plans记录数为73，内存空间占用大小为13912 KB。

Single-used Plans对性能的影响

就单单一条Single-used Plan来看的话，对数据库系统的性能影响很小，小到可以忽略的地步，所以，数据库执行计划缓存中，存在少量的Single-used Plans是很正常的，可以不用太关注。但是，如果Single-used Plans大量存在的话，可能就会对系统带来比较严重的性能影响。

定性分析

定性分析大量Single-used Plans对数据性能的影响，主要体现在以下几个方面：
总的执行计划缓存利用率不高：因为存在大量不能被重复利用的执行计划缓存，从而拉低了执行计划缓存整体利用率
浪费执行计划缓存中内存的开销：每一条执行计划缓存或多或少会占用一定的执行计划缓存内存空间，大量的Single-used Plans导致了内存空间的浪费
导致CPU使用率的上升：每一条SQL语句执行计划的编译、最优路径选择和执行计划缓存，这些过程都需要消耗系统CPU资源，如果大量存在Single-used Plans，会导致系统CPU使用率的上升。
举一个最为极端的例子，假设执行计划缓存中存储的所有执行计划都是Single-used Plans的话，那么导致的严重后果是：
执行计划缓存利用率就是0%，因为没有任何的执行计划被重用
执行计划缓存这种设计就毫无意义，因为缓存起来也没有被重用
浪费执行计划缓存的内存开销和CPU开销

定量分析

以上是定性分析Single-used Plans对系统性能的影响，那么到底Single-used Plans达到哪个数量级，占比多少的时候，我们需要密切关注呢？虽然微软没有官方的推荐数字，但是个人比较推荐的两个数字是2GB和50%，即：所有的Single-used Plans使用的内存空间超过2GB或者内存空间使用占比超过50%。当然最终也可能取决于SQL Server可以使用的最大内存数量。

USE master
GO

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
    DROP TABLE #temp

SELECT 
    objtype AS [cachetype], 
    COUNT(1) AS [total_plans], 
    CAST(SUM(size_in_bytes)/1024. AS DECIMAL(18,2)) AS [total_size_in_kb], 
    CAST(SUM((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END))/1024. AS DECIMAL(12,2)) AS [total_size_single_used_in_kb], 
    SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [total_plans_count_single_used],
    AVG(usecounts) AS [avg_used_counts], 
    CAST(AVG(size_in_bytes)/1024. AS DECIMAL(12, 2)) AS [avg_used_size_kb]
INTO #temp
FROM sys.dm_exec_cached_plans WITH(NOLOCK)
GROUP BY objtype


SELECT 
    [cachetype],
    [total_plans],
    [total_size_in_kb],
    [total_plans_count_single_used],
    [total_size_single_used_in_kb],
    CAST(([total_plans_count_single_used]*1.0 / [total_plans]*1.0) * 100. AS DECIMAL(12, 2)) AS [single_used_plan_count%],
    CAST([total_size_single_used_in_kb]/[total_size_in_kb] * 100. AS DECIMAL(12, 2)) AS [single_used_size%],
    [avg_used_counts],
    [avg_used_size_kb]
FROM #temp
ORDER BY [total_size_single_used_in_kb] DESC


SELECT 
    SUM([total_plans]) AS total_plan_counts,
    SUM([total_size_in_kb]) AS total_plan_size_in_kb,
    SUM([total_plans_count_single_used]) AS [total_plans_counts_single_used],
    SUM([total_size_single_used_in_kb]) AS [total_plan_size_single_used_in_kb],
    CAST(SUM([total_plans_count_single_used]) * 100. / SUM([total_plans]) AS DECIMAL(12, 2)) AS [plan_counts_single_used%],
    CAST(SUM([total_size_single_used_in_kb]) * 100. / SUM([total_size_in_kb]) AS DECIMAL(12, 2)) AS [plan_size_single_used%]
FROM #temp

执行结果如下所示：

从这个执行结果来看，在我的SQL Server测试实例上，single-used plans占用的执行计划记录数为72条，内存空间占用14016 KB；single-used plans执行计划记录数占总的百分比为39.78%，内存空间占用比例为50.59%。

解决Single-used Plans问题

从定量分析小结，发现我的测试实例，single-used plans占用执行计划缓存内存比例已经超过50%了，是解决single-used plans问题的时候到了。

清空Single-used Plans

第一种方法是手动清理single-used plans，当然，你也可以很暴力的将所有执行计划缓存清空，从而也就清理了single-used plans，但是这样会误杀很多有用的无辜的执行计划缓存，从而导致这些执行计划重编译，影响性能。我们推荐使用下面的方法，仅清空哪些single-used plans，方法如下：

USE master
GO

DECLARE
    @plan_handle varbinary(64)
;

DECLARE
     cur_single_used_plan_handle CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
SELECT cp.plan_handle
FROM sys.dm_exec_cached_plans AS cp WITH(NOLOCK)
WHERE usecounts = 1

OPEN cur_single_used_plan_handle
FETCH NEXT FROM cur_single_used_plan_handle INTO @plan_handle
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @plan_handle

    DBCC FREEPROCCACHE (@plan_handle); 

    FETCH NEXT FROM cur_single_used_plan_handle INTO @plan_handle
END

CLOSE cur_single_used_plan_handle
DEALLOCATE cur_single_used_plan_handle
GO

optimize for ad hoc workloads配置选项

除了手动清理single-used plans执行计划缓存外，另外一个更强大的功能是修改SQL Server 实例级别的配置选项optimize for ad hoc workloads。为了解决single-used plans带来的问题，微软从SQL Server 2008开始引入了这个全新选项，当打开这个选项以后，一个ad hoc的查询语句在第一次执行的时候，系统会创建一个“compiled plan stub”，并不会将执行计划缓存起来，只有当第二次该语句再被执行的时候，执行计划才会被缓存在内存中，从而避免了single-used plans带来的问题。配置这个选项的方法很简单，参见以下语句：

EXEC sys.sp_configure 'show advanced options', 1
RECONFIGURE
GO

EXEC sys.sp_configure 'optimize for ad hoc workloads', 1
RECONFIGURE
GO

最后总结

这篇文章讨论了执行计划缓存中的single-used plans带来的问题，如何发现，如何去定量和定性分析，以及最后怎么解决single-used plans的问题。

码农公寓