详细讲述SQLSERVER内存和缓冲区管理

详细讲述

SQL SERVER

内存和缓冲区管理

为 SQL Server 提供的可用内存越多， SQL Server 就有越多的资源使用于缓冲区，即可减少 服务器从磁盘读取数据以获取静态信息或编译过程计划的次数。调整 SQL Server 所占内存 可通过在服务器配置文件中设置 total memory 参数进行，该参数的值指定了 SQL Server 在 启动时所需的内存总量，如若该值为 10000 页，则 SQL Server 在启动时就将试图获得 1000 0*2KB=19.5MB 的内存，若不能满足则启动失败。

注意：若操作系统在进程的整个生命周期中支持动态内存分配，则可在 SQL Server 启 动后为其分配额外的内存。

SQL Server启动时将内存分配给 SQL Server可执行代码，SQL Server使用的静态内存， 用户可配置参数占用的内存及不驻留在缓存上的数据结构，剩余的内存分配给两种 SQL Se

rver 缓冲区即：数据缓冲区和过程缓冲区。两缓冲区的大小对整个系统的性能影响很大，在 一个开发系统上可能要增加过多端程缓冲区的专用内存， 而在一生产系统上则需减少过程缓 冲区大小以便获得更大的数据缓冲区。

1、确定缓冲区大小

在 total memory 配置的总空间中，首先要除去可爱执行代码所占空间，其大小因平台 和版本不同而异，可使用 sp_configure 查询 executable codesize 参数的值得知，一般为 3-4 MB ；其次需除去内部结构所占空间，内部结构分成内核结构和服务器结构，亦可将此部分 内存看成静态开销和用户配置参数占用两个部分， 前者大小不受用户可配置参数的影响， 一 般为 2-3MB ，而后者即用户可配置参数的大小则取决于配置参数的类型及其值的大小，包 括 number of user connections,number

of open databases,number of devices,number of ope n objects,number of locks 等，要得到这些用户配置参数使用内存大小的精确估计，可调用 不带选项的 sp_configure 以显示所有参数和每个参数使用的内存量， 其和即是用户配置参数 所占用内存。

除去上述占用， 剩余内存将以 procedure cache percent 配置参数的值按比例分配给过程 缓冲区和数据缓冲区，例若值为 20，即表示剩余内存的 20%分配给过程缓冲区而 80%分配

给数据缓冲区。确定缓冲区大小的另一种方法是检查 SQL SERVE 启动时写入 SQL Server

错误日志的内存信息， 其中准确地说明了分配给过程缓冲区和数据缓冲区的数据量， 以及多 少个过程或其他编译对象能同时驻留在缓冲区中，如相关信息为：

Server:Number of proc buffers assocated:556

Server:Number of blocks left for proc headers:629

Server:Memory allocated for the default data cache:4144kb

前两行为过程缓冲区信息， 过程缓冲区总大小是分配给过程缓冲区的内存

分

配给过程头 （即存放编译对象如存储过程的地方，根据被存储对象的大小，可能需要一个或 多个过程头 ）的内存 （第二行 ）之和，可存储在过程缓冲区中的编译对象受到此二者中较小者的 限制。上述信息中，第一行指明了分配给过程缓冲区的缓冲区数量为

大小为76B，故过程缓冲区大小即为

556KB ，每个缓冲区

（第一行 ）及42256B，合21页；第二行则指明了分配给过程头的空

间为 629页，由此可以得出，过程缓冲区总大小为 629+21=650 页，合 1.27MB。

信息中第三行则指明了分配给默认数据库缓冲区的空间大小。 此外， 若配置了命名缓冲 区，则相关信息还将包含每个命名缓冲区的信息， 再加上这些命名缓冲区所占内存大小即得 到总的数据缓冲区大小。欲知数据缓冲区大小还可通过 sp_helpcache过程得到每个缓冲区和

捆绑到这些缓冲区中的对象以及与不同缓冲区大小相关的开销的详细信息。

2、缓冲区管理

数据缓冲区保存 SQL Server 当前使用和最近使用过的数据页

QL Server 时,会产生一个默认的数据缓冲区。

,索引页和日志页。初装 S

SQL Server 允许系统管理员把数据缓冲区分

成独立的命名数据缓冲区 ,然后可把数据库或数据库对象捆绑到这些命名缓冲区上

