[SQL Server]内存和Buffer Pool

 SQL Server的所需要的内存，包括SQL Server服务(sqlserver.exe)和其它一些组件所占用的内存，例如SQL Server 代理程序 (SQLAGENT.exe), SQL Server 复制代理程序、 SQL Server 报表服务(ReportingServicesService.exe)、SQL Server Analysis Services(msmdsrv.exe)、 SQL Server Integration Services(MsDtsSrvr.exe)，和 SQL Server 全文搜索(msftesql.exe)。

 

 在一台运行SQL Server的服务器上，运行着sqlserver服务(sqlserver.exe)和其它一些组件。在sqlserver服务(sqlserver.exe)获取到的内存中，又分为2大块：一部分为buffer pool，另一部分为非buffer pool（默认sqlserver.exe给它预留了256MB）。下表为这两部分内存各自的用途：

SQL Server 进程所占内存
Buffer Pool EXEC sp_configure N'min server memory EXEC sp_configure N'max server memory	非Buffer Pool (默认为256MB), 可以sqlserver.exe启动时加-g参数，预留足够内存
Buffer pool中主要存放之前查询中的数据页，和索引页。然后根据它自已的算法，自动清理过期过访问或效率低下的页。	SQL Server 工作线程	占用不多
	分布式查询引用的 OLE DB 访问接口	如操作链接服务器
	备份还原	维护计划或t-sql备份恢复
	扩展过程	如sp_或sys开头的系统存储过程，sp_OACreate 存储过程
	多页的分配器 SQL Server 内存管理器	如.net framework程序（它们连接sqlserver的网络包大小为8K, sqlserver默认网络包大小为4k）
	.dll文件
	SQL Server CLR 的 Microsoft COM 对象

 

当你的系统中，大量用到“非buffer pool”时，你就要注意了，需要调整sqlserver.exe预留给它的空间，即在SQL Server服务的启动参数中，指定预留内存，下图指定了512MB, 然后重启服务。

 

 

-- 统计当前内存分配情况

       select physical_memory_in_bytes / 1024 / 1024 as physical_memory_mb,

       virtual_memory_in_bytes / 1024 / 1024 as virtual_memory_mb,

       bpool_committed * 8 / 1024 as bpool_committed_mb,

       bpool_commit_target * 8 / 1024 as bpool_target_mb,

       bpool_visible * 8 / 1024 as bpool_visible_mb

       from sys.dm_os_sys_info

 

-- buffer pool的分配情况，可以查看系统管理视图：sys.dm_os_buffer_descriptors

-- 清空buffer pool: dbcc dropcleanbuffers;

若要从缓冲池中删除清除缓冲区，请首先使用 CHECKPOINT 生成一个冷缓存。这可以强制将当前数据库的全部脏页写入磁盘，然后清除缓冲区。完成此操作后，便可发出 DBCC DROPCLEANBUFFERS 命令来从缓冲池中删除所有缓冲区。

 

-- 以下为2个buffer pool的使用详细情况的查询存储过程,

-- 来源于 http://www.mssqltips.com/tip.asp?tip=1187

use master

go

 

if ((object_id('sp_osbufferdescriptors_agg') is not null)

    and (objectproperty(object_id('sp_osbufferdescriptors_agg'),'IsProcedure') = 1))

  drop proc [dbo].sp_osbufferdescriptors_agg

go

 

create proc [dbo].sp_osbufferdescriptors_agg

as

  /*

SAMPLE EXECUTION:

exec sp_osbufferdescriptors_agg

*/

  set nocount  on ;

 

  set transaction  isolation  level  read  uncommitted ;

 

  select   case

             when grouping(dbName) = 1 then '--- TOTAL ---'

             else dbName

           end as dbName,

           case

             when grouping(fileId) = 1 then '--- TOTAL ---'

             else fileId

           end as fileId,

           case

             when grouping(pageType) = 1 then '--- TOTAL ---'

             else pageType

           end as pageType,

           count(* ) as countPages,

           sum(row_count) as sumRowCount,

           avg(row_count) as avgRowCount,

           sum(freeSpaceBytes) as sumFreeSpaceBytes,

           avg(freeSpaceBytes) as avgFreeSpaceBytes

  from     (select case

                     when database_id = 32767 then 'resourceDb'

                     else cast(db_name(database_id) as varchar(25))

                   end as dbName,

                   cast(file_id as varchar(10)) as fileId,

                   cast(page_type as varchar(25)) as pageType,

                   row_count as row_count,

                   free_space_in_bytes as freeSpaceBytes

            from   sys.dm_os_buffer_descriptors bufferDescriptor with (nolock)) tmp

  group by dbName,fileId,pageType with rollup

  order by case

             when grouping(dbName) = 1 then 'zzzzzzzzzzzzzzzzzzzzzzzzzzz'

             else dbName

           end,

           case

             when grouping(fileId) = 1 then 'zzzzzzzzzzzzzzzzzzzzzzzzzzz'

             else fileId

           end,

           case

             when grouping(pageType) = 1 then 'zzzzzzzzzzzzzzzzzzzzzzzzzzz'

             else pageType

           end;

go

 

 

 

 

 

-- ==============================================================

use master

go

 

if ((object_id('sp_osbufferdescriptors') is not null) and (objectproperty(object_id('sp_osbufferdescriptors'), 'IsProcedure') = 1))

