Sybase性能调优要点Word下载.docx
《Sybase性能调优要点Word下载.docx》由会员分享，可在线阅读，更多相关《Sybase性能调优要点Word下载.docx（20页珍藏版）》请在冰点文库上搜索。
另外，为了对数据库性能进行调整，需要十分清楚数据库存储数据的底层细节，如数据页、索引页的物理结构、每一行的大小计算、不同类型列占用的宽度等问题，只有具备了这些知识，才能深入领会各种调优措施。
SQLServer级的调优
数据库性能优化的首要问题是内存管理。
数据库占用的共享内存分成数据缓冲区（DataCache）、存储过程缓冲区（ProcedureCache）等几部分。
在ISQL下使用“sp_configure‘cache’”可以看到存储过程缓冲区所占百分比（ProcedureCachePercent）和整个数据缓冲区大小（TotalDataCacheSize）等参数。
存储过程缓冲区保存有以下对象的查询计划：
存储过程、触发器、视图、规则、缺省、游标等。
存储过程不可重入，即每个并发用户调用都会在内存中产生一个拷贝。
当存储过程、触发器、视图被装载到存储过程缓冲区时，被查询优化器优化，建立查询计划。
如果存储过程在缓冲区中，被调用就不需要重新编译。
如果存储过程缓冲区太小，存储过程就会经常被其他调入内存的存储过程覆盖掉，当再次被调用时，存储过程又被调入内存，再重新编译，用户请求因此不得不等待。
最严重的情况，如果存储过程缓冲区不够，存储过程甚至都不能运行。
所以在内存足够的情况下，存储过程缓冲参数应尽可能大一些。
数据缓冲区用来缓存数据页和索引页，给服务器增加物理内存以扩大数据缓冲区，是提高数据库性能最有效的方法。
当然，如果不能增加内存，就只能通过减少存储过程缓冲区的比例等方法来扩大数据缓冲区了。
要把数据提前读入内存，有两种方式，即预取策略或大I/O策略（PrefetchStrategy）和取后马上丢弃策略（Fetch-and-Discard）、提示策略（Hints）等几种。
可以在以下三个级别上分别设置表数据的预取策略（PrefetchStrategy，即大I/O策略）：
对象级、会话级、查询级。
如果三个级别上都有设置，它们发生作用的优先顺序是：
对象级>
会话级>
查询级。
在决策支持系统应用中常常需要较大的I/O，这时应该开放largeI/Ostrategy预取策略。
如果一个应用倾向于OLTP特征，用户可以在会话级关掉Prefetch来提高性能。
同样，对于OLTP应用，还可关闭largeI/Ostrategy预取策略。
如果所取页不会有重用的情况，应开放fetch-and-discard策略。
锁的优化是数据库级调优的另一个重要内容。
锁优化最重要的工作是设置页级锁升级成表级锁的阀限。
要尽量避免页锁很快升级成表级锁，同时减少锁的争夺。
管理临时库和多引擎（MultipleNetworkEngines）也是数据库级调优的一个重要内容。
管理临时库的一个重要原则是要避免临时表跨多个设备，可以把tempdb从master设备中分离出来，放到一个单独的设备上去。
这样可以减少存取系统表时对I/O资源的争夺。
多引擎是指操作系统使用了多个CPU。
如果有多个CPU，可用sp_configure来配置数据库的参数：
在线引擎数（MaxOnlineEngines）。
可以扩展系统的网络I/O容量，将网络I/O分布到各个引擎，从而提高性能，以允许更多的用户连接。
为了改善数据库的性能，设备的优化也必不可少。
把最常插入的表分区放在多个设备上，这样可以创建多个页链，从而改善多个并发插入时的性能，因为每一个插入都要找到页链，页链有多个，就允许多个插入同时进行。
这一点，尤其适用于客票系统的存根表和订票存根表，所带来的性能改善会非常明显。
物理I/O的代价远大于逻辑I/O，所以要尽量减少磁盘进行物理I/O的次数，尽量多进行内存中的逻辑I/O。
可以使用“statisticsio”工具和sp_sysmon来观察磁盘I/O。
可以配置使用大的I/O来减少物理I/O的次数，方法有三个，分别是用更多的磁盘、表和索引分开到不同的磁盘和增加一次I/O系统参数值的大小。
SQLServer总是为I/O请求建立一个磁盘检查的调度环。
用sp_configure“I/Opollingprocesscount”来提高数值，加长环，可以降低引擎的检查次数，提高吞吐量。
但较小的值一般有助于减少响应时间。
数据库设计级的调优
在数据库的基础理论中，倡导使用规范化的数据库设计方法，简称范式设计。
用范式来设计数据库，可以减少数据冗余度，减少插入、更新和删除异常，也可以提高性能。
但是有时为了提高某些特定的性能，有意打破范式设计，这样可以达到最好的效果。
但这种情况下，一定要注意数据完整性维护的问题。
降范式设计这种方式一般可以提高检索速度，但会略微降低数据修改性能。
对于应用开发来说，有些情况下，降范式设计还能简化应用程序的编码。
具体而言，降范式设计一般能带来如下好处：
减少表连接的需要，减少外部键和索引，减少表的数量，聚合列可以预先计算等。
有如下方法可以实现降范式设计：
●增加冗余列。
●增加导出列，从一个或多个表的几个列中导出另外一个列。
●收拢表，几个表合成一个表。
●复制表，即制作表的副本。
●将表分开,分为垂直和水平两种。
水平分开可以考虑把表中不太活跃的数据放置在一个表中，而把经常变动的数据放在另外一个表中。
垂直分开则是把多个列分成几组，每一组列成一个表。
但是，降范式设计会带来数据相关性问题，必须仔细考虑。
有以下几点措施可以帮助解决：
●尽量在空闲时刷新只读表。
●多用批处理。
●用触发器来维护。
是否要采用降范式设计，必须根据具体应用综合考虑。
这种设计理念往往紧密结合具体应用，和应用的相关度很高，所以要求数据库分析员兼具业务分析员的角色。
应用程序级的调优
如何进行应用程序级别的调优，是一个十分复杂的问题，也没有统一的方法，这里是我们通常可以采用的几个方法，供读者参考。
1．有效使用索引
查询条件和索引的配合使用，对SQL语句的性能至关重要。
下面是两种常见的情况：
（1）如果查询条件中包括索引的第一个列，而且结果列都在索引列中，系统使用匹配索引定位，会定位到索引的页级，这时可从索引页中直接提取结果，不需要使用数据页。
（2）如果查询条件中不包括索引的第一个列，而且结果列都在索引列中，系统使用非匹配索引扫描，不扫描数据页，从索引页中直接提取结果。
这种情况也不使用数据页。
2．创建高效率查询
可以充分利用索引的where条件书写格式为“columnoperatorexpression”,这里的operator一般是：
=,>
<
>
=,<
=,isnull。
而如果operator是!
=、!
>
便不能充分利用索引。
如果要充分利用索引，在column中就不要包括函数和其他操作。
expression必须是常量或可以转化成常量。
查询优化器认为，between相当于“>
=”和“<
=”，“like'
Ger%'
”相当于“>
='
Ger'
and<
'
Ges'
”。
但是“like'
%ber'
”因为没有给出首字母，就不能转化成这种结果。
在书写SQL语句时，对于表连接的情况，注意尽量少写冗余条件。
一般要在SARGs（搜索参数）的列上放置一个索引。
如果被查询列都包括在索引列中，这种查询叫索引覆盖查询。
这种查询效率比较高，应尽量使用这种查询。
在做表连接查询时，在外表的连接列上建立索引，可以大大加快速度。
而且，查询速度也和表的排列顺序有关，如果行数大的表放在后面，可以提高速度。
3．孤立级0的妙用
当应用需要较好的并发性，并且近似的查询结果也可接受的情况下，SQL语句可以使用孤立级（IsolationLevel0），尤其是对于有多个处理器环境下的OLTP应用。
IsolationLevel0扫描不获取锁，所以不需要内部重扫描，因而大大提高效率。
在客票系统的余票查询应用中，因为余票查询的结果是一个动态而近似的参考数值，只对很短的一段时间内有效，供指导售票之用，不需要很精确，所以对于余票查询模块，包括综合查询中的子功能，计划管理中的子功能，尤其是前台售票中的子功能，采用了孤立级技术，很好地改善了售票高峰期售票程序长久没有反应、相互等待的现象。
但需注意，IsolationLevel0忽略查询优化器，依赖惟一索引，所以要慎重创建SQL语句，最好由有丰富经验的程序员来完成。
4．存储过程的重编译
存储过程执行的时候带上参数“withrecompile”,可以让查询优化器更新查询计划。
当在表上增加索引，或者执行了“updatestatistics”指令后运行“sp_recomplietable_name”，则所有依赖于此表的存储过程下次运行时被重新编译，即更新它们的查询计划。
如果存储过程中会创建临时表，它总是重新生成查询计划。
当表中被查询计划使用的索引或者对象被删除后，存储过程总会自动重新编译。
要注意如有必要，应尽可能经常地编译存储过程，使存储过程的查询计划和数据库的数据存放结构保持一致。
对于客票系统，每次备份删除数据，增加或重建索引后，要执行“updatestatistics”指令，然后运行“sp_recomplietable_name”来更新相关存储过程的查询计划。
5．使用游标时的性能考虑
因为游标会引起页级和表级锁，且消耗网络资源，又有较多的处理指令，所以除非必要，尽量不用游标，而采用等价的SQL语句，即使SQL语句会涉及到多个表扫描，仍然会更好。
对于客票系统中大量的存储过程，尤其是使用最频繁的取票、取车次等几个存储过程，进行了重点优化，减少了游标的使用。
最后要特别指出的是，在数据库性能调优时，一定要建立周密的调整计划和性能基准报告，不能想到哪一项就调整那一项。
有时候，SQLServer级选项的设置还需要重新启动数据库，所以要规划调整时间，尽量在不影响生产的情况下做完可做的工作，然后利用停机时间做影响全局的工作。
在调整数据库性能时，还可能带来影响业务正常运行的风险，所以务必要由经验丰富的管理员慎重实施。
数据库日常维护工作是系统管理员的重要职责。
其内容主要包括以下几个部分：
一、备份系统数据
SYBASE系统的备份与恢复机制保证了在系统失败时重新获取数据的可能性。
SQLServer提供了两种不同类型的恢复机制：
一类是系统自动完成的恢复，这种措施在每次系统启动时都自动进行，保证了在系统瘫痪前完成的事务都写到数据库设备上，而未完成的事务都被回退；
另一类是人工完成的恢复，这是通过DUMP和LOAD命令来执行人工备份和恢复工作。
因此定期备份事务日志和数据库是一项十分重要的日常维护工作。
1、备份数据库
每一个数据库都应在创建之后卸出，从而提供一个装入基点。
在此之后按排定的时间周期表卸出。
比如每周五卸出数据库。
对一般数据库系统卸出数据库周期建议为每周一次。
除了按计划周期卸出数据库之外，还需在每次运行没有日志的操作后卸出数据库。
例如：
·
每次强制地运行了DUMPTRANWITHNO_LOG（因为数据库的磁盘空溢出）；
每次用sp_dboption允许selectinto/bulkcopy做快速拷贝，或用SELECTINTO命令创建一个永久性的表，或使用了WRITETEXT命令。
卸出数据库的命令为：
DUMPDATABASEdatabase_name
TOdump_device
database_name是要卸出的数据库名称，dump_device是卸出设备的名称。
用系统过程sp_helpdevice可以获得设备的信息。
下面一条命令用来卸出数据库my_db：
DUMPDATABASEmy_db
TOdb_bk_dev
2、备份事务日志
如果事务日志与数据库放在同一个设备上，则事务日志不应与数据库分开备份。
master数据库和小于4M的用户数据库就是这种情况。
一般数据库系统的数据库和日志分别放在不同的设备上，因此，可以用DUMPTRAN命令单独备份日志。
备份事务日志的周期直接影响数据的恢复程度，因此建议每天备份。
备份事务日志的命令格式为：
DUMPTRANsactiondatabase_name
[TOdump_device]
[WITHTRUNCATE_ONLY|WITHNO_LOG|WITHNO_TRUNCATE]
其中database_name是要备份事务的数据库名称，dump_device是备份设备名称，仅当包含了WITHTRUNCATE_ONLY或WITHNO_LOG子句时，才可以备份到设备。
注意：
如果总是用DUMPDATEBASE（备份数据库及其日志），而不用DUMPTRAN，事务日志将不会刷新，而变得非常庞大。
对于master数据库和小型数据库每次运行DUMPDATEBASE之后应当运行DUMPTRANsaction命令刷新日志。
下面一条命令备份数据库db160的事务日志到备份设备上：
DUMPTRANsactiondb160
TOdb_log_bk_dev
WITHTRUNCATE_ONLY
3、备份数据库及其日志间的相互作用
在至少卸出一次数据库前，卸出事务日志是毫无意义的。
下图显示了备份数据库及其日志间的关系
如果在星期二下午5：
01出现非硬件故障，需要做的所有工作是装入磁带5（参见下一节：
数据恢复），由于磁带5是下午5：
00刚备份的，因此只有备份和装入之间的一分钟内的数据损失。
但是，如果在星期二下午4：
49失效会怎么样呢？
在这种情况下，要装入磁带1（在星期五下午5：
00的卸出）。
然后，依次装入磁带2，3以及4。
这样，系统将恢复到星期二上午10：
00点的状态，星期二的大部分工作丢失了。
此例显示了经常卸出事务的重要性。
二、万一系统失败时恢复数据库系统
如果用户数据库存储的设备失效，从而数据库被破坏或不可存取，通过装入最新的数据库备份以及后来的事务日志备份可以恢复数据库。
假设当前的事务日志存在于一个并没有毁坏的设备上，带着WITHNO_TRUNCATE选项的DUMPTRANsaction命令卸出它。
要恢复数据库按如下步骤去做：
1、如果日志存在于一个分离的设备上，用带着NO_TRUNCATE选项的DUMPTRANsaction命令卸出被毁坏的或者不可存取的用户数据库事务日志。
2、用下面的查询检查设备分配已毁坏数据库的设备使用情况。
必须为同一目的赋同样的空间块。
下面的查询显示了分配给数据库mydb设备使用和尺寸情况：
SELECTsegmap，sizeFROM sysusages
WHEREdbid=
（SELECTdbidFROMsysdatabasesWHEREname=“mydb”）
3、检查查询的输出。
在segmap列的‘3’代表数据分配，‘4’代表日志分配。
size列代表2K数据块的数目。
注意此信息的次序、使用和尺寸部分。
例如，输出为：
egmapSize
--------------------
310240//实际尺寸为：
20M
35120//实际尺寸为：
10M
45120//实际尺寸为：
31024//实际尺寸为：
2M
42048//实际尺寸为：
4M
4、用DROPDATABASE命令删除毁坏设备上的数据库。
如果系统报错，用DBCCDBREPAIR命令的DROPDB选项。
5、删除数据库后，用sp_dropdevice删除毁坏了的设备。
6、用DISKINIT初始化新的数据库设备。
7、重建数据库。
用CREATEDATABASE命令从老的sysusages表拷贝所有的行，并包含第一逻辑设备。
对上例，命令为：
CREATEDATABASEmydb
ONdatadev1=20，datadev2=10
LOGONlogdev1=10
8、用ALTERDATABASE命令重建其余入口。
在此例中，在datadev1上分配更多的空间，命令为：
ALTERDATABASEmydbONdatadev1=2[page]
9、用LOADDATABASE重新装入数据库，然后用LOADTRAN装入前面卸出的日志。
LOADDATABASE命令语法是：
LOADDATABASEdatabase_name
FROMdump_device
LOADTRANsaction命令的语法是：
LOADTRANsactiondatabase_name
卸出数据库和事务日志的缺省权限归数据库所有者，且可以传递给其他用户；
装载数据库和事务的权限也归数据库所有者，但不能传递。
二、产生用户信息表，并为信息表授权；
系统维护人员的另一个日常事务是为用户创建新的信息表，并为之授权。
创建表以及为表授权的方法已经在讲过，在此只将有关命令语法写出来。
创建表的命令为：
CREATETABLEtable_name
（column_1datatype[NULL|NOTNULL|IDENTITY]，
column_2……
）
go
ALTERTABLEtable_name
ADDPRIMARYKEY（column_list）
删除表的命令格式为：
DROPTABLEtable_name
为表授权的命令格式为：
GRANT{ALL|permission_list}
ONtable_nameTOuser_name
go
收回权限的命令格式为
REVOKE{ALL|permission_list}
ONtable_nameFROMuser_name
三、监视系统运行状况，及时处理系统错误；
系统管理员的另一项日常工作是监视系统运行情况。
主要有以下几个方面：
1、监视当前用户以及进程的信息
使用系统过程：
sp_who
说明：
该命令显示当前系统所有注册用户及进程信息，如下表是某系统的信息。
SpidStatusLoginamehostnameblkdbnamecmd
---------------------------------------------------------------
1RunningSascosysv0MasterSELECT
2SleepingNULL0MasterNETWORKHANDLE
3SleepingNULL0MasterDEADLOCKTUNE
4SleepingNULL0MasterMIRRORHANDLER
5SleepingNULL0MasterHOUSEKEEPER
6SleepingNULL0MasterCHECKPOINTSLEEP
从左向右依次显示：
进程号、当前状态、注册用户名、主机名、占用块数、数据库名以及当前命令。
如果监视时发现进程总数接近最大连接数（用系统过程：
sp_configure“userconn”查看）时，应下掉不活动或无关进程，以保证系统正常运做；
另外亦可监视非法用户或用户使用不属于自己使用范围的数据库等情况。
2、监视目标占用空间情况
sp_spaceused
该过程显示行数、数据页数以及当前数据库中由某个目标或所有目标所占用的空间。
如下表是某数据库日志表的信息：
NameRow_totalreserveddataIndex_sizeunused
------------------------------------------------------------
SyslogsNotavail32KB32KB0KBNotavail
日常要监视的主要目标有：
用户数据库、数据库日志表（syslogs）以及计费原始数据表等。
如果发现占用空间过大，对日志表要进行转储；
对其他目标则应扩充空间或清楚垃圾数据。
3、监视SQLServer统计数字
sp_monitor
sp_monitor显示SQLServer的历史统计数字，下表是某系统的统计数字：
Last_runCurrent_runSeconds
May1320001:
27PMMay1320003:
01PM5678
CPU_busyIO_busyIdle
16（6）-0%0（0）-0%5727（5672）-99%
Packets_receivedPackets_sentPacket_errors
--------------------------
升级会员 