详细讲述 Sybase 内存和缓冲区管理-1

  来源：

---

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

注意：若操作系统在进程的整个周期中支持动态内存分配，则可在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

前两行为过程缓冲区信息，过程缓冲区总大小是分配给过程缓冲区的内存(第一行)及分配给过程头(即存放编译对象如存储过程的地方，根据被存储对象的大小，可能需要一个或多个过程头)的内存(第二行)之和，可存储在过程缓冲区中的编译对象受到此二者中较小者的限制。上述信息中，第一行指明了分配给过程缓冲区的缓冲区数量为556KB，每个缓冲区大小为76B，故过程缓冲区大小即为42256B，合21页；第二行则指明了分配给过程头的空间为629页，由此可以得出，过程缓冲区总大小为629+21=650页，合1.27MB。

信息中第三行则指明了分配给默认数据库缓冲区的空间大小。此外，若配置了命名缓冲区，则相关信息还将包含每个命名缓冲区的信息，再加上这些命名缓冲区所占内存大小即得到总的数据缓冲区大小。欲知数据缓冲区大小还可通过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重启后缓冲区的大小，在此情况下，默认缓冲区不能显式配置,故信息中其大小为；run value显示SQL Server目前使用的大小，对于默认数据缓冲区,该值将作为未显示配置到其他缓冲区中的所有数据缓冲区空间。

第二部分输出各列含义为:IO size显示缓冲池中的缓存大小，所有缓冲池缺省都分配2KB空间(有效空间大小为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 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已被激活,默认缓冲区从其空间中分出了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。