drop proc [dbo].sp_osbufferdescriptors

go

 

 

create proc [dbo].sp_osbufferdescriptors

    @top int = 0, -- Limits the result set to the top # specified - if null/default/0, all records are returned

    @opts int = 0 -- Option values for execution - bit flags:

-- <no opts> - If no opts are set, database level information is

-- returned for the database context we're executing in

-- 1 bit - If set, system level os_buffer information is returned

-- only - no db level information is returned

-- 2 bit - If set, and the 1 bit is NOT set, all db specific

-- information is gathered by iterating through all

-- databases on the system and gathering info

as

 

/*

-- Get database level information for the current db only

exec sp_osbufferdescriptors;

-- Only the top 20 results

exec sp_osbufferdescriptors @top = 20;

-- Get system level information only

exec sp_osbufferdescriptors @opts = 1;

-- Only top 5 results

exec sp_osbufferdescriptors @top = 5, @opts = 1;

-- Get database level information for all db's on the system

exec sp_osbufferdescriptors @opts = 2;

-- Only top 20 results

exec sp_osbufferdescriptors @top = 20, @opts = 2;

*/

 

set nocount on;

set transaction isolation level read uncommitted;

 

declare @sql nvarchar(4000);

 

-- Format incoming data

select @opts = isnull(@opts,0),

       @top = case when @top > 0 then @top else 0 end;

 

-- If no options were specified, we get the data for the current db and exit

if @opts = 0 begin

    -- Get largest buffer consumers for the given database

    select @sql = N'

           select ' + case when @top > 0 then N'top ' + cast(@top as nvarchar(20)) else N'' end + N' count(*) as bufferCount,

           db_name() as dbName,

           object_name(p.object_id) as objectName, isnull(i.name,''HEAP'') as indexName,

           max(p.partition_number) as partitionCount, sum(p.rows) as indexRowCount,

          sum(a.total_pages) as auTotalPages, sum(a.used_pages) as auUsedPages, sum(a.data_pages) as auDataPages

           from sys.partitions p with(nolock)

           join sys.allocation_units a with(nolock)

           on p.partition_id = a.container_id

           join sys.dm_os_buffer_descriptors b with(nolock)

           on a.allocation_unit_id = b.allocation_unit_id

           join sys.indexes i with(nolock)

           on p.object_id = i.object_id

           and p.index_id = i.index_id

           where b.database_id = db_id()

           group by p.object_id, i.name

           order by count(*) desc, p.object_id, i.name;';

    exec (@sql);

    return;

end

 

 

-- If 1 bit is set, we get system level information only...

if @opts & 1 = 1 begin

    -- Get largest buffer consumers for the system

    select @sql = N'

           select ' + case when @top > 0 then N'top ' + cast(@top as nvarchar(20)) else N'' end + N' count(*) as bufferCount,

           case when grouping(b.database_id) = 1 then ''--- TOTAL ---'' else

           case when b.database_id = 32767 then ''resourceDb'' else db_name(b.database_id) end end as dbName

           from sys.dm_os_buffer_descriptors b with(nolock)

           group by b.database_id with rollup

           order by case when grouping(b.database_id) = 1 then 0 else count(*) end desc;';

    exec (@sql);

    return;

end

 

 

-- If the 2 bit is set, we get database level information for multiple db's as appropriate

if @opts & 2 = 2 begin

    -- Create a temp object for storage

    create table #osBufferDescriptorsDbData (

       bufferCount bigint,

       dbName nvarchar(250),

       objectName nvarchar(250),

       indexName nvarchar(250),

       partitionCount int,

       indexRowCount bigint,

       auTotalPages bigint,

       auUsedPages bigint,

       auDataPages bigint);

 

    -- Gather up the appropriate data from each database on the server (not system db except tempdb)

    select @sql = N'use [?];

              if ''?'' in (''master'',''model'',''msdb'') return;

              insert #osBufferDescriptorsDbData (bufferCount, dbName, objectName, indexName, partitionCount, indexRowCount, auTotalPages, auUsedPages, auDataPages)

              select ' + case when @top > 0 then N'top ' + cast(@top as nvarchar(20)) else N'' end + N' count(*) as bufferCount,

                  db_name() as dbName,

                  object_name(p.object_id) as objectName, isnull(i.name,''HEAP'') as indexName,

                  max(p.partition_number) as partitionCount, sum(p.rows) as indexRowCount,

                  sum(a.total_pages) as auTotalPages, sum(a.used_pages) as auUsedPages, sum(a.data_pages) as auDataPages

              from sys.partitions p with(nolock)

              join sys.allocation_units a with(nolock)

              on p.partition_id = a.container_id

              join sys.dm_os_buffer_descriptors b with(nolock)

              on a.allocation_unit_id = b.allocation_unit_id

              join sys.indexes i with(nolock)

              on p.object_id = i.object_id

              and p.index_id = i.index_id

              where b.database_id = db_id()

              group by p.object_id, i.name;';

               

    exec sp_MSforeachdb @sql;

 

    -- Return the results

    select @sql = N'

           select ' + case when @top > 0 then N'top ' + cast(@top as nvarchar(20)) else N'' end + N' *

           from #osBufferDescriptorsDbData with(nolock)

           order by bufferCount desc, dbName, objectName;';

    exec (@sql);

 

    -- Cleanup

    drop table #osBufferDescriptorsDbData;

end

go