mssql buffer pool缓冲池简介


一、buffer pool 缓冲池功能简介

mssql buffer pool缓冲池在服务器中的作用为: 对 存储查询的数据信息,索引信息进行缓存,
缓存后可加快数据访问,使用户更快的检索到数据,减少物理读取的次数,减少IO操作。


二、缓冲池占用情况查看

select * from sys.dm_os_buffer_descriptors

运行此动态管理函数,可以获取,当前缓冲池中,所存放的数据信息,如下所示:

将返回:
database_id: 当前缓存信息所属数据库
file_id :所属文件
page_id :所属页id
page_level:页的索引级别
allocation_unit_id:页的分配单元ID,此值可用于连接 sys.allocation_units
page_type:该页类型
row_count:该页包含行数
free_space_in_bytes: 当前页剩余可用空间
is_modified:当前页数据缓存后,是否被修改过
numa_node: 缓冲区的非一致性内存访问节点


注意事项:

mssql db服务重启后
buffer pool 缓冲会释放


按数据库统计缓存信息

 select count(1) as [缓存信息数],
db_name(database_id)  as [数据库名]
 from sys.dm_os_buffer_descriptors
group by db_name(database_id)


获取当前数据库的所有对象的缓存信息

  SELECT COUNT(*)AS cached_pages_count 
    ,name ,index_id 
FROM sys.dm_os_buffer_descriptors AS bd 
    INNER JOIN 
    (
        SELECT object_name(object_id) AS name 
            ,index_id ,allocation_unit_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p 
                ON au.container_id = p.hobt_id 
                    AND (au.type = 1 OR au.type = 3)
        UNION ALL
        SELECT object_name(object_id) AS name   
            ,index_id, allocation_unit_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p 
                ON au.container_id = p.partition_id 
                    AND au.type = 2
    ) AS obj 
        ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = DB_ID()
GROUP BY name, index_id 
ORDER BY cached_pages_count DESC;