区空间进行更合理的组织 ,控制数据库 ,表和索引在内存的驻留。

区内创

,以对缓冲

另外,系统管理员还可在缓冲

建缓冲池 用于执行大块的磁盘I/O,改善数据查询性能，减少磁盘I/O。有关数据库缓冲区的指 令有:sp_cacheconfig 创建或删除命名缓冲区 ,改变缓冲区的大小或类型。

sp_poolconfig 创建或删除 I/O 缓冲池 ,并改变其大小。

sp_bindeache 将数据库或数据库对象捆绑到缓冲区。

sp_unbindcache 从一个缓冲区中取消对指定对象或数据库的捆绑。

sp_unbindcache_all 从一个缓冲区中取消所有对象的捆绑。

sp_helpcache 报告有关数据缓冲区的小结信息 ,并显示捆绑于缓冲区的数据库和对象。

sp_cachestrategy 报告有关为表 ,索引设置的缓冲区策略 ,禁止或重新允许先提或

略。

MRU 策

sp_logiosize 为日志改变默认的 I/O 大小。

sp_spaceused 估计表和索引大小或有关数据库使用的空间量的信息。

sp_estspace 估计表和索引的大小 ,给出表包含的行的数目。

sp_help 报告表将捆绑于哪个缓冲区。

sp_helpindex 报告索引将捆绑于哪个缓冲区。

sp_helpdb 报告数据库将捆绑于哪个缓冲区。

setshowplanon 报告有关用于查询的 I/O 大小和缓冲区的应用策略。

setstatisticsioon 报告为查询进行的读操作数量。

setprefetch on/off 允许或禁止某个会话的预先提取。

(1) 查询数据库缓冲区的信息：

查看默认数据缓冲区的大小,可使用指令sp_cacheconfig "default data cache",显示结果 如下 ,由两部分构成 ,上部分报告每个缓冲区中的内存池配置信息并总计了所有配置缓冲区的 大小；下部分提供了每个缓冲区中的其他详细信息。

第一部分输出各列含义为 :cache name 为缓冲区名； status 指示该缓冲区是否激活 （值为

pend/act 时表示该缓冲区刚建立 ,重启后将被激活； 值为 active 时表示该缓冲区目前正处于激 活状态；值为 pend/del 时表示该缓冲区处于激活状态 ,但在服务器重启后将被删除 ）； type 指

“default 示缓冲区是否可以存储数据

（“mixed'和）日志页（“logonly只有默认缓冲区才会有

类型 ,且默认的数据缓冲区类型不能改变 ,其他类型缓冲区也不能改为 “default。”

config value 显示下次 SQL Server 重启后缓冲区的大小， 在此情况下， 默认缓冲区不能 显式配置 ,故信息中其大小为； run value 显示 SQL Server 目前使用的大小， 对于默认数据缓 冲区 ,该值将作为未显示配置到其他缓冲区中的所有数据缓冲区空间。

第二部分输出各列含义为 :IO size 显示缓冲池中的缓存大小，所有缓冲池缺省都分配 2

KB空间（有效空间大小为 2KB，4KB，8KB，16KB） ； wash size指示缓冲池的刷洗区大小；

config size 和 run size 显示配置大小和目前使用的大小，该两值在 2KB 的缓冲池中是不同 的（因为不能显式配置其大小 ） ，而对于其他缓冲池 ,若试图在缓冲池间移动空间而一些空间又 不能被释放时，上述两值也不同。

Cache Name Status Type Config

Value Run value default data cache Active

Default 0.00MB 59.36MB

Total 0.00MB 59.36MB

Cache:default data cache, status:

Active, Type:Default

Config Size:0.00MB, Run size:59.36MB

IO Size Wash Size Config Size Run Size

2KB 512KB 0.00MB 59.36MB

（2） 配置数据缓冲区：

配置数据缓冲区有两种途径 : 一是使用过程 sp_cacheconfig 和 sp_poolconfig 进行交互式

配置 ,二是编辑配置文件中的 total memory 参数后重启使之生效。

A 、交互式配置：

创建命名缓冲区（新创建的命名缓冲区从缺省缓冲区 default data cache中分配空间）

每次执行sp_cacheconfig或sp_poolconfig时,SQL Server都将新的缓冲区或缓冲池信息写入 配置文件中并将文件的旧版本拷贝到一个备份文件中

志。

,给出备份文件名的信息被送至错误日

