本文介绍如何在 SQL Server 中管理和配置数据缓冲区及过程缓冲区，包括确定缓冲区大小、配置数据缓冲区、创建缓冲池以及进行缓冲区捆绑等操作。
内存和缓冲区管理
-------------------------------------
为SQL SERVER提供的可用内存越多，SQL SERVER就有越多的资源使用于缓冲区，即可减少服务器
从磁盘读取数据以获取静态信息或编译过程计划的次数。调整SQL SERVER所占内存可通过在服务器配
置文件中设置total memory参数进行，该参数的值指定了SQL SERVER在启动时所需的内存总量，如若
该值为10000页，则SQL SERVER在启动时就将试图获得10000*2K=19.5M的内存，若不能满足则启动失败。
注意，若操作系统在进程的整个生命周期中支持动态内存分配，则可在SQL SERVER启动后为其分配额
外的内存。
SQL SERVER启动时将内存分配给SQL SERVER可执行代码，SQL SERVER使用的静态内存，用户可配
置参数占用的内存及不驻留在缓存上的数据结构，剩余的内存分配给两种SQL SERVER缓冲区即：数据
缓冲区和过程缓冲区。两缓冲区的大小对整个系统的性能影响很大，在一个开发系统上可能要增加过多端
程缓冲区的专用内存，而在一生产系统上则需减少过程缓冲区大小以便获得更大的数据缓冲区。
1。 确定缓冲区大小
在total memory配置的总空间中，首先要除去可爱执行代码所占空间，其大小因平台和版本
不同而异，可使用sp_configure查询executable codesize参数的值得知，一般为3-4MB；其次需
除去内部结构所占空间，内部结构分成内核结构和服务器结构，亦可将此部分内存看成静态开销
和用户配置参数占用两个部分，前者大小不受用户可配置参数的影响，一般为2-3MB，而后者即用
户可配置参数的大小则取决于配置参数的类型及其值的大小，包括number of user connections,
number of open databases,number of devices,number of open 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
前两行为过程缓冲区信息，过程缓冲区总大小是分配给过程缓冲区的内存(第一行)及分配给过
程头(即存放编译对象如存储过程的地方，根据被存储对象的大小，可能需要一个或多个过程头)
的内存(第二行)之和，可存储在过程缓冲区中的编译对象受到此二者中较小者的限制。上述信息
中，第一行指明了分配给过程缓冲区的缓冲区数量为556，每个缓冲区大小为76B，故过程缓冲区
大小即为42256B，合21页；第二行则指明了分配给过程头的空间为629页，由此可以得出，过程
缓冲区总大小为629+21=650页，合1.27M。
信息中第三行则指明了分配给默认数据库缓冲区的空间大小。此外，若配置了命名缓冲区，则
相关信息还将包含每个命名缓冲区的信息，再加上这些命名缓冲区所占内存大小即得到总的数据
缓冲区大小。欲知数据缓冲区大小还可通过sp_helpcache过程得到每个缓冲区和捆绑到这些缓冲
区中的对象以及与不同缓冲区大小相关的开销的详细信息。
2。 缓冲区管理
数据缓冲区保存SQL SERVER当前使用和最近使用过的数据页,索引页和日志页.初装SQL SERVER
时,会产生一个默认的数据缓冲区.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
指示缓冲区是否可以存储数据("mixed")和日志页("logonly"),只有默认缓冲区才会有
"default"类型,且默认的数据缓冲区类型不能改变,其他类型缓冲区也不能改为"default";
config value显示下次SQL SERVER重启后缓冲区的大小,在此情况下,默认缓冲区不能显式
配置,故信息中其大小为0;run value显示SQL SERVER目前使用的大小,对于默认数据缓冲
区,该值将作为未显示配置到其他缓冲区中的所有数据缓冲区空间.
第二部分输出各列含义为:IO size显示缓冲池中的缓存大小,所有缓冲池缺省都分配2K
空间(有效空间大小为2K,4K,8K,16K);wash size指示缓冲池的刷洗区大小;config size和
run size显示配置大小和目前使用的大小,该两值在2K的缓冲池中是不同的(因为不能显式
配置其大小),而对于其他缓冲池,若试图在缓冲池间移动空间而一些空间又不能被释放时,
上述两值也不同.
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的10M缓冲区指令如下,大小单位除M外,还可为P页,K,G,
(默认为K).该指令将改变系统表并将新值写入配置文件但暂不激活缓冲区,要使其生效
须重启SQL SERVER.
sp_cacheconfig pubs_cache,"10M"
在重启SQL SERVER使新值生效前,可先用sp_cacheconfig "pubs_cache"查看配置信息
如下,其中的status列值为pend/act说明该缓冲区的配置是挂起的,要待重启后生效,故
Config value(配置的值)和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
已被激活,默认缓冲区从其空间中分出了10M给该缓冲区.可利用sp_cacheconfig过程设置
默认数据缓冲区的最小值,该部分空间将被锁定给默认数据缓冲区.例如设置默认数据缓
冲区大小最少不低于25M则指令为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,"7M","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.
>; 将数据缓冲区划分为缓冲池
建立数据缓冲区后,可将其划分成缓冲池,各不同的缓冲池具有不同的I/O大小,当
SQL SERVER进行大I/O操作时,可将许多页一次读入缓冲区中,故可提高I/O效率.这些
页总是被当作一个单元进行处理,它们共存于缓冲区中,作为一个单元被写到磁盘中.
缓冲池的页大小可为2K,4K,8K,16K,缓冲池的总大小不能低于512K.所有关于缓冲池
的配置都是动态的,无需重启SQL SERVER即可生效.SQL SERVER内2K页缓冲池的个数
必须保持一个合理的值，否则将会影响SQL SERVER性能，因为某些命令只能使用2K
的I/O块如disk init及某些dbcc命令。
在pubs_cache数据缓冲区中创建一个每页16K,总空间为7MB的缓冲池,指令如下.通
常,创建命名数据缓冲区时,其空间总是被默认划分为每页2K的缓冲池,创建其它页大
小的缓冲池时,该2K页缓冲池空间被部分分配给新缓冲池.下例中2K页大小的缓冲池
原共有10M空间,分配给7M给16K页的新缓冲池后,2K页缓冲池还剩3M空间.
>;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
在默认数据缓冲区中也可创建缓冲池,如创建16K缓冲池,空间大小为8M,则指令为:
sp_poolconfig "default data cache","8M","16K".
若要为一个数据库的事务日志创建缓冲区,应配置缓冲区中大部分空间来匹配日志
I/O的大小.默认值为4K,若没有4K的可用缓冲池,SQL SERVER就为日志使用2K的I/O.
日志I/O的大小可通过系统过程sp_logiosize来改变,每个数据库的日志I/O大小会在
SQL SERVER启动后的错误日志中报告,也可通过使用数据库和执行不带参数的过程
sp_logiosize来检查一个数据库的日志I/O大小.如为pubs_log缓冲区配置4K页大小
的缓冲池指令为sp_poolconfig pubs_log,"3M","4K",也可在默认数据缓冲区中创
建一个4K页缓冲池,以供未被捆绑至其它缓冲区的任何数据库的事务日志来使用:
sp_poolconfig "default data cache","2.5M","4K".
此外，可修改缓冲池大小，如从16K页I/O的缓冲池中取出1M空间增加到4K页I/O缓
冲池中:sp_poolconifg pub_cache,"1M","4K","16K"
>; 缓冲区悃绑操作
系统管理员把数据缓冲区分成独立的命名数据缓冲区后,可将数据库对象捆绑到这
些缓冲区上,以控制数据库,表和索引在内存的驻留.若建立命名缓冲区后未将任何对
象捆绑到该命名缓冲区上，则该命名缓冲区所占内存将被浪费，因为任何未指定缓
冲区的对象都将缺省使用默认数据缓冲区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_cache.
>;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捆绑至缓冲区pubs_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_bindcache tempdb_cache,tempdb
将数据库tempdb捆绑至缓冲区tempdb_cache。
注意，被捆绑到日志缓冲区上的对象只能是syslogs表。
可通过sp_helpcache过程查询所有或指定缓冲区及其中被捆绑对象的捆绑信息.如下
例示,其中status列报告缓冲区的捆绑是("V")否("I")有效.若数据库或对象被捆绑至缓
冲池而该缓冲区已被删除,则捆绑信息仍保留在系统表中但缓冲区捆绑被标记无效,所有
无效的捆绑对象均使用默认的数据库缓冲区.若随后用与被删除的缓冲区相同的名字创建
了另一个缓冲区,则当该缓冲区通过重启SQL SERVER而被激活时,上述捆绑又将变为有效.
删除缓冲区捆绑可用两个过程:sp_unbindcache用于删除缓冲区中对一个实体的捆绑;
sp_unbindcache_all则用于删除缓冲区中对所有对象的捆绑,但若捆绑至缓冲区的数据库
超过8个,或数据库的对象超过8个,就不能使用sp_unbindcache_all,此情况下,必须使用
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内的2K缓冲池中被加到指定缓冲区内的2K
页缓冲池中,所有减少的空间亦从指定缓冲区内的2K页缓冲池中被释放到缺省数据库缓冲
区default data cache内的2K缓冲池中.
>;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,"20M"后重启,则改变如下:
>;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,"6M"后重启,则改变为:
>;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重启后,缓冲区的捆绑被标记为无效,所有无效
缓冲区捆绑的对象都使用默认数据缓冲区.当捆绑被标记为无效时,警告信息将被输入
错误日志.注意,默认数据缓冲区不能删除.
原文链接：http://bbs.chinaunix.net/viewthread.php?tid=2303
转载请注明作者名及原文出处
扫一扫
2125
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
