sql server 去缓存查询 sqlserver缓存机制

本篇文章主要介绍了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），及其占用的内存空间，重用次数等信息。

三，清空缓存

在调优存储过程性能时，清空缓存是必需的，缓冲池（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）是指，在数据处理阶段，最近经常被使用的缓存页。