创建一个新的名为 pubs_cache的10MB缓冲区指令如下，大小单位除MB外,还可为P页,

KB,GB,（默认为KB）。该指令将改变系统表并将新值写入配置文件但暂不激活缓冲区，要使 其生效须重启 SQL Server。

sp_cacheconfig pubs_cache,"10MB"

在重启SQL Server使新值生效前，可先用sp_cacheconfig “ pubs_cache查看配置信息如 下,其中的status列值为pend/act说明该缓冲区的配置是挂起的，要待重启后生效，故Config v alue（配置的值）和Run value（正在使用的值）不同：

Cache Name Status Type Config

Value Run value pubs_cache pend/act mixed 10.00MB 00.00MB

Total 10.00MB 00.00MB

重启后再允许不带缓冲区名参数的 sp_cacheconfig,则信息改变如下，可见pubs_cache已

被激活，默认缓冲区从其空间中分出了 10MB给该缓冲区。可利用sp_cacheconfig过程设置默

认数据缓冲区的最小值 ,该部分空间将被锁定给默认数据缓冲区。例如设置默认数据缓冲区

大小最少不低于 25MB 则指令为 :

sp_cacheconfig "default data cache" "25M"

Cache Name Status Type Config Value Run value default data cache Active Default

0.00MB 49.28MB pubs_cache Active mixed 10.00MB 10.00MB

Total 10.00MB 59.28MB

Cache:default data cache, status:

Active, Type:Default

Config Size:0.00MB, Run size:49.28MB IO Size Wash Size Config Size Run Size

2KB 512KB 0.00MB 49.28MB

Cache:pubs_cache, status:Active, Type:mixed

Config Size:10.00MB, Run size:10.00MB

IO Size Wash Size Config Size Run Size

2KB 512KB 0.00MB 10.00MB 若要保留一个只用于事务日志的缓冲区的类型 ,可以将缓冲区的类型设为

“logonly 如”创,

建 “logonly 类型的 pubs_log 缓冲区为 sp_cacheconfig pubs_log, “7MB , “logonly下示为该缓 冲区在重启前的状态。

Cache Name Status Type Config

Value Run value pubs_cache pend/act log only

7.00MB 0.00MB

Total 7.00MB 0.00MB

创建命名缓冲区后一般都须将某些对象捆绑到该缓冲区上。 若建立命名缓冲区后未将任 何对象捆绑到该命名缓冲区上， 则该命名缓冲区所占内存将被浪费， 因为任何未指定缓冲区 的对象都将缺省使用默认数据缓冲区 default data cache。

3、将数据缓冲区划分为缓冲池

建立数据缓冲区后，可将其划分成缓冲池,各不同的缓冲池具有不同的 I/O大小，当SQL S

erver 进行大 I/O 操作时 ,可将许多页一次读入缓冲区中 ,故可提高 I/O 效率。这些页总是被当 作一个单元进行处理

，它们共存于缓冲区中

，作为一个单元被写到磁盘中。缓冲池的页大小可 为 2KB ，4KB ，8KB ， 16KB， 缓冲池的总大小不能低于 512KB 。所有关于缓冲池的配置都是 动态的，无

需重启SQL Server即可生效。SQL Server内2KB页缓冲池的个数必须保持一个 合理的值，否则将会影响

t 及某些 dbcc 命令。

SQL Server性能，因为某些命令只能使用 2K的I/O块如disk ini

在pubs_cache数据缓冲区中创建一个每页 16KB，总空间为7MB的缓冲池，指令如下。通

常，创建命名数据缓冲区时

，其空间总是被默认划分为每页 2KB 的缓冲池

，创建其它页大小的

缓冲池时

，该 2KB 页缓冲池空间被部分分配给新缓冲池。 下例中 2KB 页大小的缓冲池原共有

10MB 空间

，分配给 7MB 给 16KB 页的新缓冲池后

，2KB 页缓冲池还剩 3MB 空间。

>sp_poolconfig pubs_cache，

"7M"，"16K"

>go

>sp_cacheconfig pubs_cache

Cache Name Status Type Config

Value Run value pubs_cache Active mixed 10.00MB 10.00MB

Total 10.00MB 10.00MB

Cache:pubs_cache, status:Active, Type:mixed

Config Size:10.00MB, Run size:10.00MB

IO Size Wash Size Config Size Run Size

