场景带入
双十一后，老鸟接二连三的狂轰滥炸着菜鸟：“你读过一本叫《谁动了我的奶酪》的书吗？正好，你研究下谁动了SQL Server的Buffer Pool吧？”。
菜鸟又是满脸懵逼茫然状：“这谁跟谁啊？有半毛钱关系吗？”。没办法，老鸟交代的任务，菜鸟还是要一丝不苟的竭尽全力。
哪些数据库占用了Buffer Pool
于是，菜鸟从大处着眼：“哪些数据库占用了SQL Server的Buffer Pool？每个库占用了多少Buffer Pool的空间呢？”。带着这样的疑问，菜鸟根据“占用Buffer Pool大户汇总”截图中第二部分数据得到了以下一个直观的直方图。
直方图的解释：这个直方图从cachedPages和cachedSize两个维度来统计每个数据库占用Buffer Pool的缓存页数量和缓存空间大小，发现AdventureWorks2008R2占用了最大的Buffer Pool空间大小。
哪些表占用了Buffer Pool
菜鸟完成了数据库级别Buffer Pool占用情况的统计后，成就感爆棚，接二连三的开始表级别Buffer Pool占用情况统计（在此仅以AdventureWorks2008R2数据库为例。），数据来自“占用Buffer Pool大户汇总”截图中第三部分。
直方图的解释：这个直方图从cachedPages和cachedSize两个维度来统计排名前十位的表占用Buffer Pool的缓存页数量和缓存空间情况，这些表均位于AdventureWorks2008R2数据库下，发现SaleOrderDetails这个表占用了最大的Buffer Pool空间大小。
哪些索引占用了Buffer Pool
菜鸟再次信心满满，决定向更高的高度进发：统计下AdventureWorks2008R2数据库下索引占用了Buffer Pool排名前十位，数据来自“占用Buffer Pool大户汇总”截图中第四部分。
直方图的解释：这个直方图从cachedPages和cachedSize两个维度来统计排名前十位的索引占用Buffer Pool的缓存页数量和缓存空间大小，这些索引均属于AdventureWorks2008R2数据库的表，为了直方图的美观这里使用索引名字的Hash来代替索引名字。发现索引Hash为2109565184的索引占用了最多的Buffer Pool空间。
谁动了我的Buffer Pool
菜鸟完成了上面所有的研究后，开始飘飘然起来，决定再精益求精下：“我们能否知道哪个数据库改动了SQL Server的Buffer Pool呢？Buffer Pool被修改的概率分布情况如何？”。于是写下了如下的SQL语句：
USE master
GO
;WITH DATA
AS(
SELECT
cached_pages_kb = count(1)*8
, total_cached_pages = count(1)
, cached_modified_pages =
(select count(1)
from sys.dm_os_buffer_descriptors b
where b.database_id=a.database_id
and is_modified=1)
,database_name = CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END
FROM sys.dm_os_buffer_descriptors a
GROUP BY db_name(database_id) ,database_id
)
SELECT
database_name
, cached_pages_kb
, cached_modified_percentage = CAST(cached_modified_pages*100./total_cached_pages AS decimal(4,2))
FROM DATA
ORDER BY database_name ASC;
AI 代码解读
结果如下:
做图如下:
折线图解释：这个折线图反应了所有数据库修改Buffer Pool的概率，从结果来看Tempdb修改的概率最大，达到了96.83%，说明Tempdb对Buffer Pool的修改非常频繁，缓存效果不好。当然这个和现实显然也是非常吻合的，因为Tempdb本来就是存放临时信息，会不断的存储和删除数据。
占用Buffer Pool大户汇总
占用Buffer Pool大户汇总，从数据库，表和索引三个层面统计Buffer Pool空间占用情况，脚本如下：
USE master
GO
/*
** This script utility is used for calculation data pages located in buffer pool cache
*/
set nocount on
declare
@database_name sysname
,@table_name sysname
;
select
@database_name = null
--@database_name: null : all databases;
--@database_name: empty (''): current database
,@table_name = N''
--@table_name: null or empty: all tables;
;
--private variables
declare
@db_name sysname
,@sql nvarchar(max)
;
select
@db_name = ''
,@sql = N''
;
--@database_name is empty, change to current database.
if @database_name = N''
set
@database_name = DB_NAME()
;
--create temp table
if OBJECT_ID('tempdb..#temp','u') is not null
drop table #temp
create table #temp
(
id int identity(1,1) not null primary key
,database_id bigint not null
,database_name sysname not null
,object_id bigint null
,schema_name sysname null
,object_name sysname null
,index_id bigint null
,ix_type_desc varchar(30) null
,index_name sysname null
,cached_pages_count bigint not null
,cached_size bigint
);
--cursor to loop process each database.
declare
cur_databases cursor local static forward_only read_only
for
select name
from sys.databases
where state = 0
and name = isnull(@database_name,name)
order by name;
open cur_databases
fetch next from cur_databases into @db_name
while @@FETCH_STATUS = 0
begin
raiserror('searching on database %s......',10,1,@db_name) with nowait
set
@sql = N'USE ' +QUOTENAME(@db_name) + N'
;with data
as
(
select
buf.database_id
,database_name = case database_id
when 32767 then ''resourcedb''
else db_name(buf.database_id)
end
,part.object_id
,schema_name = schema_name(tb.schema_id)
,object_name = tb.name
,part.index_id
,ix_type_desc = inx.type_desc
,index_name = inx.name
,cached_pages_count = COUNT(1)
,cached_size = COUNT(1) * 8
from sys.dm_os_buffer_descriptors as buf with(nolock)
left join sys.allocation_units as unit with(nolock)
on buf.allocation_unit_id = unit.allocation_unit_id
left join sys.partitions as part with(nolock)
on unit.container_id = part.hobt_id
left join sys.objects as tb with(nolock)
on part.object_id = tb.object_id
left join sys.indexes as inx with(nolock)
on tb.object_id = inx.object_id
and inx.index_id = part.index_id
where buf.database_id = db_id()
and unit.type in(1,2,3)
group by buf.database_id,tb.schema_id,tb.name,part.object_id,part.index_id,inx.name,inx.type_desc
)
select * from data
';
--save the query result into temp table
insert into #temp
exec sys.sp_executesql @sql
fetch next from cur_databases into @db_name
end
close cur_databases
deallocate cur_databases
--save resourcedb into temp table
INSERT INTO #temp
select
database_id
,database_name = 'ResourceDb'
,object_id = null
,SCHEMA_NAME = null
,OBJECT_NAME = null
,index_id = null
,ix_type_desc = null
,index_name = null
,cached_pages_count = count(1)
,cached_size = COUNT(1) * 8
from sys.dm_os_buffer_descriptors as buf with(nolock)
where database_id = 32767
and ISNULL(@database_name, N'') = N''
group by database_id
/*
total buffer pool page count & size
*/
select
cached_pages_count = SUM(cached_pages_count)
,buffer_size_per = SUM(cached_pages_count) * 8
from #temp
/*
how much memory for each database
*/
select
database_id
,database_name
,cached_pages_count = SUM(cached_pages_count)
,buffer_size = SUM(cached_pages_count) * 8
from #temp
group by database_id,database_name
order by database_name ASC
/*
how much memory for specify table
*/
SET
@sql = N'
select TOP 10
database_name
, schema_name
, object_name
, cached_pages = SUM(cached_pages_count)
, cached_size = sum(cached_size)
from #temp
WHERE object_id is not null
and database_name=''AdventureWorks2008R2'''
;
IF ISNULL(@table_name, '') > ''
BEGIN
SET @sql = @sql + ' AND object_name = @table_name'
END
SET
@sql = @sql + N'
GROUP BY database_name, schema_name,object_name
order by cached_size DESC'
;
EXEC sys.sp_executesql @sql
,N'@table_name sysname'
,@table_name = @table_name
;
/*
how much buffer pool will be going to for indexes
*/
select TOP 10 index_name,indexHash = CHECKSUM(index_name),cached_pages_count,cached_size
from #temp WITH(NOLOCK)
WHERE database_name='AdventureWorks2008R2'
and index_name is not null
ORDER BY cached_size DESC
AI 代码解读
查询语句执行后的结果如下：
写在最后
老鸟对菜鸟关于Buffer Pool的研究结果非常满意，拍拍菜鸟的肩膀说：“不错啊，小子，又猛又持久啊，保持啊”。
