浅谈优化SQLServer数据库服务器内存.doc

浅谈优化SQLServer数据库服务器内存配置的策略发布: 2007-6-07 23:59 | 作者: seanhe | 来源: 不详 软件测试论坛讨论 领测软件测试网 农业银行总行1998年以来正式推广了新版网络版综合业务统计信息系统，该系统是基于WindowsNT4.0平台，采用客户服务器模式，以Microsoft SQL Server为基础建立起来的大型数据库应用程序，系统界面友好、操作简便，计算、分析、检索功能非常强大，为保证农业银行系统及时进行纵向和横向业务数据采集、按照不同要求生成统计报表，进行全面业务活动分析提供了强有力的保障。但在这套程序的推广、维护中笔者发现系统有时运行速度较慢，特别是在Win95客户端操作时尤为严重，经过排除网线连接等硬件可能带来的影响后上述问题仍然存在。笔者经过仔细摸索，发现系统对硬、软件的要求较高，为充分发挥设计效能，达到最佳运作效果，需要对计算机硬、软件系统进行较为完备的性能测试与最佳配置，特别是内存配置的好坏对系统的运行速度具有决定性的作用。下面，笔者就如何优化SQLServer数据库服务器的内存配置提出一些认识和看法。 一、有关内存的基本概念1物理内存与虚拟内存WindowsNT使用两类内存：物理内存与虚拟内存。物理内存：作为RAM芯片安装在计算机内部的存储器。虚拟内存：用于模拟RAM芯片功能的磁盘(硬盘)空间，其实质是通过将内存中当前没有使用的部分内容临时存储到磁盘上，使系统可以使用到比机器物理内存更多的内存。2分页和分页文件WindowsNT系统通过使用磁盘空间使得对内存的需求得到部分缓解，从而使用到比物理内存更多内存的技术就称为“交换”或分页，也就是通常所说的虚拟内存技术。通常Windows NT 4.0系统安装时将在引导驱动器上设置一个大小为16MB的交换(分页)文件(pagefile.sys)。二、优化Windows NT 4.0系统内存配置在大多数情况下，为了充分发挥Windows NT 4.0系统效能，内存的作用比起处理器的处理能力更具有影响力，特别是在客户服务器模式环境下更是如此，因为通常在这种环境下并不十分强调处理器的能力，相反却十分注重是否采用足够的内存来满足各个客户的应用需要。此外，为了获得容错功能和保护应用程序，保证应用程序高速运行、充分发挥设计功能都需要有足够多的内存，特别是工业绘图设计和各种工程应用程序更需要占用大量的内存来进行复杂的计算。物理内存(RAM)方便快速的优点显而易见，但由于其价格昂贵，也就不可能做到多多益善了，因此通过合理优化内存配置、扩充虚拟内存提高计算机运算速度也就成了一项很重要的应用技术手段。1保证Windows NT系统基本内存需求Windows NT 4.0系统至小应配置12MB内存，16MB内存基本够用，正常情况下保证NT系统有32MB内存就可以了，因为并不是所有的16MB基本内存在任何时候都被同时使用。如果添加一些服务和应用程序，则对内存的需求就会急剧增大。如：(1)添加网络服务需要4MB内存空间；(2)容错功能和系统保护功能需要8MB内存(如磁盘镜像和分条功能)；(3)进行图形图象处理需要增加16MB内存空间；(4)安装VC、VB开发系统需要增加16MB内存空间；另外，如在Windows NT上构建大型数据库如SYBASE、Microsoft SQL Server等，对内存的需求就更多了。2优化内存性能为了使WindowsNT不至于过分占用较多的内存或者浪费处理器的时间用于换页，可以采用以下方法优化内存性能。(1)减少显示颜色的数量；(2)降低显示分辨率；(3)尽可能不使用或使用位宽度较小的墙纸；(4)关闭不需要的服务程序或驱动程序，尽量不要在服务器上使用其它应用程序。停用服务或驱动程序的操作步骤如下：确定需要停用的服务或驱动程序的名称；从“控制面板”中双击“服务”或“设备”图标；在列表中选择想要停用的服务或设备驱动程序的名称，单击“停止”按钮，这时出现确认操作对话框；选择“是”确认操作，然后关闭对话框完成设置。3优化虚拟内存在对Windows NT虚拟内存进行设置时需要合理确定各个驱动器分页文件的“起始大小”和“最大值”两个参数，它们用于指定分页文件的起始空间和最大空间。下面对这两个参数作一些解释：起始大小：指初始创建该分页文件时的文件大小，单位为MB，根据缺省设置，这个值被设置为系统中的物理内存的大小。最大值：指出该分页文件的最大尺寸，单位为MB。(1)分页文件的设置原则：分页文件起始大小应保留缺省设置，一般情况下请不要改动；分页文件理想的最大尺寸为系统物理内存尺寸的2.5倍至4倍。需要说明的是：如果系统工作时不需要大量内存，请选择靠近下限的值，即用系统物理内存的2.5倍作为这个尺寸的起始值；如果系统工作时需要大量内存，请选择靠近上限的值。(2)Windows NT虚拟内存设置步骤：从“控制面板”中双击“系统”图标；在“系统特性”对话框中单击“性能”标签；在虚拟内存对话框中单击“更改”按钮，这时出现“虚拟内存”对话框，上端的驱动器框逐一列出了 Windows NT所有页面文件的大小；在驱动器列表中，选择需要设置分页文件的驱动器盘符，在“驱动器页面文件大小”对话框中列出了 “起始大小”和“最大值”两个参数栏，填入按照上面的原则确定的数值；单击“设置”确认以上操作，然后依次单击“确定”按钮退出各个对话框，完成设置。(3)Win9598虚拟内存设置。Win9598虚拟内存设置方法、步骤和原则与Windows NT 4.0的设置大致相同，请参照上面Windows NT的设置。4注意事项(1)合理确定分页文件的最大值，根据系统需求随时进行调整，使用过多虚拟内存将导致整个系统处理性能的下降。设置虚拟内存最大值的目的是使用户不必在WindowsNT的交换文件上消耗过多的磁盘空间，通常情况下如果超过了系统需要的最佳值后，生成交换文件的磁盘空间就被浪费了。(2)尽可能设立专用硬盘配置内存交换区，或将交换空间放到主硬盘的另一个分区，同时应将主硬盘的交换文件大小降至16MB，这样主硬盘(分区)仅用来放置操作系统和应用程序，就可以减少交换次数，防止频繁交换耗费大量 CPU时间。(3)虚拟内存技术的确改善了Windows NT系统的性能，但也受到机器硬盘空间大小、硬盘速度、处理器 (CPU)速度的影响，从理想角度出发，要提高计算机的性能就必须减少交换操作的次数，但是没有一个WindowsNT计算机不发生交换，这就要求计算机要有足够的物理内存，以保持最少的交换操作。三、优化Microsoft SQL Server数据库内存配置内存是影响Microsoft SQL Server系统性能的一个重要因素，SQL Server数据库安装时将为具有32MB物理内存的机器缺省配置16MB可用内存，16MB物理内存的机器缺省配置4MB可用内存。应在Microsoft SQL Server数据库安装后进行内存选项(Memory)设置，最大配置值为2GB。为了确定SQL Server系统最适宜的内存需求，可以从总的物理内存中减去Windows NT4.0需要的内存以及其它一些内存需求后综合确定，理想的情况是给SQL Server分配尽可能多的内存，而不产生页面调度。1根据物理内存合理规划SQL Server可用内存在大多数的生产环境中，服务器配备的物理内存是64MB128MB，偶尔也有256MB的，只要配置恰当是完全可以满足SQL Server的内存需求的。下表是笔者关于SQL Server内存分配的建议规划，供参考。物理内存 分配给SQL Server 设置值(单位：2KB) 8MB 4MB 2048 16MB 8MB 4096 32MB 1618MB 81929216 48MB 2834MB 1433617408 64MB 4046MB 2048023552 128MB 100108MB 5120055296 256MB 216226MB 110592115712 512MB 464472MB 237568241664 以下是SQL Server内存选项(Memory)设置方法(1)从Microsoft SQL Server程序集中启动SQL Enterprise Manager；(2)从Server Manager窗口中选择“Server”菜单选项；(3)在“Server”菜单中选择“Configurations”选项；(4)在“Server Configuration”对话框中选择”Configuration”标签，Configuration窗口显示配置选项列表；(5)选中“Memory”项目，在“Current”栏填入新值；(6)停止并重新启动SQLServer服务，使设置生效。2合理扩充虚拟内存、增大SQL Server可用内存当SQL Server系统确实需要扩大可用内存时，应在磁盘空间充足的情况下扩充供虚拟内存，并相应增大 SQL Server可用内存。具体做法是，系统管理员首先扩充服务器的虚拟内存，然后再参考上表增大SQL Server可用内存，关键是要根据系统的负载情况综合决定是否扩充内存、优化配置。3使用tempinRAMSQL Server使用tempdb临时数据库作为一些查询连接操作时排序或创建临时表的工作空间。将tempdb创建在RAM中可以使系统操作性能有较大提高，而且因为tempdb在每次重启动服务器时都重建，这样即使有非正常的关闭也是较为安全的，例如停电故障。要将tempdb创建在RAM中，可以使用sp_configure进行设置，具体用法请参阅有关资料。由于tempdbinRAM使用的内存是由系统从内存体单独分配的，与SQL Server的内存选项设置的可用内存池是分开的，使用tempdbin RAM将减少整个系统的可用内存，应根据SQL Server和服务器运行情况进行配置，否则就可能适得其反，影响系统性能。另外，适当增加tempdb数据库空间，即使不使用tempdbin RAM，也可以提高数据库的运行速度。4注意事项(1)建议在生产环境中SQL Server不要设置小于32MB内存，而且数据库服务器上尽量不要运行其它应用程序；(2)扩充供虚拟内存、增大SQL Server可用内存，应考虑物理内存使用状况和磁盘空间许可情况；(3)在可能的情况下，要为系统留有部分额外的内存，这样在服务器上打开一个服务或添加一个进程且不改变SQL Server内存配置时，不致于使NT服务器的运行速度受到影响(变得很慢)，一般认为最小为2MB最大为20MB。 四、巧用内存配置，解决统计服务器问题一单位的统计服务器投入使用后，运行速度较慢，经排查原因，发现SQLServer中的内存选项(Memory)仅为安装缺省值16MB(而服务器有128MB的物理内存)，在将内存值调整为100MB时却误将其改成了 1000MB，使得SQL Server服务不能启动，统计数据库打不开，也就不能再次进入SQL Enterprise Manager修改内存设置了。由于近期未备份业务数据，不到万不得已不能重装SQLServer数据库，就试图用命令行参数命令来重新启动SQL Server服务，但均不能奏效，陷入了困境。我们经过仔细分析提出：既然SQL Server可用内存设置值远远大于物理内存，造成SQLServer服务不能启动，何不扩充虚拟内存呢?经设法将机器虚拟内存扩充至1000MB并重新启动，SQL Server数据库成功启动，问题迎刃而解。五、结束语目前，大多数单位投入使用的Microsoft SQL Server数据库服务器的物理内存一般都在64MB以上，如农业银行省、市分行的统计服务器配备128MB物理内存，只要按照上面提出的方法合理规划、优化NT和SQL Server的内存配置，使设置尽可能达到最优，应用系统就一定能够充分发挥设计功能、满足业务需求。 季广胜（中国农业银行江苏省泰州市分行信息电脑中心，江苏 泰州 225300）SQL Server性能调优：资源管理之内存管理篇（上） 对SQL Server来说，最重要的资源是内存、Disk和CPU，其中内存又是重中之重，因为SQL Server为了性能要求，会将它所要访问的数据全部（只要内存足够）放到缓存中。这篇就来介绍SQL Server的内存管理体系。SQL Server作为Windows上运行的应用程序，必须接受Windows的资源管理，利用Windows的API来申请和调度各类资源。但是，由于Windows的资源管理体系，是为了满足大多数的应用程序所设计的，这对于SQL Server这种定位于企业级、支持多用户和高并发性的数据库应用程序来说不是很适合，为此SQL Server开发了自己的一套资源管理体系SQLOS(SQL操作系统)。也就是说SQL Server的资源管理分两层，第一层是在Windows上，通过Windows的API来申请资源。第二层是在SQL Server上，利用SQLOS来决定如何使用从Windows那里申请来的资源。一、操作系统层面的SQL Server内存管理由于SQL server的内存是通过Windows的API来申请的，如果Windows自己本身就缺少内存，SQL Server由于申请不到内存，性能自然受影响。因此做SQL Server的内存检测，第一步就是查看系统层面的内存，以确保系统本身不缺内存，这一步简单但是必不可少。这里先介绍Windows的一些内存管理理念，然后介绍如何检查系统的内存情况。1、Windows的一些内存术语Virtual Address Space（虚拟地址空间）：应用程序能够申请访问的最大地址空间。对于32位的服务器，地址寻址空间为2的32次方，也就是4GB，但是这4GB并不是都给SQL Server使用的，默认情况下是用户态2GB，核心态2GB，所以说对于32位的系统SQL Server只有2GB的内存可供使用。不过可以通过设置/3GB 参数，来调整系统的配置，使用户态为3GB，核心态为1GB。或者开启AWE（地址空间扩展），将寻址空间扩展为64GB，不过该设置有缺陷，下面会分析。Physical Memory（物理内存）：也就是通常所说的电脑的内存大小。Reserved Memory（保留地址）：应用程序访问内存的方式之一，先保留（Reserve）一块内存地址空间，留着将来使用（SQL Server中的数据页面使用的内存就是通过这个方式申请 的）。被保留的地址空间，不能被其他程序访问，不然会出现访问越界的报错提示。Committed Memory（提交内存）：将保留（Reserve）的内存页面正式提交（Commit）使用。Shared Memory（共享内存）：对一个以上进程可见的内存。Private Bytes（私有内存）：某进程提交的地址空间中，非共享的部分。Working Set：进程的地址空间中存放在物理内存中的部分。Page Fault（页面访问错误）：访问在虚拟地址空间，但不存在于Working Set中会发生Page Fault。这个又分两种情况，第一种是目标页面在硬盘上，这钟访问会带来硬盘读写，这种称为Hard Fault。另外一种是目标页面在物理内存中，但是不是该进程的Working Set下，Windows只需要重新定向一下，成为Soft Fault。由于Soft Hard不带来硬盘读写，对系统的性能影响很小，因此管理员关心的是Hard Fault。System Working Set：Windows系统的Working Set。2、Windows的内存检测可以通过Windows的性能监视器来检测Windows的内存使用情况，如何使用性能监视器，可以看这篇文章使用“性能监视器”监视系统性能/运行情况 。在检测内存上，比较重要的计数器有下面一些：分析Windows系统的内存总体使用情况的计数器： Memory：Available MBytes：系统中空闲的物理内存数。Memory：Pages/Sec：由于Hard Page的发生，每秒钟从硬盘中读取或者写入的页面数。该计数器等于Memory：Pages Input/Sec与Memory：Pages Output/Sec之和。分析Windows系统自身的内存使用情况的计数器： Memory：Cache Bytes：系统的Working Set，也就是Windows系统使用的物理内存数。对于每个进程的内存使用情况的计数器：Process：Private Bytes：进程提交的地址空间中非共享的部分。Process：Working Set：进程的地址空间中存放在物理内存中的那部分。从这些计数器中，我们可以看到系统中是否还有空闲内存，哪个进程使用的内存最多，在发生问题的时候是否有内存使用量突变等情况。这为接下来分析SQL Server的使用提供一个前提条件。二、SQL Server内部的内存管理1、内存使用分类按用途分类1）Database cache（数据页面）。SQL Server中的页面都是以8KB为一个页面存储的。当SQL Server需要用到某个页面时，它会将该页面读到内存中，使用完后会缓存在内存中。在内存没有压力的情况下，SQL Server不会将页面从内存中删除。如果SQL Server感觉到内存的压力时，会将最长时间没有使用的页面从内存中删除来空出内存。2）各类Consumer（功能组件） Connection的连接信息General：一组大杂烩。语句的编译、范式化、每个锁数据结构、事务上下文、表格和索引的元数据等Query Plan：语句和存储过程的执行计划。和Database cache类似，SQL Server也会将执行计划缓存以供将来使用，减少编译时间。Optimizer：生成执行计划的过程中消耗的内存。Utilities：像BCP、Log Manager、Backup等比较特殊的操作消耗的内存。3）线程内存：存放进程内每个线程的数据结构和相关信息消耗的内存，每个线程需0.5MB的内存。4）第三方代码消耗的内存：SQL Server的进程里，会运行一些非SQL Server自身的代码。例如：用户定义的CLR或Extended Stored Procedure代码。按申请方式分类1）预先Reserve一块大的内存，然后在使用的时候一块一块的Commit。Database Page是按这种方式申请的。2）直接用Commit方式申请的内存，成为Stolen方式。除了Database Page之外其他内存基本都是按这种方式申请的。按申请内存的大小分类 1）申请小于等于8KB为一个单位的内存，这些内存称为Buffer Pool2）申请大于8KB为一个单位的内存，这些内存称为Multi-Page（或MemToLeave）SQL Server对于Database Page都是采用先Reserved后Commit的方式申请的，而数据页都是以8KB为单位进行申请的。对于Consumer中的内存申请，一般都是按Stolen方式申请的，且大多数的执行计划的大小都是小于8KB的，少数特别复杂的存储过程的执行计划会超过8KB，默认的连接的数据包是4KB，除非客户端特别设置了超过8KB（不建议）第三方代码的内存申请一般是按Stolen方式申请的，个别比如CLR中可能会用Reserved/Commit的方式申请。线程的内存每个都以0.5MB的方式申请，自然是放在MemToLeave中。之所以花了这么大篇幅来讲SQL Server的内存分类，是因为SQL Server尤其是32位的SQL Server对不同种类的内存的申请大小是不一样的，对Commit、Stolen和MemTOLeave等类型的内存是有限制的。因此会出现系统中还有空闲内存，但是SQL Server不会申请使用的现象。 2、各部分内存的大小限制 1）32位的Windows在SQL Server启动时，会预先分配好MemToLeave区域的大小。默认大小为256MB+256（SQL Server配置的允许最大线程数）* 0.5MB=384MB，因此Buffer Pool中的最大值为2GB-384MB=1.664G。如果使用了AWE技术，可以将系统的扩展地址空间达到64GB，但由于AWE扩展出来的地址只能用Reserved/Commit方式申请，为此MemToLeave的内存还是384MB，Buffer Pool中的Stolen的最大内存为1.664G，剩余的内存都可以为Database Page页面使用。2）64位的Windows32位的SQL Server。由于64位的操作系统，核心态不再占用32位进程的虚拟地址空间，因此MemToLeave的大小还是为384MB，Buffer Pool可以达到3.664G。如果还开启了AWE，这3.664GB可以全部用于Buffer Pool中的Stolen，剩余的内存都可以给Database Page页面使用。不过这种情况很少见，哪里用64位操作系统的机器装32位的哦-_- 。64位的SQL Server。所有的内存都无限申请的，有需要就申请。3、SQL Server内存使用情况的分析一般来说有两种方式，第一种就是用来分析系统内存情况时使用的用性能计数器来分析，第二种是使用动态管理视图（DMV，只适用于SQL Server2005和2008）1）SQL Server性能计数器SQLServer：Memory Manager：Total Server Memory（KB)：SQL Server缓冲区提交的内存。不是SQL Server总的使用内存，只是Buffer Pool中的大小。SQLServer：Memory Manager：Target Server Memory（KB）：服务器可供SQL Server使用的内存量。一般是由SQL Server能访问到的内存量和SQL Server的sp_Configure配置中的Max Server Memory值中的较小值算得。SQLServer：Memory Manger：Memory Grants Pending：等待内存授权的进程总数。如果该值不为0，说明当前有用户的内存申请由于内存压力被延迟，这意味着比较严重的内存瓶颈。SQLServer：Buffer Manager：Buffer Cache Hit Ratio：数据从缓冲区中找到而不需要从硬盘中去取的百分比。SQL Server在运行一段时间后，该比率的变化应该很小，而且都应该在98%以上，如果在95%以下，说明有内存不足的问题。 SQLServer：Buffer Manager：Lazy Writes/Sec：每秒钟被惰性编辑器（Lazy writer）写入的缓冲数。当SQL Server感觉到内存压力的时候，会将最久没有使用的数据页面和执行计划从缓冲池中清理掉，做这个动作的就是Lazy Writer。Page Life Expectancy：页面不被引用后，在缓冲池中停留的秒数。在内存没有压力的情况下，页面会一直待在缓冲池中，Page Life Expectancy会维持在一个比较高的值，如果有内存压力时，Page Life Expectancy会下降。所以如果Page Life Expectancy不能维持在一个值上，就代表SQLServer有内存瓶颈。 SQLServer：Buffer Manager：Database Pages ：就是Database Cache的大小。SQLServer：Buffer Manager：Free Pages：SQL Server中空闲可用的大小。SQLServer：Buffer Manager：Stolen Pages：Buffer Pool中Stolen的大小。SQLServer：Buffer Manager：Total Pages：Buffer Pool的总大小（等于Database Pages+Free Pages+Stolen Pages）。该值乘以8KB，应该等于Memory Manager：Total Server Memory的值。从上面这些计数器中我们就能了解SQL Server的内存使用情况，结合前面说的系统层的计数器大概能看出是否存在内存瓶颈。2）内存动态管理视图在SQL Server 2005以后，SQL Server的内存管理是使用Memory Clerk的方式统一管理。所有的SQL Server的内存的申请或释放，都需要通过它们的Clerk，SQL Server也通过这些Clerk的协调来满足不同需求。通过查询这些DMV，可以得到比用性能计数器更加详细的内存使用情况。我们可以通过下面的查询语句来检测SQL Server的Clerk的内存使用情况。使用sys.dm_os_memory_clerks查看内存使用情况 SELECTtype,-Clerk的类型sum(virtual_memory_reserved_kb)asvm_Reserved_kb,-保留的内存sum(virtual_memory_committed_kb)asvm_Committed_kb,-提交的内存sum(awe_allocated_kb)asawe_Allocated_kb,-开启AWE后使用的内存sum(shared_memory_reserved_kb)assm_Reserved_kb,-共享的保留内存sum(shared_memory_committed_kb)assm_Committed_kb,-共享的提交内存sum(single_pages_kb)asSinlgePage_kb,-BufferPool中的Stolen的内存sum(multi_pages_kb)asMultiPage_kb-MemToLeave的内存FROMsys.dm_os_memory_clerksGROUPBYtypeORDERBYtype从上面的查询语句，我们可以算出前面提到的内存大小Reserved/Commit = sum(virtual_memory_reserved_kb) / sum(virtual_memory_committed_kb)Stolen = sum(single_pages_kb) + sum(multi_pages_kb)Buffer Pool = sum(virtual_memory_committed_kb) + sum(single_pages_kb)MemToLeave = sum(multi_pages_kb)通过上面的介绍我们可以知道SQL Server总体和各部分内存的使用情况，如果我想知道数据页的缓存中到底缓存了哪些数据，这些数据是属于哪个数据库的哪个表中的呢？执行计划又是缓存了哪些语句的执行计划呢？这也可以通过DMV查看的到。 查看内存中的数据页面缓存的是哪个数据库的哪个表格的数据 declarenamenvarchar(100)declarecmdnvarchar(1000)declaredbnamescursorforselectnamefrommaster.dbo.sysdatabasesopendbnamesfetchnextfromdbnamesintonamewhilefetc