K3 wise erp 数据库索引性能优化解决方案.docx

1、K3 wise erp 数据库索引性能优化解决方案K3系统数据库索引性能优化解决方案（具体应用篇）-重建索引速度较慢，请在系统空闲时间进行 DBCC DBREINDEX(t_icitem) DBCC DBREINDEX(t_item) DBCC DBREINDEX(t_itemclass) DBCC DBREINDEX(t_itemright) DBCC DBREINDEX(t_user) DBCC DBREINDEX(t_group) go if not exists(select 1 from sysindexes where name=ix_group_fgroupid) create

2、index ix_group_fgroupid on t_group(fgroupid) go if not exists(select 1 from sysindexes where name=ix_itemright_ftypeid) create index ix_itemright_ftypeid on t_itemright(ftypeid) go 1 SQL Server调整 当用户使用K3系统一段时间以后，发现系统的响应时间越来越长。这种情形往往是由于账套数据库缺乏维护引起的。缺乏维护的数据库会存在过多地碎片、过期的统计、隐含着可能的错误查询结果的数据库的逻辑和物理的不一致性，这

3、些都会直接影响系统的性能。这里介绍解决上述账套数据库性能问题常用的方法。 1.1 使用DBCC语句发现和解决上述问题。 DBCC: 数据库一致性检查器。 打开SQL 查询分析器 ，执行如下语句。 u DBCC SHOWCONTIG 显示指定表的数据和索引的有关数据碎片的信息DBCC SHOWCONTIG(表名，索引名) 在有大的改动的表，引入数据的表，或者引起低效查询的表上使用该语句。 例：DBCC SHOWCONTIG（T_ITEM） u DBCC DBREINDEX 重建指定数据库中表的一个或多个索引。 例1：重建某个索引 DBCC DBREINDEX (T_ITEM, uk_item2,

4、 80)例2：重建所有索引 DBCC DBREINDEX (T_ITEM,80) u DBCC SHOW_STATISTICS 显示指定表上的指定目标(例如一个索引名称)的当前分布统计信息。这些统计信息是被SQL Server查询优化器使用的DBCC SHOW_STATISTICS(表名，目标) 例：DBCC SHOW_STATISTICs(t_item,pk_item) u sp_updatestats & UPDATE STATISTICS 更新统计信息； sp_updatestats 对当前数据库中所有 用户定义的表运行 UPDATE STATISTICS. 使用UPDATE STATI

5、STICS 语句的时机：在一个空表上创建一个索引，然后在以后应用它。执行TRUNCATE TABLE语句，然后在以后重新应用该表。通过使用FULLSCAN或SAMPLE选项请求明细的索引统计信息。 例1. UPDATE STATISTICS T_ITEM 例2. UPDATE STATISTICS T_ITEM(PK_ITEM) 例 3. USE AIS20011203150410 EXEC sp_updatestats u DBCC CHECKTABLE 检查指定表或索引视图的数据、索引及 text 、ntext 和 image 页的完整性。如果你相信一个指定的表可能被破坏了，这条命令非常有

6、用。 u DBCC CHECKDB 检查指定数据库中的所有对象的分配和结构完整性。这条命令发现并修复数据库地址分配和表内部的全部错误。实际上，CHECKDB验证数据库内部一切事物的完整性，但是，DBCC CHECKDB是一个耗费CPU和磁盘资源的操作，每个需要检查的数据都必须首先从磁盘中读出到内存中。而且，DBCC CHECKDB 使用tempdb进行排序 。要获得较高的DBCC性能，推荐在下面的情况下运行DBCC： l 在系统使用率较低的情况下运行CHECKDB； l 确信当前没有执行其他磁盘I/O操作，如磁盘备份操作； l 将tempdb放在另一个磁盘系统上，或者放在一个快速磁盘子系统上；

7、 l 为tempdb提供足够的空间，运行DBCC带上参数ESTIMATE ONLY（显示执行 DBCC CHECKDB 操作所需tempdb 空间的数量 ），估计tempdb需要多少磁盘空间； l 避免运行消耗大量CPU时间的查询和批处理； l 在DBCC命令运行时，减少事物活动； l 使用NO_INFOMSGS选项（压缩使用空间使用的信息和报告 ）减少处理和tempdb使用率。 例：DBCC CHECKDB (AIS20011203150410) WITH NO_INFOMSGS,ESTIMATEONLY u DBCC SQLPERF 提供有关所有数据库中的事务日志空间使用情况的统计信息。日

