SQL Server性能调优之缓存

在执行任何查询时，SQL Server都会将数据读取到内存，数据使用之后，不会立即释放，而是会缓存在内存Buffer中，当再次执行相同的查询时，如果所需数据全部缓存在内存中，那么SQL Server不会产生Disk IO操作，立即返回查询结果，这是SQL Server的性能优化机制。

一，主要的内存消费者（Memory Consumer)

1，数据缓存（Data Cache）

Data Cache是存储数据页（Data Page）的缓冲区，当SQL Server需要读取数据文件（File）中的数据页（Data Page）时，SQL Server会把整个Page都调入内存（内存中的一个Page叫做buffer），Page是数据访问的最小单元。

当用户修改了某个Page上的数据时，SQL Server 会先在内存中修改Buffer，但是不会立即将这个数据叶写回硬盘，而是等到CheckPoint或lazy Writer进程运行时集中处理。当用户读取某个Page后，如果SQL Server没有内存压力，它不会在内存中删除这个Page，因为内存中的数据页始终存放着数据的最新状态，如果有其他用户使用这个Page，SQL Server 不需要从硬盘中读取一次，节省语句执行的时间。理想情况是SQL Server将用户需要访问的所有数据都缓存在内存中，SQL Server 永远不需要去硬盘读取数据，只需要在CheckPoint 或 lazy Write运行时把修改过的页面写回硬盘即可

2，查询计划缓存（Query Plan Cache）

存储查询语句和存储过程的执行计划，以供重用，而不需要重新编译（Compile），因为编译查询语句产生执行计划是一个非常耗费资源的过程。

二，查看内存消耗

在SQL Server中，只有内存书记员（Memory Clerk）能够分配内存，Memory Clerk会记录已经分配内存的数量，任何一个需要使用内存的对象，必须创建自己的Memory Clerk，并使用该Memory clerk来分配内存。

1，查看Memory clerk分配的内存量

select memory_node_id, 
 type,
 pages_kb,
 virtual_memory_reserved_kb,
 virtual_memory_committed_kb,
 shared_memory_reserved_kb,
 shared_memory_committed_kb,
 page_size_in_bytes
from sys.dm_os_memory_clerks 
where type = 'MEMORYCLERK_SQLQERESERVATIONS'

对于内存结点64，只在DAC中使用。

2，统计Memory Clerk分配的内存总量

select mc.type,mc.name,
 sum(mc.pages_kb) as AllocatedPages_KB,
 sum(mc.virtual_memory_reserved_kb) as VM_Reserved_KB,
 sum(mc.virtual_memory_committed_kb) as VM_Committed_KB,
 --sum(mc.shared_memory_reserved_kb) as ShareMem_Reserved_KB,
 --sum(mc.shared_memory_committed_kb) as ShareMem_Committed_KB,
 max(mc.page_size_in_bytes)/1024 as SinglePageSize_KB
from sys.dm_os_memory_clerks mc
group by mc.type,mc.name
order by AllocatedPages_KB desc,mc.type,mc.name

消耗内存较大的Clerk是：

• MEMORYCLERK_SQLBUFFERPOOL：基本是Buffer Pool中page的大小
• OBJECTSTORE_LOCK_MANAGER：锁结构使用的内存，当发生严重的锁阻塞时，这表明系统中，存储大量锁，造成锁管理占用大量的内存；
• CACHESTORE_OBJCP：触发器和存储过程等模块（Module）的执行计划占用的缓存空间；
• CACHESTORE_SQLCP：动态TSQL语句，即席（Adhoc）查询和预编译（Prepared） TSQL的执行计划缓存；
• CACHESTORE_COLUMNSTOREOBJECTPOOL：列存储索引（ColumnStore Index）占用的缓存

3，查看缓存中的数据页

当数据页从硬盘读取到内存之后，该数据页被复制到缓冲池（Buffer Pool），供SQL Server重用。每个缓存的数据页都有一个缓存描述器（Buffer Descriptor），用户唯一标识内存中的数据页，在SQL Server实例中缓存的每一个数据页，都能从 sys.dm_os_buffer_descriptors 查看缓存描述的信息。

select DB_NAME(bd.database_id) as dbname,
 OBJECT_NAME(p.object_id) as ObjectName,
 i.name as IndexName,
 count(0) as BufferCounts,
 sum(bd.free_space_in_bytes)/1024 as TotalFreeSpace_KB,
 cast(sum(bd.free_space_in_bytes)/(8*1024.0)/count(0) as decimal(10,4))*100 as FreeSpaceRatio,
 sum(cast(bd.is_modified as int)) as TotalDirtyPages,
 sum(bd.row_count) as TotalRowCounts
from sys.allocation_units au 
inner join sys.dm_os_buffer_descriptors bd
 on au.allocation_unit_id=bd.allocation_unit_id
inner join sys.partitions p 
 on au.container_id=p.hobt_id
