(转载)数据库的内存原理(sql server)

在执行任何查询时,SQL Server都会把数据读取到内存,在使用完数据之后,数据不会被立即删除,而是缓存在内存Buffer中,当再次获取相同的数据时,如果所需数据全部缓存在内存中,那么SQL Server不会产生Disk IO操作(把数据从硬盘导入到内存),而是直接从内存中获取数据。由于查询内存中的数据,速度非常快,SQL Server引擎会立即返回查询结果,缓存数据是是SQL Server的一种性能优化机制。

一,主要的内存消费者

缓存数据需要消耗内存,主要的内存消费者(Memory Consumer)是数据缓存、计划缓存、授予内存和日志缓存,SQL Server还会缓存其他类型的数据,这些数据占比较低,本文不作考虑。

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运行时把修改过的页面写回硬盘即可。

Buffer Pool 用于管理数据缓存,可以从 sys.dm_os_buffer_descriptions 中查询相关的信息。

2,查询计划缓存(Query Plan Cache)

计划缓存用于存储查询语句和存储过程的执行计划,便于计划的重用,因为编译查询语句产生执行计划是一个非常耗费资源的过程,如果执行计划被缓存起来,下次使用时就不需要重新编译(Compile)和重新生成,SQL Server引擎会直接复用已缓存的执行计划,可以通过sys.dm_exec_cached_plans 来查询计划缓存的信息。

对于Ad-Hoc查询,有一个特殊的优化选项:

执行计划生成后会存储在plan cache中,如果计划缓存从来都没有被重用过,这将会造成内存资源的浪费。当执行代码时,会产生一个hash值,用于匹配计划缓存中的hash值,相同的hash值代表语句是相同的。如果执行一个存储过程,那么会按照存储过程名称来创建hash值;如果是执行Ad-Hoc代码,那么会按照整个TSQL语句来创建Hash值,只有Ad-Hoc语句中有一点改变,都会产生不同的Hash值,导致执行计划无法重用。针对这类问题,可以考虑使用存储过程或者参数化的Ad-Hoc。

“针对即席工作负载进行优化”是一个Server级别的性能优化选项,用于提高包含许多临时批处理的工作负载的计划缓存的效率,如果把该选项设置为True,则数据库引擎在首次编译批处理时只保留计划缓存中的一个存根,而不是存储整个执行计划。当再次调用该批处理时,数据库引擎识别出该批处理在之前被执行过,进而从计划缓存中删除该执行计划的存根,并把完全编译的执行计划添加到计划缓存中。当非参数化的Ad-Hoc查询较多时,可以避免计划缓存存储过多的不会被复用的执行计划。

sql server 数据页缓存 sqlserver缓存机制_sql server 数据页缓存

3,授予内存(Granted Memory)

授予内存是已经分配给查询的那部分内存,可以通过sys.dm_exec_query_memory_grants查看,另外,RESOURCE_SEMAPHORE等待状态是针对memory grant的,所以,如果在sys.dm_os_wait_stats 中看到这个等待类型存在很久,并且处于前列,说明系统当前无可用的内存分配给查询,系统很有可能存在内存压力。

4,Log Cache

在数据被修改时,SQL Server会记录数据修改的日志,这些日志首先缓存在Log Cache中,然后积累到一定的数量或等很小的时间间隔后,写入到日志文件中。通常来说,任何一个数据修改,在Log Cache和Buffer Cache中都会有记录,Log Cache中的数据修改会在checkpoint被触发时写入到日志文件中去。

二,查看内存消耗

SQL Server的内存管理是一套完整的机制,只有内存书记员(Memory Clerk)能够分配内存,Memory Clerk会记录已经分配内存的数量,任何一个需要使用内存的对象,必须创建自己的Memory Clerk,并使用该Memory clerk来分配内存。

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

每一个对象通过Memory Clerk来分配内存的,内存消费者Buffer Pool的clerk是MEMORYCLERK_SQLBUFFERPOOL,计划缓存的clerk是MEMORYCLERK_SQLQUERYPLAN

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 in( 'MEMORYCLERK_SQLQUERYPLAN','MEMORYCLERK_SQLBUFFERPOOL')

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占用的内存大小
  • MEMORYCLERK_SQLQUERYPLAN:是计划缓存占用的内存大小
  • 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 object_name(p.object_id) as object_name
    ,o.type_desc
    ,i.name as index_name
    ,count(0) as buffer_counts
    ,cast(sum(bd.free_space_in_bytes)/(8*1024.0)/count(0) as decimal(10,4))*100 as free_space_ratio
    ,sum(cast(bd.is_modified as int)) as dirty_pages
    ,sum(bd.row_count) as row_counts
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()
    and o.type<>N'S'
group by p.object_id
    ,o.type_desc
    ,i.name 
order by buffer_counts desc
    ,object_name

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
select iif(d.database_id=32767,'Resource DB',db_name(d.database_id)) as db
    ,sum(d.row_count) as row_count
    ,count(0) as buffer_pages
    ,count(0)*8/1024 as buffer_mb
from sys.dm_os_buffer_descriptors d
where d.database_id between 5 and 32767
group by d.database_id
order by buffer_mb 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查询等必须重新编译,产生新的计划缓存。

附:冷缓存,热缓存,脏缓存和干净缓存名词解释:

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

参考文档:

 sys.dm_os_buffer_descriptors (Transact-SQL)

 What is a COLD, DIRTY or CLEAN Buffer

本文章为转载内容,我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题,欢迎原作者联系我们进行内容更正或删除文章。