sybase配置，sp_configure

转：http://blog.csdn.net/zxlyx/article/details/6453729

 Sybase配置参数

sp_configure'maxonlineengines',4

go

--配置启动cpu个数

sp_configure'numberofenginesatstartup',4

go

--配置最大内存数

sp_configure'maxmemory',2097151

go

--分配最大存储过程缓存

sp_configure'procedurecache',102400

go

--配置高速缓存

sp_cacheconfig'defaultdatacache','700M'

go

--缺省缓存分配页大小

sp_poolconfig'defaultdatacache','200M','16K'

go

--网络包大小

sp_configure'maxnetworkpacketsize',1024

go

--最大连接数

sp_configure'numberofuserconnections',500

go

--最大打开对象

sp_configure'numberofopenobject',9000

go

--最大索引

sp_configure'numberofopenindex',10000

go

--最大锁数

sp_configure'numberoflocks',100000

go

--增加网络内存

--sp_configure'additionalnetworkmemory',1024

go

--锁内存

sp_configure'locksharedmemory',512

go

--优化tempdb

selectdbid,name,segmap

fromsysusages,sysdevices

wheresysdevices.low<=sysusages.size+vstart

andsysdevices.high>=sysusages.size+vstart-1

anddbid=2

and(status=2orstatus=3)

go

usetempdb

go

sp_dropsegment'default',tempdb,master

go

sp_dropsegment'logsegment',tempdb,master

select dbid, name,segmap

fromsysusages,sysdevices

wheresysdevices.low<=sysusages.size+vstart

andsysdevices.high>=sysusages.size+vstart-1

anddbid=2

and(status=2orstatus=3)

go

sp_cacheconfigtempdb_cache,'100M'

go

sp_poolconfigtempdb_cache,'50M','16K'

go

sp_bindcache'tempdb_cache',tempdb

go

sp_helpcachetempdb_cache

selectname,idfromsyscharsets

dbcctraceon(3604)

dbccmemusage

1数据库安装的优化

1.1应用数据库使用裸设备

若在UNIX（UNIXWARE）操作系统下安装数据库服务器，请将SYBASE应用数据库的设备(device)安装成裸设备。即在创建应用数据库设备（如：IVSP，DB160，NAP2000等）时用裸设备，把文件名指向/dev/dsk/子目录下的相应文件。系统数据库设备（如:master等）仍然指向文件系统。

1.2补丁程序

完成SYBASE数据库的安装，请注意原版的SYBASE软件都会带有最新的补丁，一定要把补丁打上，否则会出现一些莫名其妙的问题。FORNT版的补丁是一个ZIP文件，解压至C:/SYBASE子目录即可。

1.3安装SYBASE在线帮助

在安装好SYBASE后，在安装目录下有一文件：../scripts/ins_syn_sql，在服务器上执行该脚本：

SybaseforUnix版：./isql-Usa-P-i../scripts/ins_syn_sql

SybaseforWinnt版：isql-Usa-P-i/sybase/scripts/ins_syn_sql

执行完毕后，即可在任意的SYBASE客户端上连接上SQLSERVER，在线取得任意命令的帮助：

sp_syntax"关键字"

如：sp_syntax"alter"即可列出所有包含"alter"字符的命令

2数据库配置的优化

2.1优化master数据库

首先加大master设备空间，初始默认为30M，加大为150M。然后加大master数据库空间，默认数据段和日志段各为5M大小，建议改为数据段100M，日志段50M。

alterdatabasemasteronmaster=95

2.2优化tempdb数据库

首先创建tempdb设备，分配给tempdb数据库，默认tempdb数据库数据段和日志段各为2M大小，并创建在master设备上，建议改为数据段200M，日志段50M，创建在tempdb设备上。

alterdatabasetempdbontempdb=200

SQLSEVRER所有用户都共享工作表和临时表的tempdb数据库，tempdb主要瓶颈是磁盘I/0。解决办法是把tempdb放在更快的设备上。在UNIX环境中，把tempdb放在文件系统中而不用原始的设备。由于tempdb在创建数据时，自动在master设备上创建为2M的数据库，为了减少冲突，最好的办法是把tempdb从master设备中移走。为了达到上述目的，可采用如下办法实现：

1：在单用户状态下启动SQLSERVER

启动单用户方法：dataserver-dmaster.dat-m

2：以sa登录

3：在文件系统中创建一个哑数据库。

4：删除sysusages和sysdatabase表中对现有tempdb数据库的引用