inner join sys.indexes i 
 on p.object_id=i.object_id and p.index_id=p.index_id
inner join sys.objects o
 on p.object_id=o.object_id
where bd.database_id=DB_ID(N'database_name')
 and o.type<>N'S'
group by bd.database_id,p.object_id,i.name 
order by BufferCounts desc,dbname,ObjectName

4，查看计划缓存

产生执行计划是十分消耗CPU资源的，SQL Server会在内存的Plan Cache中存储每个查询计划（Query Plan），及其占用的内存空间，重用次数等信息。

select cp.objtype,cp.cacheobjtype,
 sum(cp.size_in_bytes) as TotalSize_B,
 COUNT(cp.bucketid) as CacheCounts,
 sum(cp.refcounts) as TotalRefCounts,
 sum(cp.usecounts) as TotalUseCounts
from sys.dm_exec_cached_plans cp 
group by cp.objtype,cp.cacheobjtype
order by TotalSize_B desc

三，清空缓存

在调优存储过程性能时，清空缓存是必需的，缓冲池（Buffer Pool）是SQL Server的缓存管理器，包含了SQL Server的绝大部分缓存数据（Cache），例如，执行计划缓存（Plan cache），数据缓存（Data cache）等。

清空缓存常用的命令有如下三个：

CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Checkpoint和DBCC DROPCLEANBUFFERS 用于清理数据缓存（Data Cache）中的脏页（dirty pages）和干净页（clean pages），而DBCC FREEPROCCACHE 用于清空所有的计划缓存（Plan Cache）。

1，清空数据缓存

checkpoint 用于将脏页（Dirty Pages）写入硬盘，脏页（Dirty Pages）是指数据页读入缓存后，被修改过，导致内存中数据页和硬盘中的数据页中的内容不同；干净页（Clean Pages）是指数据页被读入缓存后，没有被修改过，所以，内存中的数据页和硬盘中的数据页中的内容相同。不管是Dirty pages 还是 Clean pages 都是Data Cache，在性能调优时，都必须从内存中清理掉，否则，查询性能将忽略掉数据从硬盘加载到内存的IO消耗，影响查询语句的执行情况。

CHECKPOINT 命令用于产生冷缓存（Cold buffer Cache），该命令将当前数据库产生的所有脏页写入到硬盘，并清理内存buffer；在执行CHECKPOINT命令之后，执行 DBCC DROPCLEANBUFFERS 用于从缓冲池中清空所有的干净页。

在性能测试时，使用DBCC DROPCLEANBUFFERS从SQLSERVER的数据缓存池中清除所有的clean缓存数据，需要注意的是该命令只移走干净的缓存，不移走脏缓存。因此，在执行这个命令前，应该先执行CheckPoint，将所有脏页写入磁盘，这样在运行DBCC RROPCLEANBUFFERS 时，可以保证所有的数据缓存被清理，而不是其中的一部分。

2，清空计划缓存

计划缓存（Plan Cache）用于缓存查询语句的执行计划，每一条查询语句在执行之后，其查询计划都会缓存Plan Cache中。在产品环境中，不要轻易清理掉Plan Cache。如果检测到某个Plan Cache产生参数嗅探问题，导致性能十分低下，推荐修改查询语句，重新编译存储过程，以单独刷新该SP的计划缓存。

DBCC FREEPROCCACHE [ ( { plan_handle | sql_handle} ) ]

计划缓存，之前叫做过程缓存（procedure cache），执行DBCC FREEPROCCACHE 命令，释放所有的计划缓存，这回导致存储过程，AdHoc 查询等查询必须重新编译，产生新的计划缓存。

四，强制重新编译执行计划

修改存储过程，触发器等模块（Module）能够使其执行计划重新编译，除此之外，还有其他方法，能够强制重新编译执行计划

1，标记，下次重新编译

使用该存储过程，标记一个执行模块（SP，Trigger，User-Defined Function）在下次执行时，重新编译执行计划

sys.sp_recompile [ @objname = ] 'object'

2，不复用执行计划

在创建存储过程时，使用WITH RECOMPILE 选项，在每次执行SP时，都重新编译，使用新的执行计划。

CREATE PROCEDURE dbo.usp_procname 
 @Parameter_Name varchar(30) = 'Parameter_default_value'
WITH RECOMPILE

3，执行时重新编译

在执行存储过程时，重新编译存储过程的执行计划

exec dbo.usp_procname @Parameter_name='Parameter_value' 
WITH RECOMPILE

4，语句级别的重新编译

在SP中，使用查询选项 option（recompile），只重新编译该语句级别的执行计划

select column_name_list
from dbo.tablename
option(recompile)

SQL Server在执行查询之后，查询提示（RECOMPILE）指示存储引擎将计划缓存抛弃，在下次执行存储过程时，强制查询优化器重新编译，生成新的执行计划。在重新编译时，SQL Server 优化器使用当前的变量值生成新的计划缓存。

