SQL数据库性能优化方案

Joyfully北京乐正软件有限公司乐正.com数据库性能优化解决方案数据库性能优化是对用户使用ERP过程中遇到的性能问题，从SQLServer调整，硬件调整这两方面提出了性能优化解决方案。1SQLServer调整当用户使用ERP系统一段时间以后，发现系统的响应时间越来越长。这种情形往往是由于数据库缺乏维护引起的。缺乏维护的数据库会存在过多地碎片、过期的统计、隐含着可能的错误查询结果的数据库的逻辑和物理的不一致性，这些都会直接影响系统的性能。这里介绍解决上述数据库性能问题常用的方法。1.1使用DBCC语句发现和解决上述问题。DBCC:数据库一致性检查器。打开SQL查询分析器，执行如下语句。DBCCSHOWCONTIG显示指定表的数据和索引的有关数据碎片的信息DBCCSHOWCONTIG(表名[，索引名])在有大的改动的表，引入数据的表，或者引起低效查询的表上使用该语句。例：DBCCSHOWCONTIG（’T_ITEM’）DBCCDBREINDEX重建指定数据库中表的一个或多个索引。例1：重建某个索引DBCCDBREINDEX('T_ITEM',uk_item2,80)例2：重建所有索引DBCCDBREINDEX('T_ITEM',’’,80)DBCCSHOW_STATISTICS显示指定表上的指定目标(例如一个索引名称))的当前分布统计信息。这些统计信息是被SQLServer查询优化器使用的DBCCSHOW_STATISTICS(表名，目标)例：DBCCSHOW_STATISTICs('t_item','pk_item')sp_updatestats&UPDATESTATISTICS更新统计信息；sp_updatestats对当前数据库中所有用户定义的表运行UPDATESTATISTICS.Joyfully北京乐正软件有限公司乐正.com使用UPDATESTATISTICS语句的时机：在一个空表上创建一个索引，然后在以后应用它。执行TRUNCATETABLE语句，然后在以后重新应用该表。通过使用FULLSCAN或SAMPLE选项请求明细的索引统计信息。例1.UPDATESTATISTICST_ITEM例2.UPDATESTATISTICST_ITEM(PK_ITEM)例3.USEAIS20011203150410EXECsp_updatestatsDBCCCHECKTABLE检查指定表或索引视图的数据、索引及text、ntext和image页的完整性。如果你相信一个指定的表可能被破坏了，这条命令非常有用。DBCCCHECKDB检查指定数据库中的所有对象的分配和结构完整性。这条命令发现并修复数据库地址分配和表内部的全部错误。实际上，CHECKDB验证数据库内部一切事物的完整性，但是，DBCCCHECKDB是一个耗费CPU和磁盘资源的操作，每个需要检查的数据都必须首先从磁盘中读出到内存中。而且，DBCCCHECKDB使用tempdb进行排序。要获得较高的DBCC性能，推荐在下面的情况下运行DBCC：在系统使用率较低的情况下运行CHECKDB；确信当前没有执行其他磁盘I/O操作，如磁盘备份操作；将tempdb放在另一个磁盘系统上，或者放在一个快速磁盘子系统上；为tempdb提供足够的空间，运行DBCC带上参数ESTIMATEONLY（显示执行DBCCCHECKDB操作所需tempdb空间的数量），估计tempdb需要多少磁盘空间；避免运行消耗大量CPU时间的查询和批处理；在DBCC命令运行时，减少事物活动；使用NO_INFOMSGS选项（压缩使用空间使用的信息和报告）减少处理和tempdb使用率。例：DBCCCHECKDB('AIS20011203150410')WITHNO_INFOMSGS,ESTIMATEONLYDBCCSQLPERF提供有关所有数据库中的事务日志空间使用情况的统计信息。日志文件的闲余空间的减少，会降低系统的性能。系统会在备份时日志截断日志文件，所以要求用户要制定一份良好的备份方案。例：DBCCSQLPERF(LOGSPACE)1.2使用数据库维护计划使用数据库维护计划器是一种标准且方便的可对多个数据库同时设置维护任务维护模式。Joyfully北京乐正软件有限公司乐正.com下面介绍其建立方法：本方案所介绍的数据库维护计划侧重于数据库的优化,即性能的提高。1)打开EnterpriseManager，展开服务器，展开管理,然后单击数据库维护计划。从操作（Action）中选择新建维护计划,可以看到图4.1所示的欢迎屏幕，单击下一步按钮。图1欢迎屏幕2)选择数据库，选择ERP所在的数据库（可选一个或多个）。单击下一步按钮。3)更新数据库优化信息。选择重新组织数据和索引页，选择使用原有可用空间重新组织页面。选择当增长超过50MB时，从数据库文件中删除未使用空间，收缩后保留的可用空间为10%的数据空间。单击下一步按钮。图2更新数据库优化信息4)检查数据库完整性。选择检查数据库完整性,包含索引以及尝试修复所有小问题。单击下一步。Joyfully北京乐正软件有限公司乐正.com5)指定数据库备份计划,备份在优化方案中暂不考虑，跳过，单击下一步。6)指定事务日志备份计划在优化方案中暂不考虑，跳过，单击下一步。7)生成报表。选择将报表写入目录中的文本文件，选择删除早于4周的报表文件。或者选择将电子邮件报表发送到操作员，然后花时间阅读这个报表，看看数据库中是否有任何需要注意的问题。单击下一步。8)维护计划历史记录。SQLServer每次运行时保持维护计划的历史。可以浏览这个历史，看看操作中何时遇到故障，然后确定故障原因。如果只有单台机器，则要在本地服务器存放历史纪录，但如果网络中又多台机器，则要将历史纪录存放在中央服务器中，以便从各台机器上方便的访问。下面选择缺省在本地存放1000行历史纪录。单击下一步。9)完成数据库维护计划向导。用于命名和检查具体工作，在计划名中输入：ERP数据库维护计划。单击完成按钮生成计划。1.3发现死锁和消除死锁死锁形成的原因是不同的，有的死锁系统可以自动地侦测和消除而另外一些则需要管理员调整请求死锁发生在两个或多个进程同时等待被其中一个进程保留着的锁。该进程将不会释放它保留的锁直到它获得被其它进程保留的资源，反过来也一样。当一个死锁被被确认以后，SQLServer通过自动选择可以立即打断死锁的线程来结束死锁。许多阻塞的问题发生在由于一个进程保留锁过长时间，引起一系列被阻塞的进程等待其它进程释放锁。SQLServer不能识别阻塞锁并自动地解决它们,所以必须监控阻塞锁的存在并手工消除它。在一个应用中建立一个锁的超时设置是一个防止阻塞锁的方法。这允许应用监控阻塞锁并回滚进程而不是不确定地等待或阻塞语句的重提交。下面，介绍手工消除死锁的方法：1)系统长时间没有响应，可以在SQL查询分析器中执行系统存储过程sp_lock和sp_who，如图所示，spid57正在等待资源。Spid：系统进程ID执行命令：sp_who57可以得到关联该进程和锁的用户的登录名称，主机名称和状态等信息。Joyfully北京乐正软件有限公司乐正.com图1.运行sp_lock显示的锁信息2)转到SQLServerEnterpriseManager,展开管理，展开当前活动，展开锁/进ID,如图所示，spid57被spid56阻塞。图2.显示锁的阻塞情况3)双击spid56,然后单击取消进程（KillProcess）。4)spid57阻塞解除。2硬件调整硬件调整，是为ERP系统的正常运行要求的工作量提供足够的硬件资源的行动。要调整系统的硬件，就要决定可以为ERP系统分配那些资源以改进其性能，这些资源包括附加的内存、CPU、Joyfully北京乐正软件有限公司乐正.comI/O资源或所有这些资源的组合。调整系统性能的工作主要涉及决定应该增加哪种资源，以及增加多少资源。硬件调整是非常重要的，因为许多典型的性能问题是由不充足的或配置失当的硬件组件导致的。I/O子系统是一个数据库调整的关键性部分。通过提供足够的CPU、内存与I/O资源。可以避免许多性能问题。通过监控相关的计数器，可以及时发现和解决引起系统性能降低的硬件问题。2.1控制内存的使用SQLServer要求内存是基于静态内存的需要：一是它自己的程序代码和内部数据结构，例如内核的工作负载，打开对象，锁。二是数据高速缓存。基于有效的系统资源和这些资源的竞争需要,SQLServer动态地获得和释放数据高速缓存。如果SQLServer的数据高速缓存需要更多的内存，它查询操作系统检查是否有物理内存可以利用。如果有，SQLServer在数据高速存中使用它并且在内存中保留先前读到的数据。为阻止Windows2000页面调度,SQLServer依赖Serveractivity增减数据高速缓存以保留4MB~10MB剩余物理内存。对SQLServer不足的内存分配或使用会引起数据连续地从硬盘上而不是高速缓存上读取,这将降低系统的性能。请观察以下与内存有关的计数器,以便及时发现和解决内存上的问题。使用工具:性能监视器监控内存和分页的使用对象:计数器描述指导Memory:AvailableBytes监控被进程执行使用的有效字节数。（可用物理内存量）这个计数器应该总是大于5000KB;低值显示物理内存整体的缺乏和需要提高。推荐值：大于4MBMemory:Page/sec为了访问不在内存中的页而读取或写入磁盘的总页数。该计数器应该从不持续大于零.如果值持续大于零，Windows2000操作系统正在使用页面调度来填充内存.Joyfully北京乐正软件有限公司乐正.com推荐值：小于5Process:PageFaults/sec/SQLServerInstance缺页/秒处理器中的PageFaults的计数值。当进程所引用的虚拟内存页不在其主内存的工作集中时，将发生页错误。如果某一页已在主内存中（位与备用列表内），或者它正被共享此页的其他进程使用，PageFault将不会导致系统从磁盘调入该页。这个计数器的高值表明过多的页面调度和磁盘压力,检查是否是SQLServer或其他的进程引起过多的页面调度。隔离SQLServer使用的内存Process:WorkingSet/SQLServerInstance监控用于SQLServer的一个实例的SQLServer进程的内存的数量。这个计数器应该大于5000KB。当这个计数器低于5000KB，没有更多的内存可供SQLServer使用。SQLServer:BufferManager:BufferCacheHitRatio高速缓存命中率监控高速缓存中不需从硬盘中读取的页的百分率,。不用区分用于高速缓存的是物理内存还是页面调度内存。这个计数器应该大于90%，因为它显示的是发现在内存中的页的数量。SQLServer:BufferManger:TotalPages监控高速缓存中页的总数量，包括数据库，free和来自其他进程的stolen页。低值显示连续的磁盘输入输出或压力.考虑增加更多的内存.SQLServer:MemoryManagerTotalServerMemory监控服务器正在使用的动态内存的总的数量。如果该计数器与可用的物理内存比较持续高，则需加更多的内存。2.2监控线程和处理器的使用优化处理器性能是输出量和响应时间之间的一种平衡。处理器的性能Joyfully北京乐正软件有限公司ht