详细讲述SQLSERVER内存和缓冲区管理.docx
《详细讲述SQLSERVER内存和缓冲区管理.docx》由会员分享，可在线阅读，更多相关《详细讲述SQLSERVER内存和缓冲区管理.docx（12页珍藏版）》请在冰点文库上搜索。
详细讲述SQLSERVER内存和缓冲区管理
详细讲述SQLSERVER内存和缓冲区管理
为SQLServer提供的可用内存越多，SQLServer就有越多的资源使用于缓冲区，即可减少服务器从磁盘读取数据以获取静态信息或编译过程计划的次数。
调整SQLServer所占内存可通过在服务器配置文件中设置totalmemory参数进行，该参数的值指定了SQLServer在启动时所需的内存总量，如若该值为10000页，则SQLServer在启动时就将试图获得10000*2KB=19.5MB的内存，若不能满足则启动失败。
注意：
若操作系统在进程的整个生命周期中支持动态内存分配，则可在SQLServer启动后为其分配额外的内存。
SQLServer启动时将内存分配给SQLServer可执行代码，SQLServer使用的静态内存，用户可配置参数占用的内存及不驻留在缓存上的数据结构，剩余的内存分配给两种SQLSe
rver缓冲区即：
数据缓冲区和过程缓冲区。
两缓冲区的大小对整个系统的性能影响很大，在一个开发系统上可能要增加过多端程缓冲区的专用内存，而在一生产系统上则需减少过程缓冲区大小以便获得更大的数据缓冲区。
1、确定缓冲区大小
在totalmemory配置的总空间中，首先要除去可爱执行代码所占空间，其大小因平台和版本不同而异，可使用sp_configure查询executablecodesize参数的值得知，一般为3-4MB；其次需除去内部结构所占空间，内部结构分成内核结构和服务器结构，亦可将此部分内存看成静态开销和用户配置参数占用两个部分，前者大小不受用户可配置参数的影响，一般为2-3MB，而后者即用户可配置参数的大小则取决于配置参数的类型及其值的大小，包括numberofuserconnections,numberofopendatabases,numberofdevices,numberofopenobjects,numberoflocks等，要得到这些用户配置参数使用内存大小的精确估计，可调用不带选项的sp_configure以显示所有参数和每个参数使用的内存量，其和即是用户配置参数所占用内存。
除去上述占用，剩余内存将以procedurecachepercent配置参数的值按比例分配给过程缓冲区和数据缓冲区，例若值为20，即表示剩余内存的20%分配给过程缓冲区而80%分配
错误日志的内存信息，其中准确地说明了分配给过程缓冲区和数据缓冲区的数据量，以及多少个过程或其他编译对象能同时驻留在缓冲区中，如相关信息为：
Server:
Numberofprocbuffersassocated:
556
Server:
Numberofblocksleftforprocheaders:
629
Server:
Memoryallocatedforthedefaultdatacache:
4144kb
前两行为过程缓冲区信息，过程缓冲区总大小是分配给过程缓冲区的内存（第一行）及分
配给过程头（即存放编译对象如存储过程的地方，根据被存储对象的大小，可能需要一个或多个过程头）的内存（第二行）之和，可存储在过程缓冲区中的编译对象受到此二者中较小者的限制。
上述信息中，第一行指明了分配给过程缓冲区的缓冲区数量为556KB，每个缓冲区
大小为76B，故过程缓冲区大小即为42256B，合21页；第二行则指明了分配给过程头的空
间为629页，由此可以得出，过程缓冲区总大小为629+21=650页，合1.27MB。
信息中第三行则指明了分配给默认数据库缓冲区的空间大小。
此外，若配置了命名缓冲区，则相关信息还将包含每个命名缓冲区的信息，再加上这些命名缓冲区所占内存大小即得到总的数据缓冲区大小。
欲知数据缓冲区大小还可通过sp_helpcache过程得到每个缓冲区和
捆绑到这些缓冲区中的对象以及与不同缓冲区大小相关的开销的详细信息。
2、缓冲区管理
数据缓冲区保存SQLServer当前使用和最近使用过的数据页,索引页和日志页。
初装S
QLServer时,会产生一个默认的数据缓冲区。
SQLServer允许系统管理员把数据缓冲区分
成独立的命名数据缓冲区,然后可把数据库或数据库对象捆绑到这些命名缓冲区上,以对缓冲
区空间进行更合理的组织,控制数据库,表和索引在内存的驻留。
另外,系统管理员还可在缓冲
区内创
建缓冲池用于执行大块的磁盘I/O,改善数据查询性能，减少磁盘I/O。
有关数据库缓冲区的指令有:
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报告为查询进行的读操作数量。
setprefetchon/off允许或禁止某个会话的预先提取。
（1）查询数据库缓冲区的信息：
查看默认数据缓冲区的大小,可使用指令sp_cacheconfig"defaultdatacache",显示结果如下,由两部分构成,上部分报告每个缓冲区中的内存池配置信息并总计了所有配置缓冲区的大小；下部分提供了每个缓冲区中的其他详细信息。
pend/act时表示该缓冲区刚建立,重启后将被激活；值为active时表示该缓冲区目前正处于激活状态；值为pend/del时表示该缓冲区处于激活状态,但在服务器重启后将被删除）；type指
示缓冲区是否可以存储数据（“mixed'和）日志页（“logonly只有默认缓冲区才会有“default
类型,且默认的数据缓冲区类型不能改变,其他类型缓冲区也不能改为“default。
”
configvalue显示下次SQLServer重启后缓冲区的大小，在此情况下，默认缓冲区不能显式配置,故信息中其大小为；runvalue显示SQLServer目前使用的大小，对于默认数据缓冲区,该值将作为未显示配置到其他缓冲区中的所有数据缓冲区空间。
第二部分输出各列含义为:
IOsize显示缓冲池中的缓存大小，所有缓冲池缺省都分配2
KB空间（有效空间大小为2KB，4KB，8KB，16KB）；washsize指示缓冲池的刷洗区大小；configsize和runsize显示配置大小和目前使用的大小，该两值在2KB的缓冲池中是不同的（因为不能显式配置其大小），而对于其他缓冲池,若试图在缓冲池间移动空间而一些空间又不能被释放时，上述两值也不同。
CacheNameStatusTypeConfig
ValueRunvaluedefaultdatacacheActive
Default0.00MB59.36MB
Total0.00MB59.36MB
Cache:
defaultdatacache,status:
Active,Type:
Default
ConfigSize:
0.00MB,Runsize:
59.36MB
IOSizeWashSizeConfigSizeRunSize
2KB512KB0.00MB59.36MB
（2）配置数据缓冲区：
配置数据缓冲区有两种途径:
一是使用过程sp_cacheconfig和sp_poolconfig进行交互式
配置,二是编辑配置文件中的totalmemory参数后重启使之生效。
A、交互式配置：
创建命名缓冲区（新创建的命名缓冲区从缺省缓冲区defaultdatacache中分配空间）
每次执行sp_cacheconfig或sp_poolconfig时,SQLServer都将新的缓冲区或缓冲池信息写入配置文件中并将文件的旧版本拷贝到一个备份文件中,给出备份文件名的信息被送至错误日
志。
创建一个新的名为pubs_cache的10MB缓冲区指令如下，大小单位除MB外,还可为P页,KB,GB,（默认为KB）。
该指令将改变系统表并将新值写入配置文件但暂不激活缓冲区，要使其生效须重启SQLServer。
sp_cacheconfigpubs_cache,"10MB"
在重启SQLServer使新值生效前，可先用sp_cacheconfig“pubs_cache查看配置信息如下,其中的status列值为pend/act说明该缓冲区的配置是挂起的，要待重启后生效，故Configvalue（配置的值）和Runvalue（正在使用的值）不同：
CacheNameStatusTypeConfig
ValueRunvaluepubs_cachepend/actmixed10.00MB00.00MB
Total10.00MB00.00MB
重启后再允许不带缓冲区名参数的sp_cacheconfig,则信息改变如下，可见pubs_cache已
被激活，默认缓冲区从其空间中分出了10MB给该缓冲区。
可利用sp_cacheconfig过程设置默
认数据缓冲区的最小值,该部分空间将被锁定给默认数据缓冲区。
例如设置默认数据缓冲区
大小最少不低于25MB则指令为:
sp_cacheconfig"defaultdatacache""25M"
CacheNameStatusTypeConfigValueRunvaluedefaultdatacacheActiveDefault0.00MB49.28MBpubs_cacheActivemixed10.00MB10.00MB
Total10.00MB59.28MB
Cache:
defaultdatacache,status:
Active,Type:
Default
ConfigSize:
0.00MB,Runsize:
49.28MBIOSizeWashSizeConfigSizeRunSize
2KB512KB0.00MB49.28MB
Cache:
pubs_cache,status:
Active,Type:
mixed
ConfigSize:
10.00MB,Runsize:
10.00MB
IOSizeWashSizeConfigSizeRunSize
2KB512KB0.00MB10.00MB若要保留一个只用于事务日志的缓冲区的类型,可以将缓冲区的类型设为“logonly如”创,
建“logonly类型的pubs_log缓冲区为sp_cacheconfigpubs_log,“7MB,“logonly下示为该缓冲区在重启前的状态。
CacheNameStatusTypeConfig
ValueRunvaluepubs_cachepend/actlogonly
7.00MB0.00MB
Total7.00MB0.00MB
创建命名缓冲区后一般都须将某些对象捆绑到该缓冲区上。
若建立命名缓冲区后未将任何对象捆绑到该命名缓冲区上，则该命名缓冲区所占内存将被浪费，因为任何未指定缓冲区的对象都将缺省使用默认数据缓冲区defaultdatacache。
3、将数据缓冲区划分为缓冲池
建立数据缓冲区后，可将其划分成缓冲池,各不同的缓冲池具有不同的I/O大小，当SQLS
erver进行大I/O操作时,可将许多页一次读入缓冲区中,故可提高I/O效率。
这些页总是被当作一个单元进行处理，它们共存于缓冲区中，作为一个单元被写到磁盘中。
缓冲池的页大小可为2KB，4KB，8KB，16KB，缓冲池的总大小不能低于512KB。
所有关于缓冲池的配置都是动态的，无需重启SQLServer即可生效。
SQLServer内2KB页缓冲池的个数必须保持一个合理的值，否则将会影响SQLServer性能，因为某些命令只能使用2K的I/O块如diskini
t及某些dbcc命令。
在pubs_cache数据缓冲区中创建一个每页16KB，总空间为7MB的缓冲池，指令如下。
通
常，创建命名数据缓冲区时，其空间总是被默认划分为每页2KB的缓冲池，创建其它页大小的
缓冲池时，该2KB页缓冲池空间被部分分配给新缓冲池。
下例中2KB页大小的缓冲池原共有10MB空间，分配给7MB给16KB页的新缓冲池后，2KB页缓冲池还剩3MB空间。
>sp_poolconfigpubs_cache，
"7M"，"16K"
>go
>sp_cacheconfigpubs_cache
CacheNameStatusTypeConfig
ValueRunvaluepubs_cacheActivemixed10.00MB10.00MB
Total10.00MB10.00MB
Cache:
pubs_cache,status:
Active,Type:
mixed
ConfigSize:
10.00MB,Runsize:
10.00MB
IOSizeWashSizeConfigSizeRunSize
2KB512KB0.00MB3.00MB
16KB1424KB7.00MB7.00MB>在默认数据缓冲区中也可创建缓冲池,如创建16KB缓冲池,空间大小为8MB,则指令为:
sp_poolconfig"defaultdatacache","8M","16K"。
若要为一个数据库的事务日志创建缓冲区,应配置缓冲区中大部分空间来匹配日志I/O
的大小。
默认值为4KB,若没有4KB的可用缓冲池，SQLServer就为日志使用2KB的I/O。
日志I/O的大小可通过系统过程sp_logiosize来改变，每个数据库的日志I/O大小会在SQLServer启动后的错误日志中报告，也可通过使用数据库和执行不带参数的过程。
sp_logiosize来检查一个数据库的日志I/O大小。
如为pubs_log缓冲区配置4KB页大小的缓冲池指令为sp_poolconfigpubs_log，"3MB"，"4KB"，也可在默认数据缓冲区中创建一个4KB页缓冲池，以供未被捆绑至其它缓冲区的任何数据库的事务日志来使用:
sp_poolconfig"defaultdatacache"，"2.5MB"，"4KB"。
此外，可修改缓冲池大小，如从16K页I/O的缓冲池中取出1MB空间增加到4KB页I/O缓冲池中:
sp_poolconifgpub_cache，"1MB"，"4KB"，"16KB"
4、缓冲区悃绑操作
系统管理员把数据缓冲区分成独立的命名数据缓冲区后，可将数据库对象捆绑到这些缓冲区上，以控制数据库，表和索引在内存的驻留。
若建立命名缓冲区后未将任何对象捆绑到该命名缓冲区上，则该命名缓冲区所占内存将被浪费，因为任何未指定缓冲区的对象都将缺省使用默认数据缓冲区defaultdatacache。
为了将任何系统表，包括事务日志syslogs捆绑
至缓冲区中，数据库须处于单用户模式。
对象被捆绑后立即生效而无需重启SQLServer。
注意，进行捆绑或删除捆绑操作时，SQLServer需锁定相应对象，故相应对象上的其它操作
动或有打开的游标时,捆绑或删除捆绑的操作将不能进行。
1>sp_dboptionpubs2,single,true置pubs2库为单用户状态2>usepubs23>checkpoint4>go
（准备进行数据库对象的捆绑。
注意，捆绑对象须在对象所在的库中进行）。
>sp_bindcachepubs_cache,pubs2,titles/将pubs2库中的表titles捆绑至缓冲区pubs_cache。
>sp_bindcachepubs_cache,pubs2,titles。
titleind/在titles上捆绑索引,并将pubs2库中的表titles捆绑至缓冲区pubs_cache。
>sp_bindcachepubs_cache,pubs2,"hj。
sale_east"/将pubs2库中用户hj的表sale_east捆绑至缓冲区pubs_cache。
>sp_bindcachepubs_log,pubs2,syslogs/将pubs2库的事务日志syslogs捆绑至缓冲区pubs_log。
>sp_bindcachepubs_cache,pubs2,au_pix,"textonly"/表的text和image列存储在一个单
独的数据结构中，若将此表捆绑至缓冲区，需加“text参数。
1>sp_dboptionpubs3,single,
true置pubs3库为单用户状态
2>usepubs3
3>checkpoiot
4>usemaster
5>go准备进行数据库的捆绑，注意，捆绑数据库须在master库中进行。
>sp_bind
cachetempdb_cache,tempdb
将数据库tempdb捆绑至缓冲区tempdb_cache。
可通过sp_helpcache过程查询所有或指定缓冲区及其中被捆绑对象的捆绑信息。
如下例示,其中status列报告缓冲区的捆绑是（“『否（“I”）。
若数据库或对象被捆绑至缓冲池而该缓冲区已被删除,则捆绑信息仍保留在系统表中但缓冲区捆绑被标记无效，所有无效的捆绑对象均使用默认的数据库缓冲区。
若随后用与被删除的缓冲区相同的名字创建了另一个缓冲区，则当该缓冲区通过重启SQLServer而被激活时,上述捆绑又将变为有效。
删除缓冲区捆绑可用两个过程:
sp_unbindcache用于删除缓冲区中对一个实体的捆绑；sp_unbindcache_al
l则用于删除缓冲区中对所有对象的捆绑,但若捆绑至缓冲区的数据库超过8个，或数据库的
对象超过8个，就不能使用sp_unbindcache_alI,此情况下,必须使用sp_unbindcache删除单个的数据库或对象，使捆绑的数据库不超过8个。
此外，当删除缓冲区对一个对象的捆绑时，内存中所有当前的页都被从缓冲区中清除。
>sp_unbindcachepubs2/删除对数据库pubs2的捆绑。
>sp_unbindcachepubs2,titles/删除对pubs2库中对titles表的捆绑。
>sp_unbindcachepubs2,titles,titleidind/铲除对pubs2库中titles表上titleidind索引的捆绑。
可通过sp_cacheconfig指定一个新的空间以增加或减少指定缓冲区的大小，所有新增空间都将从缺省数据缓冲区defaultdatacache内的2KB缓冲池中被加到指定缓冲区内的2K
页缓冲池中，所有减少的空间亦从指定缓冲区内的2KB页缓冲池中被释放到缺省数据库缓冲区defaultdatacache内的2KB缓冲池中。
>sp_cacheconfigpubs_cache
CacheNameStatusTypeConfig
ValueRunvaluepubs_cacheActivemixed10.00MB10.00MB
Total10.00MB10.00MB
Cache:
pubs_cache,status:
Active,Type:
mixedConfigSize:
10.00MB,Runsize:
10.00MB
IOSizeWashSizeConfigSizeRunSize2KB720KB0.00MB3.00MB
4KB1024KB4.00MB4.00MB
16KB1424KB3.00MB3.00MB
现以sp_cacheconfigpubs_cache,“20MB后重启，则改变如下
>sp_cacheconfigpubs_cache
CacheNameStatusTypeConfigValueRunvaluepubs_cacheActivemixed20.00MB20.00MB
Total20.00MB20.00MB
Cache:
pubs_cache,status:
Active,Type:
mixed
ConfigSize:
20.00MB,Runsize:
20.00MB
IOSizeWashSizeConfigSizeRunSize
2KB512KB0.00MB13.00MB
4KB1024KB4.00MB4.00MB
16KB1424KB3.00MB3.00MB
返回pubs_log缓冲区报告
>sp_cacheconfigpubs_logCacheNameStatusTypeConfigValueRunvaluepubs_logActivelogonly7.00MB7.00MB
Total7.00MB7.00MB
Cache:
pubs_log,status:
Active,Type:
logonly
ConfigSize:
7。
00MB,Runsize:
7.00MBIOSizeWashSizeConfigSizeRunSize2KB512KB0.00MB4.00MB
4KB1024KB3.00MB3.00MB
再运行以指令sp_cacheconfigpubs_log,“6MB后重启，则改变为
>sp_cacheconfigpubs_log
CacheNameStatusTypeConfigValueRunvaluepubs_logActivelogonly6.00MB6.00MB
Total6.00MB6.00MB
Cache:
pubs
升级会员 