附：

冷缓存，热缓存，脏缓存和干净缓存名词解释：

• 净缓存页（Clean Buffer） 是指内存中未被修改的数据页，DBCC DROPCLEANBUFFERS 用于从缓冲池（Buffer Pool）移除干净页，释放Buffer。
• 脏缓存页（Dirty Buffer）是指数据页在内存中被修改，但是还没有写入到硬盘中，导致硬盘中的数据不同于内存，通常情况下，脏页通过CHECKPOINT进程来自动同步，CHECKPOINT 将脏页数据写入到硬盘中，使内存和硬盘文件中的数据保持一致，能够减少数据还原的时间。
• 冷缓存页（Cold Buffer）是指，在数据处理阶段，最近没有被使用的缓存页。
• 热缓存页（Hot Buffer）是指，在数据处理阶段，最近经常被使用的缓存页。

参考文档：

Recompile a Stored Procedure

What is a COLD, DIRTY or CLEAN Buffer

以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，同时也希望多多支持！

# sqlserver  # 性能调优  # sql  # server  # 调优  # sqlserver调优  # Mysql调优Explain工具详解及实战演练(推荐)  # 关于MySQL性能调优你必须了解的15个重要变量(小结)  # sql server性能调优 I/O开销的深入解析  # MySQL的常见存储引擎介绍与参数设置调优  # SQL Server 性能调优之查询从20秒至2秒的处理方法  # 千万级用户系统SQL调优实战分享  # 存储过程  # 是指  # 清空  # 盘中  # 会在  # 下次  # 数据处理  # 自己的  # 池中  # 的是  # 都是  # 这是  # 移走  # 都有  # 还没有  # 不需要  # 都能  # 只需  # 要去  # 所需 

相关文章： 建站之星Pro快速搭建教程：模板选择与功能配置指南  如何在云虚拟主机上快速搭建个人网站？  制作销售网站教学视频,销售网站有哪些？  C#怎么创建控制台应用 C# Console App项目创建方法  如何通过PHP快速构建高效问答网站功能？  建站之星五站合一营销型网站搭建攻略，流量入口全覆盖优化指南  如何用狗爹虚拟主机快速搭建网站？  c++怎么用jemalloc c++替换默认内存分配器【性能】  如何制作新型网站程序文件,新型止水鱼鳞网要拆除吗？  如何在宝塔面板中修改默认建站目录？  专业制作网站的公司哪家好,建立一个公司网站的费用.有哪些部分,分别要多少钱？  mc皮肤壁纸制作器,苹果平板怎么设置自己想要的壁纸我的世界？  正规网站制作公司有哪些,目前国内哪家网页网站制作设计公司比较专业靠谱？口碑好？  javascript中的try catch异常捕获机制用法分析  网站制作大概要多少钱一个,做一个平台网站大概多少钱？  建站之星代理费用多少？最新价格详情介绍  兔展官网 在线制作,怎样制作微信请帖？  高防服务器：AI智能防御DDoS攻击与数据安全保障  如何在香港服务器上快速搭建免备案网站？  网站制作模板下载什么软件,ppt模板免费下载网站？  b2c电商网站制作流程,b2c水平综合的电商平台？  如何注册花生壳免费域名并搭建个人网站？  开心动漫网站制作软件下载,十分开心动画为何停播？  怀化网站制作公司,怀化新生儿上户网上办理流程？  建站之星如何快速解决建站难题？  青浦网站制作公司有哪些,苹果官网发货地是哪里？  平台云上自主建站：模板化设计与智能工具打造高效网站  ,如何利用word制作宣传手册？  无锡制作网站公司有哪些,无锡优八网络科技有限公司介绍？  建站之星CMS五站合一模板配置与SEO优化指南  制作网站的软件免费下载,免费制作app哪个平台好？  建站之星如何实现网站加密操作？  建站之星后台密码遗忘如何找回？  我的世界制作壁纸网站下载,手机怎么换我的世界壁纸？  如何快速搭建个人网站并优化SEO？  移民网站制作流程,怎么看加拿大移民官网？  香港服务器部署网站为何提示未备案？  如何快速上传建站程序避免常见错误？  如何在IIS服务器上快速部署高效网站？  大连网站制作费用,大连新青年网站，五年四班里的视频怎样下载啊？  建站之星ASP如何实现CMS高效搭建与安全管理？  建站主机选购指南与交易推荐：核心配置解析  建站之星安装步骤有哪些常见问题？  如何用花生壳三步快速搭建专属网站？  如何在服务器上三步完成建站并提升流量？  上海网站制作开发公司,上海买房比较好的网站有哪些？  海南网站制作公司有哪些,海口网是哪家的？  新网站制作渠道有哪些,跪求一个无线渠道比较强的小说网站，我要发表小说？  品牌网站制作公司有哪些,买正品品牌一般去哪个网站买？  广州美橙建站如何快速搭建多端合一网站？