2KB 512KB 0.00MB 3.00MB

16KB 1424KB 7.00MB 7.00MB

> 在默认数据缓冲区中也可创建缓冲池 ,如创建 16KB 缓冲池 ,空间大小为 8MB, 则指令为 :

sp_poolconfig "default data cache","8M","16K" 。

若要为一个数据库的事务日志创建缓冲区 ,应配置缓冲区中大部分空间来匹配日志 I/O

的大小。默认值为 4KB,若没有4KB的可用缓冲池，SQL Server就为日志使用2KB的I/O。 日志I/O的大小可通过系统过程 sp_logiosize来改变，每个数据库的日志I/O大小会在SQL S erver启动后的错误日志中报告，也可通过使用数据库和执行不带参数的过程。

sp_logiosize 来检查一个数据库的日志 I/O 大小。如为 pubs_log 缓冲区配置 4KB 页大小 的缓冲池指令为 sp_poolconfig pubs_log，"3MB"，"4KB"， 也可在默认数据缓冲区中创建一个 4 KB 页缓冲池

，以供未被捆绑至其它缓冲区的任何数据库的事务日志来使用 :

sp_poolconfig "default data cache"，"2.5MB"，"4KB" 。

此外，可修改缓冲池大小，如从 16K 页 I/O 的缓冲池中取出 1MB 空间增加到 4KB 页 I /O 缓冲池中 :sp_poolconifg pub_cache，"1MB"，"4KB"，"16KB"

4、缓冲区悃绑操作

系统管理员把数据缓冲区分成独立的命名数据缓冲区后， 可将数据库对象捆绑到这些缓 冲区上， 以控制数据库， 表和索引在内存的驻留。 若建立命名缓冲区后未将任何对象捆绑到 该命名缓冲区上， 则该命名缓冲区所占内存将被浪费， 因为任何未指定缓冲区的对象都将缺 省使用默认数据缓冲区 default data cache。为了将任何系统表，包括事务日志 syslogs捆绑

SQL Server。 至缓冲区中，数据库须处于单用户模式。对象被捆绑后立即生效而无需重启

注意

，进行捆绑或删除捆绑操作时， SQL Server 需锁定相应对象，故相应对象上的其它操作

可能有延迟。此外 ,可不删除现有的捆绑而重新捆绑对象。另外

动或有打开的游标时 ,捆绑或删除捆绑的操作将不能进行。

,当被捆绑对象上有脏的读活

1>sp_dboption pubs2,single,true

置

pubs2

库为单用户状态

2>use pubs2

3>checkpoint 4>go

（准备进行数据库对象的捆绑。注意，捆绑对象须在对象所在的库中进行）。

>sp_bindcache pubs_cache,pubs2,titles /将 pubs2 库中的表 titles 捆绑至缓冲区 pubs_cach

e。

>sp_bindcache pubs_cache,pubs2,titles。 titleind /在 titles 上捆绑索引 ,并将 pubs2 库中的 表

titles 捆绑至缓冲区 pubs_cache。

>sp_bindcache pubs_cache,pubs2,"hj。sale_east" /将 pubs2 库中用户 hj 的表 sale_east捆 绑至缓冲区 pubs_cache。

>sp_bindcache pubs_log,pubs2,syslogs /将 pubs2 库的事务日志 syslogs 捆绑至缓冲区 pu

bs_log。

>sp_bindcache pubs_cache,pubs2,au_pix,"text only" /表的 text 和 image 列存储在一个单

独的数据结构中，若将此表捆绑至缓冲区，需加“text参数。

1>sp_dboption pubs3,single,

true

置

pubs3

库为单用户状态

2>use pubs3

3>checkpoiot

4>use master

5>go

准备进行数据库的捆绑，注意，捆绑数据库须在

master

库中进行。

>sp_bind

cache tempdb_cache,tempdb

将数据库 tempdb 捆绑至缓冲区 tempdb_cache。

可通过 sp_helpcache 过程查询所有或指定缓冲区及其中被捆绑对象的捆绑信息。 如下例 示,其

注意，被捆绑到日志缓冲区上的对象只能是 syslogs 表。

中status列报告缓冲区的捆绑是（“『否（“I ”）。若数据库或对象被捆绑至缓冲池而 该缓冲区已被删除 ,则捆绑信息仍保留在系统表中但缓冲区捆绑被标记无效，所有无效的捆 绑对象均使用默认的数据库缓冲区。 若随后用与被删除的缓冲区相同的名字创建了另一个缓 冲区，则当该缓冲区通过重启

