K3 wise erp 数据库索引性能优化解决方案.docx
《K3 wise erp 数据库索引性能优化解决方案.docx》由会员分享，可在线阅读，更多相关《K3 wise erp 数据库索引性能优化解决方案.docx（16页珍藏版）》请在冰豆网上搜索。
K3wiseerp数据库索引性能优化解决方案
K3系统数据库索引性能优化解决方案（具体应用篇）
--重建索引速度较慢，请在系统空闲时间进行
DBCCDBREINDEX（t_icitem）
DBCCDBREINDEX（t_item）
DBCCDBREINDEX（t_itemclass）
DBCCDBREINDEX（t_itemright）
DBCCDBREINDEX（t_user）
DBCCDBREINDEX（t_group）
go
ifnotexists（select1fromsysindexeswherename='ix_group_fgroupid'）
createindexix_group_fgroupidont_group（fgroupid）
go
ifnotexists（select1fromsysindexeswherename='ix_itemright_ftypeid'）
createindexix_itemright_ftypeidont_itemright（ftypeid）
go
1SQLServer调整
当用户使用K3系统一段时间以后，发现系统的响应时间越来越长。
这种情形往往是由于账套数据库缺乏维护引起的。
缺乏维护的数据库会存在过多地碎片、过期的统计、隐含着可能的错误查询结果的数据库的逻辑和物理的不一致性，这些都会直接影响系统的性能。
这里介绍解决上述账套数据库性能问题常用的方法。
1.1使用DBCC语句发现和解决上述问题。
DBCC:
数据库一致性检查器。
打开SQL查询分析器，执行如下语句。
uDBCCSHOWCONTIG显示指定表的数据和索引的有关数据碎片的信息DBCCSHOWCONTIG（表名[，索引名]）
在有大的改动的表，引入数据的表，或者引起低效查询的表上使用该语句。
例：
DBCCSHOWCONTIG（’T_ITEM’）
uDBCCDBREINDEX重建指定数据库中表的一个或多个索引。
例1：
重建某个索引
DBCCDBREINDEX（'T_ITEM',uk_item2,80）
例2：
重建所有索引
DBCCDBREINDEX（'T_ITEM',’’,80）
uDBCCSHOW_STATISTICS显示指定表上的指定目标（例如一个索引名称））的当前分布统计信息。
这些统计信息是被SQLServer查询优化器使用的DBCCSHOW_STATISTICS（表名，目标）
例：
DBCCSHOW_STATISTICs（'t_item','pk_item'）
usp_updatestats&UPDATESTATISTICS更新统计信息；sp_updatestats对当前数据库中所有用户定义的表运行UPDATESTATISTICS.
使用UPDATESTATISTICS语句的时机：
在一个空表上创建一个索引，然后在以后应用它。
执行TRUNCATETABLE语句，然后在以后重新应用该表。
通过使用FULLSCAN或SAMPLE选项请求明细的索引统计信息。
例1.UPDATESTATISTICST_ITEM
例2.UPDATESTATISTICST_ITEM（PK_ITEM）
例3.USEAIS20011203150410
EXECsp_updatestats
uDBCCCHECKTABLE检查指定表或索引视图的数据、索引及text、ntext和image页的完整性。
如果你相信一个指定的表可能被破坏了，这条命令非常有用。
uDBCCCHECKDB检查指定数据库中的所有对象的分配和结构完整性。
这条命令发现并修复数据库地址分配和表内部的全部错误。
实际上，CHECKDB验证数据库内部一切事物的完整性，但是，DBCCCHECKDB是一个耗费CPU和磁盘资源的操作，每个需要检查的数据都必须首先从磁盘中读出到内存中。
而且，DBCCCHECKDB使用tempdb进行排序。
要获得较高的DBCC性能，推荐在下面的情况下运行DBCC：
l在系统使用率较低的情况下运行CHECKDB；
l确信当前没有执行其他磁盘I/O操作，如磁盘备份操作；
l将tempdb放在另一个磁盘系统上，或者放在一个快速磁盘子系统上；
l为tempdb提供足够的空间，运行DBCC带上参数ESTIMATEONLY（显示执行DBCCCHECKDB操作所需tempdb空间的数量），估计tempdb需要多少磁盘空间；
l避免运行消耗大量CPU时间的查询和批处理；
l在DBCC命令运行时，减少事物活动；
l使用NO_INFOMSGS选项（压缩使用空间使用的信息和报告）减少处理和tempdb使用率。
例：
DBCCCHECKDB（'AIS20011203150410'）WITHNO_INFOMSGS,ESTIMATEONLY
uDBCCSQLPERF提供有关所有数据库中的事务日志空间使用情况的统计信息。
日志文件的闲余空间的减少，会降低系统的性能。
系统会在备份时日志截断日志文件，所以要求用户要制定一份良好的备份方案。
例：
DBCCSQLPERF（LOGSPACE）
1.2使用数据库维护计划
使用数据库维护计划器是一种标准且方便的可对多个账套数据库同时设置维护任务维护模式。
下面介绍其建立方法：
本方案所介绍的数据库维护计划侧重于数据库的优化,即性能的提高。
1）打开EnterpriseManager，展开服务器，展开管理,然后单击数据库维护计划。
从操作（Action）中选择新建维护计划,可以看到图4.1所示的欢迎屏幕，单击下一步按钮。
2）选择数据库，选择K3账套所在的数据库（可选一个或多个）。
单击下一步按钮。
图2选择数据库
3）更新数据库优化信息。
选择重新组织数据和索引页，选择使用原有可用空间重新组织页面。
选择当增长超过50MB时，从数据库文件中删除未使用空间，收缩后保留的可用空间为10%的数据空间。
单击下一步按钮。
图3更新数据库优化信息
4）检查数据库完整性。
选择检查数据库完整性,包含索引以及尝试修复所有小问题。
单击下一步。
图4检查数据库完整性
5）指定数据库备份计划,备份在优化方案中暂不考虑，跳过，单击下一步。
图5数据库备份计划
6）指定事务日志备份计划在优化方案中暂不考虑，跳过，单击下一步。
图6指定事物备份计划
7）生成报表。
选择将报表写入目录中的文本文件，选择删除早于4周的报表文件。
或者选择将电子邮件报表发送到操作员，然后花时间阅读这个报表，看看数据库中是否有任何需要注意的问题。
单击下一步。
图7生成报表
8）维护计划历史记录。
SQLServer每次运行时保持维护计划的历史。
可以浏览这个历史，看看操作中何时遇到故障，然后确定故障原因。
如果只有单台机器，则要在本地服务器存放历史纪录，但如果网络中又多台机器，则要将历史纪录存放在中央服务器中，以便从各台机器上方便的访问。
下面选择缺省在本地存放1000行历史纪录。
单击下一步。
图8维护历史纪录
9）完成数据库维护计划向导。
用于命名和检查具体工作，在计划名中输入：
K3账套数据库维护计划。
单击完成按钮生成计划。
图9完成数据库维护计划向导
1.3发现死锁和消除死锁
死锁形成的原因是不同的，有的死锁系统可以自动地侦测和消除而另外一些则需要管理员调整请求
死锁发生在两个或多个进程同时等待被其中一个进程保留着的锁。
该进程将不会释放它保留的锁直到它获得被其它进程保留的资源，反过来也一样。
当一个死锁被被确认以后，SQLServer通过自动选择可以立即打断死锁的线程来结束死锁。
许多阻塞的问题发生在由于一个进程保留锁过长时间，引起一系列被阻塞的进程等待其它进程释放锁。
SQLServer不能识别阻塞锁并自动地解决它们,所以必须监控阻塞锁的存在并手工消除它。
在一个应用中建立一个锁的超时设置是一个防止阻塞锁的方法。
这允许应用监控阻塞锁并回滚进程而不是不确定地等待或阻塞语句的重提交。
下面，介绍手工消除死锁的方法：
1）系统长时间没有响应，可以在SQL查询分析器中执行系统存储过程sp_lock和sp_who，如图所示，spid57正在等待资源。
Spid：
系统进程ID
执行命令：
sp_who57可以得到关联该进程和锁的用户的登录名称，主机名称和状态等信息。
图1.运行sp_lock显示的锁信息
2）转到SQLServerEnterpriseManager,展开管理，展开当前活动，展开锁/进ID,如图所示，spid57被spid56阻塞。
图2.显示锁的阻塞情况
3）双击spid56,然后单击取消进程（KillProcess）。
4）spid57阻塞解除。
2硬件调整
硬件调整，是为K3系统的正常运行要求的工作量提供足够的硬件资源的行动。
要调整系统的硬件，就要决定可以为K3系统分配那些资源以改进其性能，这些资源包括附加的内存、CPU、I/O资源或所有这些资源的组合。
调整系统性能的工作主要涉及决定应该增加哪种资源，以及增加多少资源。
硬件调整是非常重要的，因为许多典型的性能问题是由不充足的或配置失当的硬件组件导致的。
I/O子系统是一个数据库调整的关键性部分。
通过提供足够的CPU、内存与I/O资源。
可以避免许多性能问题。
通过监控相关的计数器，可以及时发现和解决引起系统性能降低的硬件问题。
2.1控制内存的使用
SQLServer要求内存是基于静态内存的需要：
一是它自己的程序代码和内部数据结构，例如内核的工作负载，打开对象，锁。
二是数据高速缓存。
基于有效的系统资源和这些资源的竞争需要,SQLServer动态地获得和释放数据高速缓存。
如果SQLServer的数据高速缓存需要更多的内存，它查询操作系统检查是否有物理内存可以利用。
如果有，SQLServer在数据高速
存中使用它并且在内存中保留先前读到的数据。
为阻止Windows2000页面调度,SQLServer依赖Serveractivity增减数据高速缓存以保留4MB~10MB剩余物理内存。
对SQLServer不足的内存分配或使用会引起数据连续地从硬盘上而不是高速缓存上读取,这将降低系统的性能。
请观察以下与内存有关的计数器,以便及时发现和解决内存上的问题。
使用工具:
性能监视器
监控内存和分页的使用
对象:
计数器
描述
指导
Memory:
AvailableBytes
监控被进程执行使用的有效字节数。
（可用物理内存量）
这个计数器应该总是大于5000KB;低值显示物理内存整体的缺乏和需要提高。
推荐值：
大于4MB
Memory:
Page/sec
为了访问不在内存中的页而读取或写入磁盘的总页数。
该计数器应该从不持续大于零.如果值持续大于零，Windows2000操作系统正在使用页面调度来填充内存.
推荐值：
小于5
Process:
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监控线程和处理器的使用
优化处理器性能是输出量和响应时间之间的一种平衡。
处理器的性能
当你检查处理器的使用，考虑SQLServer实例正在做的工作的类型。
如果SQLServer正在做大量的计算，例如包含集合的查询或绑定内存这种不需要磁盘输入输出的查询，100%的处理器时间可能被使用。
对于多处理器的系统,你需要监控每个处理器的这个计数器的分离的实例。
确定所有处理器的平均值，可使
计数器：
System:
%TotalProcessorTime。
线程
每个SQLServer的实例都是一个独立的操作系统进程，SQLServer2000的实例使用Windows线程，有时是纤程
去有效的管理并发的任务。
1）一个进程是一个应用的实例，例如SQLServer并且能有一个或多个任务。
2）一个线程是进程任务的一种机制，并且被用来计划处理器的时间。
当一个线程处于等待一个操作（例如读写磁盘）完成的空闲期时，Windows2000操作系统通过转换线程来最大化处理器的使用。
线程间的转换叫做contextswitching.每个SQLServer的实例用户连接的一个线程池,池中的线程被叫做工作线程。
当Processor:
%ProcessorTime持续接近100%并且System:
ProcessorQueueLength显示更多的应用的进程正在等待处理器，或者当System:
ContextSwitches/Sec较高。
显示出现了系统瓶颈。
当Processor:
%ProcessorTime接近100%并且System:
ContextSwitches/Sec接近8000，考虑更快的处理器，附加的处理器或者转换到使用纤程。
请观察以下与内存有关的计数器,以便及时发现和解决处理器上的问题。
使用工具：
Windows性能监视器
对象:
计数器
描述
指导
Processor:
%ProcessorTime
以处理器运行非空闲线程所经历时间的百分比表示。
它被视为用于处理有效工作的时间比。
每一个处理器在空闲时将会指定一个空闲线程来消耗未被其他线程使用的处理器时间段。
这个计数器应该低于90%，如果这个计数器较高，应降低工作负荷，提高工作效率或者或加大处理器的能力。
System:
ContextSwitches/sec
监控处理器每秒在线程间转换的次数。
在一个多处理器的计算机上,如果这个计数器达到8000,并且Processor:
%ProcessorTime计数器超过90%,考虑使用SQLServerfiberscheduling.
System:
ProcessorQueueLength
监控等待进程时间的线程的数目
这个计数器不应该持续大于2。
如果这个计数器持续大于2，降低工作负荷,提高工作负荷的效率，或者增加处理器的能力，在多处理器的系统中可以增加处理器。
Processor:
%PrivilegedTime
在“特权模式”下处理器运行非空闲线程所经历时间的百分比。
WindowsNT服务层，执行体子程序及WindowsNT内核都是在“特权方式”下运行。
如果处理器的大部分时间被用来做系统内核命令,并且物理硬盘的计数器较高，考虑提高硬盘输入输出子系统的性能。
Processor:
%UserTime
在“用户模式”下处理器运行非空闲线程所经历时间的百分比。
所有应用程序码及子系统码都在“用户模式“下运行。
这个能确定其它进程或应用正在执行或阻止SQLServer操作。
2.3监控硬盘输入输出
SQLServer使用Windows2000I/Ocalls执行磁盘的读写。
SQLServer管理何时和如何执行磁盘读写，但依赖Windows执行底层的输入输出操作。
I/O子系统包括系统总线，磁盘控制卡，磁盘，磁带驱动器，CD-ROM驱动器和许多其它的I/O设备。
磁盘经常是系统的最大的瓶颈。
监控硬盘输入输出将帮助你确定读页和写页是否超出硬盘子系统的能力。
一个忙碌的硬盘子系统也可以显示不足的内存所引起的过多的页面调度输入输出。
下面的表描述了优化对象计数器，你可以用来监控你的硬盘子系统的性能。
使用工具:
Windows性能监视器
对象：
计数器
描述
指导
PhysicalDisk:
%DiskTime
所选的驱动器忙于处理读取或写入请求作服务所花费时间的百分比。
这个计数器应当持续低于90%。
推荐值：
小于50%
PhysicalDisk:
Avg.DiskQueueLength
指在采样间隔期内，对所选磁盘的读写操作被排入队列的平均次数。
这个计数器应该不超过中心值的两倍。
PhysicalDisk:
DiskRead/sec
读取磁盘的速度
这个计数器应该续低于硬盘子系统的能力。
PhysicalDisk:
DiskWrites/sec
写入磁盘的速度
这个计数器应持续低于硬盘子系统的能力。
如果这些硬盘计数器显示你的硬盘正在超负荷运行，考虑：
1.通过使用一个更快的硬盘，提高硬盘输入输出能力
2.把一些文件转移到一个附加硬盘或服务器上
3.增加一个硬盘阵列
4.提高硬盘的数量有助于减少硬盘的压力。
SQLSERVER中一些常见性能问题的总结
1.对查询进行优化，应尽量避免全表扫描，首先应考虑在where及orderby涉及的列上建立索引。
2.应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
selectidfromtwherenumisnull
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
selectidfromtwherenum=0
3.应尽量避免在where子句中使用!
=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
4.应尽量避免在where子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
selectidfromtwherenum=10ornum=20
可以这样查询：
selectidfromtwherenum=10
unionall
selectidfromtwherenum=20
5.in和notin也要慎用，否则会导致全表扫描，如：
selectidfromtwherenumin（1,2,3）
对于连续的数值，能用between就不要用in了：
selectidfromtwherenumbetween1and3
6.下面的查询也将导致全表扫描：
selectidfromtwherenamelike'%abc%'
若要提高效率，可以考虑全文检索。
7.如果在where子句中使用参数，也会导致全表扫描。
因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运
行时；它必须在编译时进行选择。
然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。
如下面语句将
进行全表扫描：
selectidfromtwherenum=@num
可以改为强制查询使用索引：
selectidfromtwith（index（索引名））wherenum=@num
8.应尽量避免在where子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。
如：
selectidfromtwherenum/2=100
应改为:
selectidfromtwherenum=100*2
9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。
如：
selectidfromtwheresubstring（name,1,3）='abc'--name以abc开头的id
selectidfromtwheredatediff（day,createdate,'2005-11-30'）=0--‘2005-11-30’生成的id
应改为:
selectidfromtwherenamelike'abc%'
selectidfromtwherecreatedate>='2005-11-30'andcreatedate<'2005-12-1'
10.不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
12.不要写一些没有意义的查询，如需要生成一个空表结构：
selectcol1,col2into#tfromtwhere1=0
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
createtable#t（...）
13.很多时候用exists代替in是一个好的选择：
selectnumfromawherenumin（selectnumfromb）
用下面的语句替换：
selectnumfromawhereexists（select1frombwherenum=a.num）
14.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
15.索引并不是越多越好，索引固然可以提高相应的select的效率，但同时也降低了insert及update的效率，因为insert或update时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。
一个表的索引数最好不
升级会员 