5：获取哑数据库的数据库ID，相应修改sysusages和ysdatabase表对tempdb的引用

6：重新启动数据库

以在newdevice中创建200M的tempdb数据库为例，执行过程如下：

createdatabasenewtemponnewdevice=200

go/*创建新的数据库*/

begintran

go/*开始事务，防止操作错误时破坏整个SQLSERVER的运行*/

deletesysusageswheredbid=2

deletesysdatabaseswheredbid=2

go/*删除系统表对tempdb的引用，只能在单用户状态下执行*/

selectdbidfromsysdatabaseswherename=‘newtemp’

go/*获取newtemp数据库ID，假定为10*/

updatesysusagessetdbid=2wheredbid=10

updatesysdatabasessetname=’tempdb’,dbid=2wherename=’newtemp’

go/*修改数据库的引用，对newtemp的引用改为对tempdb的引用*/

selectname,dbid,fromsysdatabaseswherename=‘tempdb’

select*fromsysusageswheredbid=2

go/*测试修改是否正确，正确则提交，否则可用rollback回退*/

committran

go/*修改成功，重新启动系统*/

这种方法只对tempdb有效，其他数据库不能采用这种方法。因为在SQLSERVER启动时，tempdb每次都重新初始化。

2.3优化系统参数

以下参数为标准建议值，可根据实际情况修改。

优化系统参数的SQL脚本注释

sp_configure'totalmemory',100000优化数据库的内存，应根据不同机器配置设置,建议为一半的物理内存大小。以db_block为单位，即每个单位为2k，上例为200M，默认为24M.

sp_configure"lockscheme",1,"datarows"系统默认为表级锁，优化为行锁

sp_configure"numberoflocks",10000加大最大锁进程数,默认为5000。

（如果设置一个已经存在的表(tabel)的锁方式为行锁，则执行：

altertabletable_namelockdatarows）

sp_configure'procedurecachepercent',30缺省值：20建议值：procedure使用频率高时采用较大的值，不超过30

sp_configure"numberofuserconnections",100最大用户连接数，默认为25，每个连接要占70k内存

sp_configure'numberofdevices',20将最大设备文件数据改为15个

sp_configure‘numberofOpendatabases’,20最大打开数据库个数，对于需在一台数据库服务上打个多个数据库则需加大此参数，默认为15

Sp_configure‘maxonlineenginesCPU’,2缺省值：1建议值：采用实际机器的CPU个数

Sp_configure‘totaldatacachesize’,60000缺省值：0建议值：使用SQLSERVER内存的30%

如果上述参数改动后SYBASE启动不正常，则可检查SYBASE的错误日志，把SYBASE.cfg中的相应选项修改为较小的值。

附：SYBASESQLSERVER内存的分配

1.SQLSERVER可执行代码3-4M

2.SQLSERVER使用的静态内存2.2-3.25M

3.用户可配置的参数所占用内存，以下示例(11.9.2版)：

默认值占用内存

用户连接数(userconnections)25每个约70k

打开的数据库数(opendatabase)12每个约60k

打开的对象数(openobjects)500每个约1k

打开的索引数(openindexs)500每个约1k

锁数目(locks)5000每个约0.1k

数据库设备数(datadevice)10每个约0.5k

4.剩余部份分配给

过程缓存(由procedurecachepercent决定，默认值为20%)

数据缓存(默认值为减去1、2、3项的80%)

2.4优化数据库系统属性

在sybasecenter中选择数据库属性，将属性中options选项中的下列项目选中。

allowselectinto/bulkcopy

truncatelogoncheckpoint

checkpointonrecovery

aborttransctiononfulllog

freespaceaccounting

allownullsbydefault

autoidentitycolumninnon-unquieindex

方法二：在SQLPLUS中执行下列SQL脚本

如：

sp_dboptionmydb,"aborttranonlogfull",true（设定当数据库的日志空间满时，就终止该进程，使用sa用户）

sp_dboptionmydb,"selectinto/bulkcopy",true

sp_dboptionmydb,"trunclogonchkpt",true

sp_dboptionmydb,"nochkptonrecovery",true

sp_dboptionmydb,"nofreespaceacctg",true

sp_dboptionmydb,"allownullsbydefault",true

sp_dboptionmydb,"autoidentitydbouseonly",true

2.5创建阈值存储过程

可根据不同的应用修改以下脚本或创建多个阈值存储过程，并在配置阈值时指定相应的存储过程。

createproceduresp_thresholdaction

@dbnamevarchar(30),@segmentnamevarchar(30),@free_spaceint,@statusint

