数据库性能优化方案

1、数据库性能优化解决方案数据库能优化方案是对用户提出的k3系统在使用过程中遇到的性能问题，从sql server调整，硬件调整这两方面提出了性能优化解决方案。1 1 sql server调整当用户使用k3系统一段时间以后，发现系统的响应时间越来越长。这种情形往往是由于账套数据库缺乏维护引起的。缺乏维护的数据库会存在过多地碎片、过期的统计、隐含着可能的错误查询结果的数据库的逻辑和物理的不一致性，这些都会直接影响系统的性能。这里介绍解决上述账套数据库性能问题常用的方法。1.1 1.1 使用dbcc语句发现和解决上述问题。dbcc: 数据库一致性检查器。打开sql查询分析器，执行如下语句。u u db

2、cc showcontig 显示指定表的数据和索引的有关数据碎片的信息dbcc showcontig(表名，索引名)在有大的改动的表，引入数据的表，或者引起低效查询的表上使用该语句。例：dbcc showcontig（t_item）u u dbcc dbreindex重建指定数据库中表的一个或多个索引。例1：重建某个索引dbcc dbreindex (t_item, uk_item2, 80)例2：重建所有索引dbcc dbreindex (t_item,80)u u dbcc show_statistics显示指定表上的指定目标(例如一个索引名称)的当前分布统计信息。这些统计信息是被sql

3、server查询优化器使用的dbcc show_statistics(表名，目标)例：dbcc show_statistics(t_item,pk_item)u u sp_updatestats & update statistics 更新统计信息； sp_updatestats对当前数据库中所有用户定义的表运行 update statistics.使用update statistics语句的时机：在一个空表上创建一个索引，然后在以后应用它。执行truncate table语句，然后在以后重新应用该表。通过使用fullscan或sample选项请求明细的索引统计信息。例1. 例1. updat

4、e statistics t_item例2. 例2. update statistics t_item(pk_item)例3. 例3. use ais20011203150410exec sp_updatestatsu u dbcc checktable检查指定表或索引视图的数据、索引及 text、ntext 和 image 页的完整性。如果你相信一个指定的表可能被破坏了，这条命令非常有用。u u dbcc checkdb检查指定数据库中的所有对象的分配和结构完整性。这条命令发现并修复数据库地址分配和表内部的全部错误。实际上，checkdb验证数据库内部一切事物的完整性，但是，dbcc che

5、ckdb是一个耗费cpu和磁盘资源的操作，每个需要检查的数据都必须首先从磁盘中读出到内存中。而且，dbcc checkdb 使用tempdb进行排序 。要获得较高的dbcc性能，推荐在下面的情况下运行dbcc：l l 在系统使用率较低的情况下运行checkdb；l l 确信当前没有执行其他磁盘i/o操作，如磁盘备份操作；l l 将tempdb放在另一个磁盘系统上，或者放在一个快速磁盘子系统上；l l 为tempdb提供足够的空间，运行dbcc带上参数estimate only（显示执行dbcc checkdb操作所需tempdb空间的数量），估计tempdb需要多少磁盘空间；l l 避免运行消

6、耗大量cpu时间的查询和批处理；l l 在dbcc命令运行时，减少事物活动；l l 使用no_infomsgs选项（压缩使用空间使用的信息和报告）减少处理和tempdb使用率。例：dbcc checkdb (ais20011203150410) with no_infomsgs,estimateonlyu u dbcc sqlperf 提供有关所有数据库中的事务日志空间使用情况的统计信息。日志文件的闲余空间的减少，会降低系统的性能。系统会在备份时日志截断日志文件，所以要求用户要制定一份良好的备份方案。例：dbcc sqlperf ( logspace )1.2 1.2 使用数据库维护计划使用数

7、据库维护计划器是一种标准且方便的可对多个账套数据库同时设置维护任务维护模式。下面介绍其建立方法：本方案所介绍的数据库维护计划侧重于数据库的优化,即性能的提高。1) 1) 打开enterprise manager，展开服务器，展开管理,然后单击数据库维护计划。从操作（action）中选择新建维护计划,可以看到图4.1所示的欢迎屏幕，单击下一步按钮。 图1欢迎屏幕2) 2) 选择数据库，选择k3账套所在的数据库（可选一个或多个）。单击下一步按钮。 图2 选择数据库3) 3) 更新数据库优化信息。选择重新组织数据和索引页，选择使用原有可用空间重新组织页面。选择当增长超过50mb时，从数据库文件中删除