SQL Server 而被激活时 ,上述捆绑又将变为有效。删除缓冲区 捆绑可用两个过程 :sp_unbindcache

用于删除缓冲区中对一个实体的捆绑；

l 则用于删除缓冲区中对所有对象的捆绑 ,但若捆绑至缓冲区的数据库超过

sp_unbindcache_al

8 个，或数据库的

对象超过8个，就不能使用sp_unbindcache_alI,此情况下,必须使用sp_unbindcache删除单个 的数据库或对象，使捆绑的数据库不超过 8个。此外，当删除缓冲区对一个对象的捆绑时， 内存中所有当前的页都被从缓冲区中清除。

>sp_unbindcache pubs2 /删除对数据库 pubs2 的捆绑。

>sp_unbindcache pubs2,titles /删除对 pubs2 库中对 titles 表的捆绑。

>sp_unbindcache pubs2,titles,titleidind /铲除对 pubs2 库中 titles 表上 titleidind 索引的捆 绑。

可通过 sp_cacheconfig 指定一个新的空间以增加或减少指定缓冲区的大小， 所有新增空 间都将从缺省数据缓冲区 default data cache内的2KB缓冲池中被加到指定缓冲区内的 2K

页缓冲池中，所有减少的空间亦从指定缓冲区内的 2KB 页缓冲池中被释放到缺省数据库缓 冲区default data cache内的2KB缓冲池中。

>sp_cacheconfig pubs_cache

Cache Name Status Type Config

Value Run value pubs_cache Active mixed 10.00MB 10.00MB

Total 10.00MB 10.00MB

Cache:pubs_cache, status:Active, Type:mixed Config Size:10.00MB, Run

size:10.00MB

IO Size Wash Size Config Size Run Size 2KB 720KB 0.00MB 3.00MB

4KB 1024KB 4.00MB 4.00MB

16KB 1424KB 3.00MB 3.00MB

现以 sp_cacheconfig pubs_cache, “20MB 后重启，则改变如下

>sp_cacheconfig pubs_cache

Cache Name Status Type Config Value Run value pubs_cache Active mixed

20.00MB 20.00MB

Total 20.00MB 20.00MB

Cache:pubs_cache, status:Active, Type:mixed

Config Size:20.00MB, Run size:20.00MB

IO Size Wash Size Config Size Run Size

2KB 512KB 0.00MB 13.00MB

4KB 1024KB 4.00MB 4.00MB

16KB 1424KB 3.00MB 3.00MB

返回 pubs_log 缓冲区报告

>sp_cacheconfig pubs_log Cache Name Status Type Config Value Run value

pubs_log Active log only 7.00MB 7.00MB

Total 7.00MB 7.00MB

Cache:pubs_log, status:Active, Type:log only

Config Size:7

。00MB, Run size:7.00MB IO Size Wash Size Config Size Run Size

2KB 512KB 0.00MB 4.00MB

4KB 1024KB 3.00MB 3.00MB

再运行以指令 sp_cacheconfig pubs_log, “6MB后重启，则改变为

>sp_cacheconfig pubs_log

Cache Name Status Type Config Value Run value pubs_log Active log only 6.00MB

6.00MB

Total 6.00MB 6.00MB

Cache:pubs_log, status:Active, Type:log only

Config Size:6

。

00MB, Run size:6

。

00MB

IO Size Wash Size Config Size Run Size 2KB 512KB 0.00MB 3.00MB

4KB 1024KB 3.00MB 3.00MB

欲彻底删除一个数据缓冲区，将其大小复位为 0 可使用 sp_cacheconfig pubs_log, “ 0。” 则将缓冲区状态变为 “pend/del,”重启SQL Server后此改变生效。在执行该操作前，缓冲区

I/O 操作。若有对象捆绑于 需保持激活状态 ,捆绑至缓冲区的所有对象仍使用该缓冲区执行

待删除的数据缓冲区 ,则 SQL Server 重启后，缓冲区的捆绑被标记为无效，所有无效缓冲区 捆绑的对象都使用默认数据缓冲区。当捆绑被标记为无效时，警告信息将被输入错误日志。 注意 ,默认数据缓冲区不能删除。