[SQL Server]内存和Buffer Pool

阳光穿透心脏的1/2处 2022-07-14 16:50 156阅读 0赞

以下仅为本人自已总结的东西，欢迎留补充和指正。

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）。下表为这两部分内存各自的用途：

<table style="border-collapse:collapse"> 
 <tbody> 
 <tr style=""> 
 <td colspan="3" style="border-bottom:windowtext 1pt solid; border-left:windowtext 1pt solid; padding-bottom:0cm; padding-left:5.4pt; width:426.1pt; padding-right:5.4pt; background:#e0e0e0; border-top:windowtext 1pt solid; border-right:windowtext 1pt solid; padding-top:0cm"> SQL Server 进程所占内存 </td> 
 </tr> 
 <tr style=""> 
 <td style="border-bottom:windowtext 1pt solid; border-left:windowtext 1pt solid; padding-bottom:0cm; padding-left:5.4pt; width:189.15pt; padding-right:5.4pt; background:#e0e0e0; border-top:#ebe9ed; border-right:windowtext 1pt solid; padding-top:0cm"> Buffer Pool EXEC sp_configure N'min server memory EXEC sp_configure N'max server memory </td> 
 <td colspan="2" style="border-bottom:windowtext 1pt solid; border-left:#ebe9ed; padding-bottom:0cm; padding-left:5.4pt; width:236.95pt; padding-right:5.4pt; background:#e0e0e0; border-top:#ebe9ed; border-right:windowtext 1pt solid; padding-top:0cm"> 非Buffer Pool (默认为256MB), 可以sqlserver.exe启动时加-g参数，预留足够内存 </td> 
 </tr> 
 <tr style=""> 
 <td style="border-bottom:windowtext 1pt solid; border-left:windowtext 1pt solid; padding-bottom:0cm; background-color:transparent; padding-left:5.4pt; width:189.15pt; padding-right:5.4pt; border-top:#ebe9ed; border-right:windowtext 1pt solid; padding-top:0cm"> Buffer pool中主要存放之前查询中的数据页，和索引页。然后根据它自已的算法，自动清理过期过访问或效率低下的页。 </td> 
 <td style="border-bottom:windowtext 1pt solid; border-left:#ebe9ed; padding-bottom:0cm; background-color:transparent; padding-left:5.4pt; width:130.4pt; padding-right:5.4pt; border-top:#ebe9ed; border-right:windowtext 1pt solid; padding-top:0cm"> SQL Server 工作线程 </td> 
 <td style="border-bottom:windowtext 1pt solid; border-left:#ebe9ed; padding-bottom:0cm; background-color:transparent; padding-left:5.4pt; width:106.55pt; padding-right:5.4pt; border-top:#ebe9ed; border-right:windowtext 1pt solid; padding-top:0cm"> 占用不多 </td> 
 </tr> 
 <tr style=""> 
 <td style="border-bottom:windowtext 1pt solid; border-left:#ebe9ed; padding-bottom:0cm; background-color:transparent; padding-left:5.4pt; width:130.4pt; padding-right:5.4pt; border-top:#ebe9ed; border-right:windowtext 1pt solid; padding-top:0cm"> 分布式查询引用的 OLE DB 访问接口 </td> 
 <td style="border-bottom:windowtext 1pt solid; border-left:#ebe9ed; padding-bottom:0cm; background-color:transparent; padding-left:5.4pt; width:106.55pt; padding-right:5.4pt; border-top:#ebe9ed; border-right:windowtext 1pt solid; padding-top:0cm"> 如操作链接服务器 </td> 
 </tr> 
 <tr style=""> 
 <td style="border-bottom:windowtext 1pt solid; border-left:#ebe9ed; padding-bottom:0cm; background-color:transparent; padding-left:5.4pt; width:130.4pt; padding-right:5.4pt; border-top:#ebe9ed; border-right:windowtext 1pt solid; padding-top:0cm"> 备份还原 </td> 
 <td style="border-bottom:windowtext 1pt solid; border-left:#ebe9ed; padding-bottom:0cm; background-color:transparent; padding-left:5.4pt; width:106.55pt; padding-right:5.4pt; border-top:#ebe9ed; border-right:windowtext 1pt solid; padding-top:0cm"> 维护计划或t-sql备份恢复 </td> 
 </tr> 
 <tr style=""> 
 <td style="border-bottom:windowtext 1pt solid; border-left:#ebe9ed; padding-bottom:0cm; background-color:transparent; padding-left:5.4pt; width:130.4pt; padding-right:5.4pt; border-top:#ebe9ed; border-right:windowtext 1pt solid; padding-top:0cm"> 扩展过程 </td> 
 <td style="border-bottom:windowtext 1pt solid; border-left:#ebe9ed; padding-bottom:0cm; background-color:transparent; padding-left:5.4pt; width:106.55pt; padding-right:5.4pt; border-top:#ebe9ed; border-right:windowtext 1pt solid; padding-top:0cm"> 如sp_或sys开头的系统存储过程，sp_OACreate 存储过程 </td> 
 </tr> 
 <tr style=""> 
 <td style="border-bottom:windowtext 1pt solid; border-left:#ebe9ed; padding-bottom:0cm; background-color:transparent; padding-left:5.4pt; width:130.4pt; padding-right:5.4pt; border-top:#ebe9ed; border-right:windowtext 1pt solid; padding-top:0cm"> 多页的分配器 SQL Server 内存管理器 </td> 
 <td style="border-bottom:windowtext 1pt solid; border-left:#ebe9ed; padding-bottom:0cm; background-color:transparent; padding-left:5.4pt; width:106.55pt; padding-right:5.4pt; border-top:#ebe9ed; border-right:windowtext 1pt solid; padding-top:0cm"> 如.net framework程序（它们连接sqlserver的网络包大小为8K, sqlserver默认网络包大小为4k） </td> 
 </tr> 
 <tr style=""> 
 <td style="border-bottom:windowtext 1pt solid; border-left:#ebe9ed; padding-bottom:0cm; background-color:transparent; padding-left:5.4pt; width:130.4pt; padding-right:5.4pt; border-top:#ebe9ed; border-right:windowtext 1pt solid; padding-top:0cm"> .dll文件 </td> 
 <td style="border-bottom:windowtext 1pt solid; border-left:#ebe9ed; padding-bottom:0cm; background-color:transparent; padding-left:5.4pt; width:106.55pt; padding-right:5.4pt; border-top:#ebe9ed; border-right:windowtext 1pt solid; padding-top:0cm"> &nbsp; </td> 
 </tr> 
 <tr style=""> 
 <td style="border-bottom:windowtext 1pt solid; border-left:#ebe9ed; padding-bottom:0cm; background-color:transparent; padding-left:5.4pt; width:130.4pt; padding-right:5.4pt; border-top:#ebe9ed; border-right:windowtext 1pt solid; padding-top:0cm"> SQL Server CLR 的 Microsoft COM 对象 </td> 
 <td style="border-bottom:windowtext 1pt solid; border-left:#ebe9ed; padding-bottom:0cm; background-color:transparent; padding-left:5.4pt; width:106.55pt; padding-right:5.4pt; border-top:#ebe9ed; border-right:windowtext 1pt solid; padding-top:0cm"> &nbsp; </td> 
 </tr> 
 </tbody> 
</table>

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

![0_1287043095U27a.gif][]

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

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

if ((object\_id('sp\_osbufferdescriptors\_agg') is not null)

and (objectproperty(object\_id('sp\_osbufferdescriptors\_agg'),'IsProcedure') = 1))

drop proc \[dbo\].sp\_osbufferdescriptors\_agg

create proc \[dbo\].sp\_osbufferdescriptors\_agg

/\*

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;

\-- ==============================================================

use master

if ((object\_id('sp\_osbufferdescriptors') is not null) and (objectproperty(object\_id('sp\_osbufferdescriptors'), 'IsProcedure') = 1))

drop proc \[dbo\].sp\_osbufferdescriptors

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

/\*

\-- 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