8、未使用空间，收缩后保留的可用空间为10%的数据空间。单击下一步按钮。 图3更新数据库优化信息4) 4) 检查数据库完整性。选择检查数据库完整性,包含索引以及尝试修复所有小问题。单击下一步。 图4 检查数据库完整性5) 5) 指定数据库备份计划,备份在优化方案中暂不考虑，跳过，单击下一步。 图5数据库备份计划6) 6) 指定事务日志备份计划在优化方案中暂不考虑，跳过，单击下一步。 图6指定事物备份计划7) 7) 生成报表。选择将报表写入目录中的文本文件，选择删除早于4周的报表文件。或者选择将电子邮件报表发送到操作员，然后花时间阅读这个报表，看看数据库中是否有任何需要注意的问题。单击下一步。 图7

9、生成报表8) 8) 维护计划历史记录。sql server每次运行时保持维护计划的历史。可以浏览这个历史，看看操作中何时遇到故障，然后确定故障原因。如果只有单台机器，则要在本地服务器存放历史纪录，但如果网络中又多台机器，则要将历史纪录存放在中央服务器中，以便从各台机器上方便的访问。下面选择缺省在本地存放1000行历史纪录。单击下一步。 图 8 维护历史纪录9) 9) 完成数据库维护计划向导。用于命名和检查具体工作，在计划名中输入：k3账套数据库维护计划。单击完成按钮生成计划。 图9 完成数据库维护计划向导1.3 1.3 发现死锁和消除死锁 死锁形成的原因是不同的，有的死锁系统可以自动地侦测和消

10、除而另外一些则需要管理员调整请求死锁发生在两个或多个进程同时等待被其中一个进程保留着的锁。该进程将不会释放它保留的锁直到它获得被其它进程保留的资源，反过来也一样。当一个死锁被被确认以后，sql server通过自动选择可以立即打断死锁的线程来结束死锁。许多阻塞的问题发生在由于一个进程保留锁过长时间，引起一系列被阻塞的进程等待其它进程释放锁。sql server不能识别阻塞锁并自动地解决它们,所以必须监控阻塞锁的存在并手工消除它。在一个应用中建立一个锁的超时设置是一个防止阻塞锁的方法。这允许应用监控阻塞锁并回滚进程而不是不确定地等待或阻塞语句的重提交。下面，介绍手工消除死锁的方法：1) 1) 系

11、统长时间没有响应，可以在sql查询分析器中执行系统存储过程sp_lock 和sp_who，如图所示，spid 57正在等待资源。spid：系统进程id 执行命令：sp_who 57 可以得到关联该进程和锁的用户的登录名称，主机名称和状态等信息。 图1. 运行sp_lock显示的锁信息2) 转到sql server enterprise manager,展开管理，展开当前活动，展开锁/进id,如图所示，spid57被spid56阻塞。 图2. 显示锁的阻塞情况3) 双击spid56,然后单击取消进程（kill process）。4) spid57阻塞解除。2 2 硬件调整硬件调整，是为k3系统的

12、正常运行要求的工作量提供足够的硬件资源的行动。要调整系统的硬件，就要决定可以为k3系统分配那些资源以改进其性能，这些资源包括附加的内存、cpu、i/o资源或所有这些资源的组合。调整系统性能的工作主要涉及决定应该增加哪种资源，以及增加多少资源。硬件调整是非常重要的，因为许多典型的性能问题是由不充足的或配置失当的硬件组件导致的。i/o子系统是一个数据库调整的关键性部分。通过提供足够的cpu、内存与i/o资源。可以避免许多性能问题。通过监控相关的计数器，可以及时发现和解决引起系统性能降低的硬件问题。2.1 2.1 控制内存的使用sql server 要求内存是基于静态内存的需要：一是它自己的程序代码

13、和内部数据结构，例如内核的工作负载，打开对象，锁。二是数据高速缓存。基于有效的系统资源和这些资源的竞争需要, sql server动态地获得和释放数据高速缓存。如果sql server的数据高速缓存需要更多的内存，它查询操作系统检查是否有物理内存可以利用。如果有，sql server在数据高速存中使用它并且在内存中保留先前读到的数据。为阻止windows 2000页面调度,sql server依赖server activity增减数据高速缓存以保留4mb10mb剩余物理内存。对sql server不足的内存分配或使用会引起数据连续地从硬盘上而不是高速缓存上读取,这将降低系统的性能。请观察以下与

