SQL Server性能调优之缓存
在执行任何查询时，SQLServer都会将数据读取到内存，数据使用之后，不会立即释放，而是会缓存在内存Buffer中，当再次执行相同的查询时，如果所需数据全部缓存在内存中，那么SQLServer不会产生DiskIO操作，立即返回查询结果，这是SQLServer的性能优化机制。
一，主要的内存消费者（MemoryConsumer)
1，数据缓存（DataCache）
DataCache是存储数据页（DataPage）的缓冲区，当SQLServer需要读取数据文件（File）中的数据页（DataPage）时，SQLServer会把整个Page都调入内存（内存中的一个Page叫做buffer），Page是数据访问的最小单元。
当用户修改了某个Page上的数据时，SQLServer会先在内存中修改Buffer，但是不会立即将这个数据叶写回硬盘，而是等到CheckPoint或lazyWriter进程运行时集中处理。当用户读取某个Page后，如果SQLServer没有内存压力，它不会在内存中删除这个Page，因为内存中的数据页始终存放着数据的最新状态，如果有其他用户使用这个Page，SQLServer不需要从硬盘中读取一次，节省语句执行的时间。理想情况是SQLServer将用户需要访问的所有数据都缓存在内存中，SQLServer永远不需要去硬盘读取数据，只需要在CheckPoint或lazyWrite运行时把修改过的页面写回硬盘即可
2，查询计划缓存（QueryPlanCache）
存储查询语句和存储过程的执行计划，以供重用，而不需要重新编译（Compile），因为编译查询语句产生执行计划是一个非常耗费资源的过程。
二，查看内存消耗
在SQLServer中，只有内存书记员（MemoryClerk）能够分配内存，MemoryClerk会记录已经分配内存的数量，任何一个需要使用内存的对象，必须创建自己的MemoryClerk，并使用该Memoryclerk来分配内存。
1，查看Memoryclerk分配的内存量
selectmemory_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 fromsys.dm_os_memory_clerks wheretype='MEMORYCLERK_SQLQERESERVATIONS'
对于内存结点64，只在DAC中使用。
2，统计MemoryClerk分配的内存总量
selectmc.type,mc.name, sum(mc.pages_kb)asAllocatedPages_KB, sum(mc.virtual_memory_reserved_kb)asVM_Reserved_KB, sum(mc.virtual_memory_committed_kb)asVM_Committed_KB, --sum(mc.shared_memory_reserved_kb)asShareMem_Reserved_KB, --sum(mc.shared_memory_committed_kb)asShareMem_Committed_KB, max(mc.page_size_in_bytes)/1024asSinglePageSize_KB fromsys.dm_os_memory_clerksmc groupbymc.type,mc.name orderbyAllocatedPages_KBdesc,mc.type,mc.name
消耗内存较大的Clerk是：
- MEMORYCLERK_SQLBUFFERPOOL：基本是BufferPool中page的大小
- OBJECTSTORE_LOCK_MANAGER：锁结构使用的内存，当发生严重的锁阻塞时，这表明系统中，存储大量锁，造成锁管理占用大量的内存；
- CACHESTORE_OBJCP：触发器和存储过程等模块（Module）的执行计划占用的缓存空间；
- CACHESTORE_SQLCP：动态TSQL语句，即席（Adhoc）查询和预编译（Prepared）TSQL的执行计划缓存；
- CACHESTORE_COLUMNSTOREOBJECTPOOL：列存储索引（ColumnStoreIndex）占用的缓存
3，查看缓存中的数据页
当数据页从硬盘读取到内存之后，该数据页被复制到缓冲池（BufferPool），供SQLServer重用。每个缓存的数据页都有一个缓存描述器（BufferDescriptor），用户唯一标识内存中的数据页，在SQLServer实例中缓存的每一个数据页，都能从sys.dm_os_buffer_descriptors查看缓存描述的信息。
selectDB_NAME(bd.database_id)asdbname, OBJECT_NAME(p.object_id)asObjectName, i.nameasIndexName, count(0)asBufferCounts, sum(bd.free_space_in_bytes)/1024asTotalFreeSpace_KB, cast(sum(bd.free_space_in_bytes)/(8*1024.0)/count(0)asdecimal(10,4))*100asFreeSpaceRatio, sum(cast(bd.is_modifiedasint))asTotalDirtyPages, sum(bd.row_count)asTotalRowCounts fromsys.allocation_unitsau innerjoinsys.dm_os_buffer_descriptorsbd onau.allocation_unit_id=bd.allocation_unit_id innerjoinsys.partitionsp onau.container_id=p.hobt_id innerjoinsys.indexesi onp.object_id=i.object_idandp.index_id=p.index_id innerjoinsys.objectso onp.object_id=o.object_id wherebd.database_id=DB_ID(N'database_name') ando.type<>N'S' groupbybd.database_id,p.object_id,i.name orderbyBufferCountsdesc,dbname,ObjectName
4，查看计划缓存
产生执行计划是十分消耗CPU资源的，SQLServer会在内存的PlanCache中存储每个查询计划（QueryPlan），及其占用的内存空间，重用次数等信息。
selectcp.objtype,cp.cacheobjtype, sum(cp.size_in_bytes)asTotalSize_B, COUNT(cp.bucketid)asCacheCounts, sum(cp.refcounts)asTotalRefCounts, sum(cp.usecounts)asTotalUseCounts fromsys.dm_exec_cached_planscp groupbycp.objtype,cp.cacheobjtype orderbyTotalSize_Bdesc
三，清空缓存
在调优存储过程性能时，清空缓存是必需的，缓冲池（BufferPool）是SQLServer的缓存管理器，包含了SQLServer的绝大部分缓存数据（Cache），例如，执行计划缓存（Plancache），数据缓存（Datacache）等。
清空缓存常用的命令有如下三个：
CHECKPOINT DBCCDROPCLEANBUFFERS DBCCFREEPROCCACHE
Checkpoint和DBCCDROPCLEANBUFFERS用于清理数据缓存（DataCache）中的脏页（dirtypages）和干净页（cleanpages），而DBCCFREEPROCCACHE用于清空所有的计划缓存（PlanCache）。
1，清空数据缓存
checkpoint用于将脏页（DirtyPages）写入硬盘，脏页（DirtyPages）是指数据页读入缓存后，被修改过，导致内存中数据页和硬盘中的数据页中的内容不同；干净页（CleanPages）是指数据页被读入缓存后，没有被修改过，所以，内存中的数据页和硬盘中的数据页中的内容相同。不管是Dirtypages还是Cleanpages都是DataCache，在性能调优时，都必须从内存中清理掉，否则，查询性能将忽略掉数据从硬盘加载到内存的IO消耗，影响查询语句的执行情况。
CHECKPOINT命令用于产生冷缓存（ColdbufferCache），该命令将当前数据库产生的所有脏页写入到硬盘，并清理内存buffer；在执行CHECKPOINT命令之后，执行DBCCDROPCLEANBUFFERS用于从缓冲池中清空所有的干净页。
在性能测试时，使用DBCCDROPCLEANBUFFERS从SQLSERVER的数据缓存池中清除所有的clean缓存数据，需要注意的是该命令只移走干净的缓存，不移走脏缓存。因此，在执行这个命令前，应该先执行CheckPoint，将所有脏页写入磁盘，这样在运行DBCCRROPCLEANBUFFERS时，可以保证所有的数据缓存被清理，而不是其中的一部分。
2，清空计划缓存
计划缓存（PlanCache）用于缓存查询语句的执行计划，每一条查询语句在执行之后，其查询计划都会缓存PlanCache中。在产品环境中，不要轻易清理掉PlanCache。如果检测到某个PlanCache产生参数嗅探问题，导致性能十分低下，推荐修改查询语句，重新编译存储过程，以单独刷新该SP的计划缓存。
DBCCFREEPROCCACHE[({plan_handle|sql_handle})]
计划缓存，之前叫做过程缓存（procedurecache），执行DBCCFREEPROCCACHE命令，释放所有的计划缓存，这回导致存储过程，AdHoc查询等查询必须重新编译，产生新的计划缓存。
四，强制重新编译执行计划
修改存储过程，触发器等模块（Module）能够使其执行计划重新编译，除此之外，还有其他方法，能够强制重新编译执行计划
1，标记，下次重新编译
使用该存储过程，标记一个执行模块（SP，Trigger，User-DefinedFunction）在下次执行时，重新编译执行计划
sys.sp_recompile[@objname=]'object'
2，不复用执行计划
在创建存储过程时，使用WITHRECOMPILE选项，在每次执行SP时，都重新编译，使用新的执行计划。
CREATEPROCEDUREdbo.usp_procname @Parameter_Namevarchar(30)='Parameter_default_value' WITHRECOMPILE
3，执行时重新编译
在执行存储过程时，重新编译存储过程的执行计划
execdbo.usp_procname@Parameter_name='Parameter_value' WITHRECOMPILE
4，语句级别的重新编译
在SP中，使用查询选项option（recompile），只重新编译该语句级别的执行计划
selectcolumn_name_list fromdbo.tablename option(recompile)
SQLServer在执行查询之后，查询提示（RECOMPILE）指示存储引擎将计划缓存抛弃，在下次执行存储过程时，强制查询优化器重新编译，生成新的执行计划。在重新编译时，SQLServer优化器使用当前的变量值生成新的计划缓存。
附：
冷缓存，热缓存，脏缓存和干净缓存名词解释：
- 净缓存页（CleanBuffer）是指内存中未被修改的数据页，DBCCDROPCLEANBUFFERS用于从缓冲池（BufferPool）移除干净页，释放Buffer。
- 脏缓存页（DirtyBuffer）是指数据页在内存中被修改，但是还没有写入到硬盘中，导致硬盘中的数据不同于内存，通常情况下，脏页通过CHECKPOINT进程来自动同步，CHECKPOINT将脏页数据写入到硬盘中，使内存和硬盘文件中的数据保持一致，能够减少数据还原的时间。
- 冷缓存页（ColdBuffer）是指，在数据处理阶段，最近没有被使用的缓存页。
- 热缓存页（HotBuffer）是指，在数据处理阶段，最近经常被使用的缓存页。
参考文档：
RecompileaStoredProcedure
WhatisaCOLD,DIRTYorCLEANBuffer
以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，同时也希望多多支持毛票票！