SYBASE数据库优化设置.doc

1)优化引擎修改最大在线引擎数参数值“max online engines” 1sp_configure max online engines,4修改启动引擎数参数值“number of engines at startup” 2 sp_configure number of engines at startup,42)优化共享内存修改允许分配最大的共享内存参数值allocate max shared memory3sp_configure allocate max shared memory,1修改最大内存参数值max memory,该值大小一般为服务器(没有其它应用)内存的80%。4sp_configure max memory,820000 (说明：单位为2K的页)3)优化网络连接、设备数量等参数修改用户连接数参数值number of user connections5sp_configure number of user connections,200修改设备数参数值number of devices6 sp_configure number of devices,20修改锁数量参数值number of locks7 sp_configure number of locks,500004)优化tempdb添加tempdb数据库设备8 disk init name=tempdb, physname=/*/tempdb.dat, vdevno=8,size=256000修改数据库tempdb增加tempdb设备9alter database tempdb on tempdb=400从各段中删除master主设备10sp_dropsegment default,tempdb,mastersp_dropsegment system,tempdb,mastersp_dropsegment logsegment,tempdb,master5)优化高速缓存修改过程缓存大小sp_configure procedure cache size,51200为tempdb数据库创建命名高速数据缓存并绑定,同时增加16K的缓冲池sp_cacheconfig tempdb_cache,256Msp_bindcache tempdb_cache,tempdbsp_poolconfig tempdb_cache,128M,16K修改缺省数据高速缓存default data cache大小 sp_cacheconfig default data cache,500M行级锁sp_configure lock scheme,1,datarowsgo索引数sp_configure number of open indexes,5000go 对象数sp_configure number of open objects,5000go6)观察内存分配置状况dbcc traceon(3604)godbcc memusagegodbcc traceoff(3604)go重启数据库服务器两次.6.设置数据库选项Isql Usa Secmtuse mastergo sp_dboption ecmtdb,select into/bulkcopy/pllsort,truegocheckpointgosp_dboption ecmtdb,trunc log on chkpt,true(设置数据库自动截断日志或者在sybase central 得数据库属性对话框得选项页中选择“在检查点上截断日志”项。)gocheckpointgo6.检查应用库Isql Usa Secmtdbcc checkdb(ecmtdb)go执行约几分钟.7.重整索引第一步:先在ecmtdb中生成重整索引的语句select update index statistics + name from sysobjects where type like %U% and name not like z% and name not like y% order by name得到以下的语句: update index statistics FLOWTYPE_10000 update index statistics MID_10000 update index statistics MID_CIRCUIT update index statistics MID_CUSTOMER update index statistics MID_NE update index statistics MID_ROOM update index statistics SMS_10000 update index statistics TAnNumberSeg update index statistics TDataCirDetail update index statistics TDataDevType update index statistics TDeviceType update index statistics TSwi2An update index statistics TSwi2Pas update index statistics TSwiRelType update index statistics TSwitch2User update index statistics TTransCir2Others update index statistics TTransCirRoute 第二步:在ecmtdb中一起执行上面生成的语句update index statistics FLOWTYPE_10000 update index statistics MID_10000 update index statistics MID_CIRCUIT update index statistics MID_CUSTOMER update index statistics MID_NE update index statistics MID_ROOM update index statistics SMS_10000 update index statist