14、内存有关的计数器,以便及时发现和解决内存上的问题。使用工具:性能监视器1 监控内存和分页的使用对象:计数器描述指导memory: available bytes监控被进程执行使用的有效字节数。（可用物理内存量）这个计数器应该总是大于5000kb;低值显示物理内存整体的缺乏和需要提高。推荐值：大于4mbmemory: page/sec为了访问不在内存中的页而读取或写入磁盘的总页数。该计数器应该从不持续大于零.如果值持续大于零，windows 2000操作系统正在使用页面调度来填充内存.推荐值：小于5process: page faults/sec/sql server instance缺页/秒处

15、理器中的page faults的计数值。当进程所引用的虚拟内存页不在其主内存的工作集中时，将发生页错误。如果某一页已在主内存中（位与备用列表内），或者它正被共享此页的其他进程使用，page fault 将不会导致系统从磁盘调入该页。这个计数器的高值表明过多的页面调度和磁盘压力,检查是否是sql server 或其他的进程引起过多的页面调度。隔离sql server 使用的内存process: working set/sql server instance监控用于sql server的一个实例的sql server进程的内存的数量。这个计数器应该大于5000kb。当这个计数器低于5000kb，没

16、有更多的内存可供sql server 使用。sql server: buffer manager: buffer cache hit ratio高速缓存命中率监控高速缓存中不需从硬盘中读取的页的百分率,。不用区分用于高速缓存的是物理内存还是页面调度内存。这个计数器应该大于90%，因为它显示的是发现在内存中的页的数量。sql server: buffer manger: total pages监控高速缓存中页的总数量，包括数据库，free和来自其他进程的stolen页。低值显示连续的磁盘输入输出或压力.考虑增加更多的内存.sql server: memory manager total serv

17、er memory 监控服务器正在使用的动态内存的总的数量。如果该计数器与可用的物理内存比较持续高，则需加更多的内存。2.2 2.2 监控线程和处理器的使用优化处理器性能是输出量和响应时间之间的一种平衡。处理器的性能当你检查处理器的使用，考虑sql server实例正在做的工作的类型。如果sql server正在做大量的计算，例如包含集合的查询或绑定内存这种不需要磁盘输入输出的查询，100%的处理器时间可能被使用。对于多处理器的系统,你需要监控每个处理器的这个计数器的分离的实例。确定所有处理器的平均值，可使计数器：system:% total processor time。线程每个sql se

18、rver的实例都是一个独立的操作系统进程，sql server2000的实例使用windows线程，有时是纤程去有效的管理并发的任务。1) 1) 一个进程是一个应用的实例，例如sql server并且能有一个或多个任务。2) 2) 一个线程是进程任务的一种机制，并且被用来计划处理器的时间。当一个线程处于等待一个操作（例如读写磁盘）完成的空闲期时，windows 2000操作系统通过转换线程来最大化处理器的使用。线程间的转换叫做context switching.每个sql server的实例用户连接的一个线程池,池中的线程被叫做工作线程。当processor: %processor time

19、持续接近100%并且system: processor queue length显示更多的应用的进程正在等待处理器，或者当system: context switches/sec较高。显示出现了系统瓶颈。当processor:% processor time接近100%并且system: context switches/sec接近8000，考虑更快的处理器，附加的处理器或者转换到使用纤程。请观察以下与内存有关的计数器,以便及时发现和解决处理器上的问题。使用工具：windows性能监视器对象:计数器描述指导processor: %processor time以处理器运行非空闲线程所经历时间的百

20、分比表示。它被视为用于处理有效工作的时间比。每一个处理器在空闲时将会指定一个空闲线程来消耗未被其他线程使用的处理器时间段。这个计数器应该低于90%，如果这个计数器较高，应降低工作负荷，提高工作效率或者或加大处理器的能力。system: context switches/sec监控处理器每秒在线程间转换的次数。在一个多处理器的计算机上,如果这个计数器达到8000,并且processor:% processor time计数器超过90%,考虑使用sql server fiber scheduling.system: processor queue length监控等待进程时间的线程的数目这个计数器

21、不应该持续大于2。如果这个计数器持续大于2，降低工作负荷,提高工作负荷的效率，或者增加处理器的能力，在多处理器的系统中可以增加处理器。processor: % privileged time在“特权模式”下处理器运行非空闲线程所经历时间的百分比。windows nt服务层，执行体子程序及windows nt内核都是在“特权方式”下运行。如果处理器的大部分时间被用来做系统内核命令,并且物理硬盘的计数器较高，考虑提高硬盘输入输出子系统的性能。processor: %user time在“用户模式”下处理器运行非空闲线程所经历时间的百分比。所有应用程序码及子系统码都在“用户模式“下运行。这个能确定其它进程或应