8、志文件的闲余空间的减少，会降低系统的性能。系统会在备份时日志截断日志文件，所以要求用户要制定一份良好的备份方案。 例：DBCC SQLPERF ( LOGSPACE ) 1.2 使用数据库维护计划 使用数据库维护计划器是一种标准且方便的可对多个账套数据库同时设置维护任务维护模式。下面介绍其建立方法： 本方案所介绍的数据库维护计划侧重于数据库的优化,即性能的提高。 1) 打开Enterprise Manager，展开服务器，展开管理,然后单击数据库维护计划。从操作（Action）中选择新建维护计划 ,可以看到图4.1所示的欢迎屏幕，单击下一步按钮。 2) 选择数据库，选择K3账套所在的数据库（可

9、选一个或多个）。单击下一步按钮。 图2 选择数据库 3) 更新数据库优化信息。选择重新组织数据和索引页，选择使用原有可用空间重新组织页面。选择当增长超过50MB时，从数据库文件中删除未使用空间，收缩后保留的可用空间为10%的数据空间。单击下一步按钮。 图3更新数据库优化信息 4) 检查数据库完整性。选择检查数据库完整性,包含索引以及尝试修复所有小问题。单击下一步。 图4 检查数据库完整性 5) 指定数据库备份计划,备份在优化方案中暂不考虑，跳过，单击下一步。 图5数据库备份计划 6) 指定事务日志备份计划在优化方案中暂不考虑，跳过，单击下一步。 图6指定事物备份计划 7) 生成报表。选择将报表

10、写入目录中的文本文件，选择删除早于4周的报表文件。或者选择将电子邮件报表发送到操作员，然后花时间阅读这个报表，看看数据库中是否有任何需要注意的问题。单击下一步。 图7生成报表 8) 维护计划历史记录。 SQL Server每次运行时保持维护计划的历史。可以浏览这个历史，看看操作中何时遇到故障，然后确定故障原因。如果只有单台机器，则要在本地服务器存放历史纪录，但如果网络中又多台机器，则要将历史纪录存放在中央服务器中，以便从各台机器上方便的访问。下面选择缺省在本地存放1000行历史纪录。单击下一步。 图 8 维护历史纪录 9) 完成数据库维护计划向导。用于命名和检查具体工作，在计划名中输入：K3账

11、套数据库维护计划。单击完成按钮生成计划。 图9 完成数据库维护计划向导 1.3 发现死锁和消除死锁 死锁形成的原因是不同的，有的死锁系统可以自动地侦测和消除而另外一些则需要管理员调整请求 死锁发生在两个或多个进程同时等待被其中一个进程保留着的锁。该进程将不会释放它保留的锁直到它获得被其它进程保留的资源，反过来也一样。当一个死锁被被确认以后，SQL Server通过自动选择可以立即打断死锁的线程来结束死锁。 许多阻塞的问题发生在由于一个进程保留锁过长时间，引起一系列被阻塞的进程等待其它进程释放锁。SQL Server不能识别阻塞锁并自动地解决它们,所以必须监控阻塞锁的存在并手工消除它。 在一个应

12、用中建立一个锁的超时设置是一个防止阻塞锁的方法。这允许应用监控阻塞锁并回滚进程而不是不确定地等待或阻塞语句的重提交。 下面，介绍手工消除死锁的方法： 1) 系统长时间没有响应，可以在SQL查询分析器中执行系统存储过程sp_lock 和sp_who ， 如图所示，spid 57正在等待资源。 Spid ：系统进程 ID 执行命令：sp_who 57 可以得到关联该进程和锁的用户的登录名称，主机名称和状态等信息。 图1. 运行sp_lock显示的锁信息 2) 转到SQL Server Enterprise Manager,展开管理 ，展开当前活动， 展开锁 / 进ID ,如图所示，spid57被s

13、pid56阻塞。 图2. 显示锁的阻塞情况 3) 双击spid56,然后单击取消进程 （Kill Process）。 4) spid57阻塞解除。 2 硬件调整 硬件调整，是为K3系统的正常运行要求的工作量提供足够的硬件资源的行动。要调整系统的硬件，就要决定可以为K3系统分配那些资源以改进其性能，这些资源包括附加的内存、CPU、I/O资源或所有这些资源的组合。调整系统性能的工作主要涉及决定应该增加哪种资源，以及增加多少资源。 硬件调整是非常重要的，因为许多典型的性能问题是由不充足的或配置失当的硬件组件导致的。I/O子系统是一个数据库调整的关键性部分。通过提供足够的CPU、内存与I/O资源。可以

