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

原文链接：http://click.aliyun.com/m/25022/
摘要： # 背景引入 执行计划缓存是SQL Server内存管理中非常重要的特性，这篇系列文章我们探讨执行计划缓存设计中遇到的single-used plans问题，以及如何发现、如何定性和定量分析single-used plans带来的影响，最后我们使用两种方法来解决这个问题。 # 什么是Single-used Plans 要解释清楚什么是Single-used Plans，首先需要解释SQL语句

背景引入
执行计划缓存是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
我的测试实例展示的查询结果如下：
01.png

执行计划缓存中有多少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)
我的测试实例执行结果如下截图：
02.png

从这个结果我们可以得出如下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
执行结果如下所示：
03.png

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

解决Single-used Plans问题
原文链接：http://click.aliyun.com/m/25022/