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调优实战分享  # 存储过程  # 是指  # 清空  # 盘中  # 会在  # 下次  # 数据处理  # 自己的  # 池中  # 的是  # 都是  # 这是  # 移走  # 都有  # 还没有  # 不需要  # 都能  # 只需  # 要去  # 所需 

相关文章： 三星网站视频制作教程下载,三星w23网页如何全屏？  网站制作话术技巧,网站推广做的好怎么话术？  建站之星手机一键生成：多端自适应+小程序开发快速建站指南  如何高效搭建专业期货交易平台网站？  邀请函制作网站有哪些,有没有做年会邀请函的网站啊？在线制作，模板很多的那种？  深圳网站制作费用多少钱,读秀，深圳文献港这样的网站很多只提供网上试读，但有些人只要提供试读的文章就能全篇下载，这个是怎么弄的？  如何快速查询域名建站关键信息？  如何快速搭建FTP站点实现文件共享？  宝盒自助建站智能生成技巧：SEO优化与关键词设置指南  常州自助建站：操作简便模板丰富，企业个人快速搭建网站  建站主机是否等同于虚拟主机？  哈尔滨网站建设策划,哈尔滨电工证查询网站？  品牌网站制作公司有哪些,买正品品牌一般去哪个网站买？  学校建站服务器如何选型才能满足性能需求？  青岛网站建设如何选择本地服务器？  如何通过山东自助建站平台快速注册域名？  linux top下的 minerd 木马清除方法  python的本地网站制作,如何创建本地站点？  C++ static_cast和dynamic_cast区别_C++静态转换与动态类型安全转换  如何通过FTP服务器快速搭建网站？  网站规划与制作是什么,电子商务网站系统规划的内容及步骤是什么？  哪家制作企业网站好,开办像阿里巴巴那样的网络公司和网站要怎么做？  建站与域名管理如何高效结合？  网站建设设计制作营销公司南阳,如何策划设计和建设网站？  临沂网站制作公司有哪些,临沂第四中学官网？  网站制作与设计教程,如何制作一个企业网站，建设网站的基本步骤有哪些？  西安制作网站公司有哪些,西安货运司机用的最多的app或者网站是什么？  南阳网站制作公司推荐,小学电子版试卷去哪里找资源好？  代刷网站制作软件,别人代刷火车票靠谱吗？  建站之星体验版：智能建站系统+响应式设计，多端适配快速建站  香港服务器租用费用高吗？如何避免常见误区？  网站图片在线制作软件,怎么在图片上做链接？  *服务器网站为何频现安全漏洞？  高端智能建站公司优选：品牌定制与SEO优化一站式服务  如何通过VPS搭建网站快速盈利？  官网自助建站平台指南：在线制作、快速建站与模板选择全解析  微信h5制作网站有哪些,免费微信H5页面制作工具？  长沙做网站要多少钱,长沙国安网络怎么样？  建站IDE高效指南：快速搭建+SEO优化+自适应模板全解析  网站制作公司,橙子建站是合法的吗？  如何选择域名并搭建高效网站？  如何在阿里云虚拟服务器快速搭建网站？  视频网站制作教程,怎么样制作优酷网的小视频？  香港服务器部署网站为何提示未备案？  如何制作算命网站,怎么注册算命网站？  如何快速搭建个人网站并优化SEO？  湖州网站制作公司有哪些,浙江中蓝新能源公司官网？  大连网站制作公司哪家好一点,大连买房网站哪个好？  合肥做个网站多少钱,合肥本地有没有比较靠谱的交友平台？  建站主机是什么？如何选择适合的建站主机？