14、避免许多性能问题。 通过监控相关的计数器，可以及时发现和解决引起系统性能降低的硬件问题。 2.1 控制内存的使用 SQL Server 要求内存是基于静态内存的需要：一是它自己的程序代码和内部数据结构，例如内核的工作负载，打开对象，锁。二是数据高速缓存。 基于有效的系统资源和这些资源的竞争需要, SQL Server动态地获得和释放数据高速缓存。如果SQL Server的数据高速缓存需要更多的内存，它查询操作系统检查是否有物理内存可以利用。如果有，SQL Server在数据高速 存中使用它并且在内存中保留先前读到的数据。 为阻止Windows 2000页面调度,SQL Server依赖Serv

15、er activity增减数据高速缓存以保留4MB10MB剩余物理内存。对SQL Server不足的内存分配或使用会引起数据连续地从硬盘上而不是高速缓存上读取,这将降低系统的性能。 请观察以下与内存有关的计数器,以便及时发现和解决内存上的问题。 使用工具:性能监视器 监控内存和分页的使用对象: 计数器 描述 指导 Memory: Available Bytes 监控被进程执行使用的有效字节数。 （可用物理内存量） 这个计数器应该总是大于5000KB;低值显示物理内存整体的缺乏和需要提高。 推荐值：大于4MB Memory: Page/sec 为了访问不在内存中的页而读取或写入磁盘的总页数。 该

16、计数器应该从不持续大于零.如果值持续大于零，Windows 2000操作系统正在使用页面调度来填充内存. 推荐值：小于5 Process: Page Faults/sec/SQL Server Instance 缺页/秒 处理器中的Page Faults的计数值。当进程所引用的虚拟内存页不在其主内存的工作集中时，将发生页错误。如果某一页已在主内存中（位与备用列表内），或者它正被共享此页的其他进程使用，Page Fault 将不会导致系统从磁盘调入该页。 这个计数器的高值表明过多的页面调度和磁盘压力,检查是否是SQL Server 或其他的进程引起过多的页面调度。 隔离SQL Server 使用

17、的内存 Process: Working Set/SQL Server Instance 监控用于SQL Server的一个实例的SQL Server进程的内存的 数量。 这个计数器应该大于5000KB。当这个计数器低于5000KB，没有更多的内存可供SQL Server 使用。 SQL Server: Buffer Manager: Buffer Cache Hit Ratio 高速缓存命中率 监控高速缓存中不需从硬盘中读取的页的百分率,。不用区分用于高速缓存的是物理内存还是页面调度内存。 这个计数器应该大于90%，因为它显示的是发现在内存中的页的数量。 SQL Server: Buffer

18、 Manger: Total Pages 监控高速缓存中页的总数量，包括数据库，free和来自其他进程的stolen页。 低值显示连续的磁盘输入输出或压力.考虑增加更多的内存. SQL Server: Memory Manager Total Server Memory 监控服务器正在使用的动态内存的总的数量。 如果该计数器与可用的物理内存比较持续高，则需加更多的内存。 2.2 监控线程和处理器的使用 优化处理器性能是输出量和响应时间之间的一种平衡。 处理器的性能 当你检查处理器的使用，考虑SQL Server实例正在做的工作的类型。如果SQL Server正在做大量的计算，例如包含集合的查询

19、或绑定内存这种不需要磁盘输入输出的查询，100%的处理器时间可能被使用。 对于多处理器的系统,你需要监控每个处理器的这个计数器的分离的实例。确定所有处理器的平均值，可使 计数器：System:% Total Processor Time 。 线程 每个SQL Server的实例都是一个独立的操作系统进程，SQL Server2000的实例使用Windows线程，有时是纤程 去有效的管理并发的任务。 1) 一个进程是一个应用的实例，例如SQL Server并且能有一个或多个任务。 2) 一个线程是进程任务的一种机制，并且被用来计划处理器的时间。 当一个线程处于等待一个操作（例如读写磁盘）完成的空

20、闲期时，Windows 2000操作系统通过转换线程来最大化处理器的使用。线程间的转换叫做context switching. 每个SQL Server的实例用户连接的一个线程池,池中的线程被叫做工作线程。 当Processor: %Processor Time 持续接近100%并且System: Processor Queue Length 显示更多的应用的进程正在等待处理器，或者当System: Context Switches/Sec 较高。显示出现了系统瓶颈。当Processor:% Processor Time 接近100%并且System: Context Switches/Sec

21、 接近8000，考虑更快的处理器，附加的处理器或者转换到使用纤程。 请观察以下与内存有关的计数器,以便及时发现和解决处理器上的问题。 使用工具：Windows 性能监视器 对象: 计数器 描述 指导 Processor: %Processor Time 以处理器运行非空闲线程所经历时间的百分比表示。它被视为用于处理有效工作的时间比。每一个处理器在空闲时将会指定一个空闲线程来消耗未被其他线程使用的处理器时间段。 这个计数器应该低于90%，如果这个计数器较高，应降低工作负荷，提高工作效率或者或加大处理器的能力。 System: Context Switches/sec 监控处理器每秒在线程间转换的

