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

详细讲述 SQL SERVER 内存和缓冲区管理2008-05-09为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过程得到每个缓冲区和捆绑到这些缓冲区中的对象以及与不同缓冲区大小相关的开销的详细信息。