asdumptransaction@dbnamewithno_log

print"LOGDUMP:'%1!'for'%2!'dumped",@segmentname,@dbname

go

2.6配置多个阈值

方法一：

打开SybaseCentral，双击相应数据库(database)的段Segments->;logsegment，在Thresholds页面中可设置自动清除日志的阀值。其中有LastChance的一行是系统默认的最后机会阀值，即系统日志空闲空间小于该值时为最后一次自动清除日志的机会。设置时阀值的大小可设为日志总空间大小的20%左右。

另外再增加多个阈值。

方法二：

1、使用如下指令查出数据库中日志的容量（用页表示）

selectsum(size)frommaster..sysusageswheredbid=db_id("database_name"and(segmap&4)=4

2、使用sp_addthreshold增加新的阈值，大小为日志容量的50%,如上面语句显示值为2048

sp_addthresholddatabase_name,logsegment,1024，proc_dump_display

注意：因一个大事务时可能会越过当前的threshold，所以必须加多个threshold，

使用命令select@@thresh_hysteresis查看数据库的滞后值，如结果为64页，则下一个阈值设为"最近的阈值-（2*64）"，请在所设阈值再按这种原则各增加两个更小的阈值。

3索引的优化

在良好的数据库设计基础上，需高效地使用索引，并经常的维护索引，下文介绍关于索引的相关内容。

3.1创建索引

索引分为三类：聚簇索引(clusteredindexes)、非聚簇索引(nonclusteredindexes)、覆盖索引(coveringindexes)

鉴于索引加快了查询速度，但减慢了数据更新速度的特点。可通过在一个段上建表，而在另一个段上建其非聚簇索引，而这两段分别在单独的物理设备上来改善操作性能。

create[unique][clustered|nonclustered]indexindex_nameontable_name(column_name...)

3.2重建索引

随着数据行的插入、删除和数据页的分裂，有些索引页可能只包含几页数据，另外应用在执行大块I/O的时候，重建非聚簇索引可以降低分片，维护大块I/O的效率。重建索引实际上是重新组织B-树空间。在下面情况下需要重建索引：

(1)、数据和使用模式大幅度变化。

(2)、排序的顺序发生改变。

(3)、要进行大量插入操作或已经完成。

(4)、使用大块I/O的查询的磁盘读次数比预料的要多。

(5)、由于大量数据修改，使得数据页和索引页没有充分使用而导致空间的使用超出估算。

(6)、dbcc检查出索引有问题。

当重建聚簇索引时,这张表的所有非聚簇索引将被重建.

3.3索引统计信息的更新

当在一个包含数据的表上创建索引的时候，SQLServer会创建分布数据页来存放有关索引的两种统计信息：分布表和密度表。优化器利用这个页来判断该索引对某个特定查询是否有用。但这个统计信息并不动态地重新计算。这意味着，当表的数据改变之后，统计信息有可能是过时的，从而影响优化器追求最有工作的目标。因此，在下面情况下应该运行updatestatistics命令：

(1)、数据行的插入和删除修改了数据的分布。

(2)、对用truncatetable删除数据的表上增加数据行。

(3)、修改索引列的值。

4查询优化

4.1NOTIN子句

不知大家是否喜欢使用‘NOTIN’这样的操作，如果是，那尽量使用(NOT)EXISTS替代。

例子：

语句1

SELECTdname,deptnoFROMdeptWHEREdeptnoNOTIN(SELECTdeptnoFROMemp);

语句2

SELECTdname,deptnoFROMdeptWHERENOTEXISTS(SELECTdeptnoFROMempWHEREdept.deptno=emp.deptno);

明显的，2要比1的执行性能好很多，因为1中对emp进行了fulltablescan,这是很浪费时间的操作。而且1中没有用到emp的index，因为没有where子句。而2中的语句对emp进行的是rangescan。

4.2海量查询

在海量查询时尽量少用格式转换。

如用

WHEREa.order_no=b.order_no

而不用

WHERETO_NUMBER(substr(a.order_no,instr(b.order_no,'.')-1)=TO_NUMBER(substr(a.order_no,instr(b.order_no,'.')-1)

3）查询海量数据是可以使用optimizerhints，例如/*+ORDERED*/

如

SELECT/*+FULL(EMP)*/E.ENAMEFROMEMPEWHEREE.JOB='CLERK';

而不是

SELECTE.ENAMEFROMEMPEWHEREE.JOB||''='CLERK';

对于数据量较大和业务功能较复杂的系统，Sybase的默认参数根本无法满足需要，必须进行优化。系统数据库方面的优化从两方面进行，一个是调整数据库系统的一些性能参数的值，另一个是应用程序的调优。应用程序的调优调整hsql和sql的写法，配合sql合理的建索引，这里主要对Sybase系统一些基本的性能参数的调优进行一个小结。

1、内存

内存是对性能影响最大，也是最需要也是最难调优的地方。内存调优一定要，常用的需要调整的参数有：

sp_configure"maxmemory",0,"2600M"(设置为共享内存的75%,重启生效)

sp_configure"allocatemaxsharedmem",1(启动的时候自动分配maxmemory指定的最大内存)

sp_cacheconfig"defaultdatacache","1300m"(设置数据缓存为maxmemory的一半)

sp_cacheconfig"defaultdatacache","cache_partition=2″

sp_configure"procedurecachesize",102400(过程高速缓存,通常是maxmem20%-30%,这里是200M,在大量的执行sql的时候这个参数一定要调大)

sp_cacheconfig‘tempdb_cache’,'100m’,'mixed’(创建一个100M命名高速缓存tempdb_cache给temdpb使用)

sp_bindcache‘tempdb_cache’,tempdb(将tempdb_cache绑定到tempdb)

实际中遇到一个很头痛的问题，32位Windows版本的Sybase最大内存只能到搞到3G左右，"defaultdatacache"的值超过1500MSybase实例就起不来了，导致服务器的16G内存形同虚设，所以如果大家的项目和我这个类似，服务器和操作系统由客户提供，还换不了的，内存不妨要求个4G就够了，多了也浪费。

2、CPU

当服务器的CPU个数多于一个时，可以考虑多CPU。实际上对于OS会自动调度，设一下只不过是控制的更精确一点。实际需要根据CPU数来修改，若CPU=N，一般设置为N-1。设置这个参数，比如我的服务器8个CPU,就像下面这样设置：

sp_configure"maxonlineengines",7

sp_configure"numberofenginesatstartup",7

sp_configure"maxparalleldegree",1(并行的度，大于或等于1)

sp_configure"numberofworkerprocesses",7(并行度＊并发连接数＊1.5倍)

3、连接数(这个没什么说的，数量管够就可以，默认数为25，可根据应用需要来修改。)

sp_configure"numberofuserconnections",600

查询数据库死进程

select*frommaster..syslogshold

4、锁

数据库的锁机制其实是一个比较复杂的话题，这里只能简单说一下。Sybase数据库系统两个级别的锁机制：所有页锁、数据页锁。所有页锁在当数据库加锁时，既锁数据页，也锁索引页；数据页锁当数据库加锁时，只锁数据页，不锁索引页。

Sybase支持三种类型的锁:数据表锁、数据页锁、数据行锁。一些常用的调优命令和策略如下:

sp_configure"numberoflocks",50000（设置锁的数量）

系统设置时要把锁的数量设大一点，简单说就是要管够；如果需要节省空间，减少维护量，使用所有页锁机制；而如果需要加快速度，空间足够，使用数据页锁机制。

sp_sysmon"00:10:00",locks（检测表的使用情况）

当通过监测发现锁竞争超过15%时，首先修改加锁最重的表的锁机制，然后再把数据页锁设置为数据行锁。如果发现螺旋锁多，则为该表建立单独的命名缓存并对命名缓存进行分区。

5、I/O

数据库调优总的思路是尽量减少和分散物理I/O，尽量减少网络I/O。

减少物理I/O的办法有：在命名缓存中增加大块的I/O缓冲池，把数据分散到多个硬盘上,采用RAID技术,建立段，使一个表跨越多个硬盘等等，基本和其他的数据库软件调优一样。

减少网络I/O的办法是采用大数据包。

sp_configure"defaultnetworkpacketsize",2048设置网络传送包的大小(需要重启动)

sp_configure"maxnetworkpacketsize",2048

6、设备调整

主要调整两块：一个是业务数据库的数据设备与日志设备必须分开，添加临时数据库设备；另一个是调整Tempdb，这一条很重要却很容易被大家忽视。

Tempdb是sybase数据库当中的临时库，用于存放中间结果和临时表。由于使用很频繁而默认大小又很小，我们需要加大其设备空间和库的大小，尽可能把tempdb放置到最快的硬盘上，并建立单独的命名缓存。

sp_cacheconfig‘tempdb_cache’,'200m’,'mixed’(创建一个200M命名高速缓存tempdb_cache给temdpb使用)