22、次数。 在一个多处理器的计算机上,如果这个计数器达到8000,并且Processor:% Processor Time计数器超过90%,考虑使用SQL Server fiber scheduling. System: Processor Queue Length 监控等待进程时间的线程的数目 这个计数器不应该持续大于2。如果这个计数器持续大于2，降低工作负荷,提高工作负荷的效率，或者增加处理器的能力，在多处理器的系统中可以增加处理器。 Processor: % Privileged Time 在“特权模式”下处理器运行非空闲线程所经历时间的百分比。Windows NT服务层，执行体子程序及Wi

23、ndows NT内核都是在“特权方式”下运行。 如果处理器的大部分时间被用来做系统内核命令,并且物理硬盘的计数器较高，考虑提高硬盘输入输出子系统的性能。 Processor: %User Time 在“用户模式”下处理器运行非空闲线程所经历时间的百分比。所有应用程序码及子系统码都在“用户模式“下运行。 这个能确定其它进程或应用正在执行或阻止SQL Server操作。 2.3 监控硬盘输入输出 SQL Server 使用Windows 2000 I/O calls 执行磁盘的读写。SQL Server管理何时和如何执行磁盘读写，但依赖Windows执行底层的输入输出操作。I/O子系统包括系统总线

24、，磁盘控制卡，磁盘，磁带驱动器，CD-ROM驱动器和许多其它的I/O设备。磁盘经常是系统的最大的瓶颈。 监控硬盘输入输出将帮助你确定读页和写页是否超出硬盘子系统的能力。一个忙碌的硬盘子系统也可以显示不足的内存所引起的过多的页面调度输入输出。 下面的表描述了优化对象计数器，你可以用来监控你的硬盘子系统的性能。 使用工具:Windows 性能监视器 对象：计数器 描述 指导 PhysicalDisk: %Disk Time 所选的驱动器忙于处理读取或写入请求作服务所花费时间的百分比。 这个计数器应当持续低于90%。 推荐值：小于50% PhysicalDisk:Avg.Disk Queue Len

25、gth 指在采样间隔期内，对所选磁盘的读写操作被排入队列的平均次数。 这个计数器应该不超过中心值的两倍。 PhysicalDisk:Disk Read/sec 读取磁盘的速度 这个计数器应该续低于硬盘子系统的能力。 PhysicalDisk: Disk Writes/sec 写入磁盘的速度 这个计数器应持续低于硬盘子系统的能力。 如果这些硬盘计数器显示你的硬盘正在超负荷运行，考虑： 1. 通过使用一个更快的硬盘，提高硬盘输入输出能力 2. 把一些文件转移到一个附加硬盘或服务器上 3. 增加一个硬盘阵列 4. 提高硬盘的数量有助于减少硬盘的压力。 SQL SERVER中一些常见性能问题的总结 1

26、.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： select id from t where num=0 3.应尽量避免在 where 子句中使用!=或操作符，否则将引擎放弃使用索引而进行全表扫描。 4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使

27、用索引而进行全表扫描，如： select id from t where num=10 or num=20 可以这样查询： select id from t where num=10 union all select id from t where num=20 5.in 和 not in 也要慎用，否则会导致全表扫描，如： select id from t where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了： select id from t where num between 1 and 3 6.下面的查询也将导致全表扫描： select id

28、 from t where name like %abc% 若要提高效率，可以考虑全文检索。 7.如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运 行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将 进行全表扫描： select id from t where num=num 可以改为强制查询使用索引： select id from t with(index(索引名) where num=num 8.应尽量避免在 where 子句中对字段

29、进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如： select id from t where num/2=100 应改为: select id from t where num=100*2 9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如： select id from t where substring(name,1,3)=abc-name以abc开头的id select id from t where datediff(day,createdate,2005-11-30)=0-2005-11-30生成的id 应改为: select

30、 id from t where name like abc% select id from t where createdate=2005-11-30 and createdate2005-12-1 10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。 11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。 12.不要写一些没有意义的查询，如需要生成一个空表结构： select col1,col2 i

31、nto #t from t where 1=0 这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样： create table #t(.) 13.很多时候用 exists 代替 in 是一个好的选择： select num from a where num in(select num from b) 用下面的语句替换： select num from a where exists(select 1 from b where num=a.num) 14.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。 15.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不