文档章节

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

 爱哭的小魔女
发布于 2017/07/05 15:19
字数 1896
阅读 0
收藏 0

原文链接

 

摘要: # 背景引入 执行计划缓存是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问题

从定量分析小结,发现我的测试实例,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

 

原文链接

本文转载自:

粉丝 0
博文 105
码字总数 7329
作品 0
私信 提问
MySQL · 特性分析 · 执行计划缓存设计与实现

Plan Cache背景知识 一条SQL语句输入到MySQL服务器后,一般要经历:词法语法解析(parse),优化(optimize),生成执行计划(plan)和执行(execute)的过程。词法语法分析,优化以及生成执行计划...

阿里云RDS-数据库内核组
2016/09/05
0
0
最佳实践 · 实例级别数据库上云RDS SQL Server

摘要 到目前,我们完成了SQL Server备份还原专题系列八篇月报分享:三种常见的数据库备份、备份策略的制定、查找备份链、数据库的三种恢复模式与备份之间的关系、利用文件组实现冷热数据隔离...

阿里云云栖社区
2018/08/13
22
0
阿里云RDS SQL自动化迁移上云的一种解决方案

摘要 至今为止我们完成了SQL Server备份还原专题系列六篇月报分享:三种常见的数据库备份、备份策略的制定、查找备份链、数据库的三种恢复模式与备份之间的关系、利用文件组实现冷热数据隔离...

风移
2018/06/19
0
0
使用Ado.net执行SP很慢,而用SSMS执行很快

今天遇到一个问题,有用户反应,在site上打开报表,一直loading,出不来结果。 遇到这种问题,我立刻simulate用户使用Filter Condition,问题repro,看来不是偶然事件,通过SQL Server Prof...

长征6号
2015/10/14
0
0
MSSQL · 最佳实践 · RDS SDK实现数据库迁移上阿里云RDS SQL Server

title: MSSQL · 最佳实践 · RDS SDK实现数据库迁移上阿里云RDS SQL Server author: 风移 摘要 至今,我们完成了SQL Server备份还原专题系列七篇月报分享:三种常见的数据库备份、备份策略的...

风移
2018/07/18
0
0

没有更多内容

加载失败,请刷新页面

加载更多

OSChina 周六乱弹 —— 早上儿子问我他是怎么来的

Osc乱弹歌单(2019)请戳(这里) 【今日歌曲】 @凉小生 :#今日歌曲推荐# 少点戾气,愿你和这个世界温柔以待。中岛美嘉的单曲《僕が死のうと思ったのは (曾经我也想过一了百了)》 《僕が死の...

小小编辑
38分钟前
42
0
Excption与Error包结构,OOM 你遇到过哪些情况,SOF 你遇到过哪些情况

Throwable 是 Java 中所有错误与异常的超类,Throwable 包含两个子类,Error 与 Exception 。用于指示发生了异常情况。 Java 抛出的 Throwable 可以分成三种类型。 被检查异常(checked Exc...

Garphy
今天
9
0
计算机实现原理专题--二进制减法器(二)

在计算机实现原理专题--二进制减法器(一)中说明了基本原理,现准备说明如何来实现。 首先第一步255-b运算相当于对b进行按位取反,因此可将8个非门组成如下图的形式: 由于每次做减法时,我...

FAT_mt
昨天
6
0
好程序员大数据学习路线分享函数+map映射+元祖

好程序员大数据学习路线分享函数+map映射+元祖,大数据各个平台上的语言实现 hadoop 由java实现,2003年至今,三大块:数据处理,数据存储,数据计算 存储: hbase --> 数据成表 处理: hive --> 数...

好程序员官方
昨天
7
0
tabel 中含有复选框的列 数据理解

1、el-ui中实现某一列为复选框 实现多选非常简单: 手动添加一个el-table-column,设type属性为selction即可; 2、@selection-change事件:选项发生勾选状态变化时触发该事件 <el-table @sel...

everthing
昨天
6
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部