Sybase数据库性能优化的具体过程.docx
《Sybase数据库性能优化的具体过程.docx》由会员分享，可在线阅读，更多相关《Sybase数据库性能优化的具体过程.docx（13页珍藏版）》请在冰豆网上搜索。
Sybase数据库性能优化的具体过程
Sybase数据库性能优化的具体过程
用一个实例讲解了Sybase数据库性能优化的具体过程，具体内容请参考下文：
共享锁
sp_getapplock锁定应用程序资源
sp_releaseapplock为应用程序资源解锁
SETLOCK_TIMEOUT1800锁超时期限设置
sp_configure'deadlockcheckingperiod',5000设置锁检测周期
sp_configure'lockwaitperiod',5000设置锁的等待时间
sp_setrowlockpromote设置基本个表的最大行锁升级数（锁数）
sp_setrowlockpromote'TABLE',TREECODE,500,500,100
sp_setrowlockpromote'TABLE',LCD05,500,500,100
[LockManager]
numberoflocks=50000#锁数
deadlockcheckingperiod=DEFAULT
freelocktransferblocksize=DEFAULT
maxenginefreelocks=DEFAULT
lockspinlockratio=DEFAULT
lockhashtablesize=DEFAULT
lockscheme=DEFAULT
lockwaitperiod=DEFAULT
readcommittedwithlock=DEFAULT
当很多事务同时访问同一个数据库时，会加剧锁资源争夺，严重时事务之间会发生死锁。
可用sp_object_stats查明死锁位置。
该过程报告资源争夺最激烈的10张表、一个数据库中资源争夺的表和单个表的争夺情况。
语法为sp_object_statsinterval[,top_n[,dbname[,objname[,rpt_option]]]]，查看锁争夺情况只需设置interval为“hh:
mm:
ss”。
如果显示每种锁的争夺程度超过15%，应该改变加锁方式，比如表的全页锁改成数据页锁，数据页锁改成数据行锁等。
ParameterNameDefaultMemoryUsedConfigValueRunValue
-----------------------------------------------------
allowremoteaccess1011
printrecoveryinformation0000
recoveryintervalinminutes5055
taperetentionindays0000
ParameterNameDefaultMemoryUsedConfigValueRunValue
-----------------------------------------------------
globalasyncprefetchlimit1001010
globalcachepartitionnumber1011
memoryalignmentboundary2048020482048
numberofindextrips0000
numberofoamtrips0000
procedurecachepercent20224262020
totaldatacachesize089698089698
totalmemory471041966089830498304
ParameterNameDefaultMemoryUsedConfigValueRunValue
-----------------------------------------------------
cisbulkinsertbatchsize0000
cisconnecttimeout0000
ciscursorrows5005050
cispacketsize5120512512
cisrpchandling0000
enablecis1011
maxcisremoteconnections0000
maxcisremoteservers25192525
ParameterNameDefaultMemoryUsedConfigValueRunValue
-----------------------------------------------------
dtmdetachtimeoutperiod0000
dtmlocktimeoutperiod3000300300
enablexactcoordination1011
numberofdtxparticipants500149500500
strictdtmenforcement0000
txntopssratio1636921616
xactcoordinationinterval6006060
ParameterNameDefaultMemoryUsedConfigValueRunValue
-----------------------------------------------------
averagecapsize2000200200
capsperccb5005050
dumponconditions0000
maximumdumpconditions1001010
numberofccbs0000
ParameterNameDefaultMemoryUsedConfigValueRunValue
-----------------------------------------------------
allowsqlserverasynci/o1011
disablediskmirroring0000
diski/ostructures25631256256
numberofdevices10#51010
pageutilizationpercent9509595
ParameterNameDefaultMemoryUsedConfigValueRunValue
-----------------------------------------------------
eventlogcomputernameLocalSystem0LocalSystemLocalSystem
eventlogging1011
logauditlogonfailure0000
logauditlogonsuccess0000
ParameterNameDefaultMemoryUsedConfigValueRunValue
-----------------------------------------------------
espexecutionpriority8088
espexecutionstacksize7782407782477824
espunloaddll0000
startmailsession0000
xp_cmdshellcontext1011
ParameterNameDefaultMemoryUsedConfigValueRunValue
-----------------------------------------------------
configurationfile000/sybase/hgd
ParameterNameDefaultMemoryUsedConfigValueRunValue
-----------------------------------------------------
enablejava0000
sizeofglobalfixedheap3000300300
sizeofprocessobjectheap3000300300
sizeofsharedclassheap3072030723072
ParameterNameDefaultMemoryUsedConfigValueRunValue
-----------------------------------------------------
defaultcharactersetid1011
defaultlanguageid0000
defaultsortorderid5005050
disablecharactersetconversi0000
enableunicodeconversions0011
numberoflanguagesincache3433
sizeofunilibcache014000
ParameterNameDefaultMemoryUsedConfigValueRunValue
-----------------------------------------------------
deadlockcheckingperiod5000500500
freelocktransferblocksize3003030
lockaddressspinlockratio1000100100
lockhashtablesize20484820482048
lockschemeallpages0allpagesallpages
lockspinlockratio8508585
locktablespinlockratio2002020
lockwaitperiod2147483647021474836472147483647
maxenginefreelocks1001010
numberoflocks500023441000010000
printdeadlockinformation0011
readcommittedwithlock0000
ParameterNameDefaultMemoryUsedConfigValueRunValue
-----------------------------------------------------
additionalnetworkmemory0000
allowresourcelimits0000
auditqueuesize10042100100
averagecapsize2000200200
capsperccb5005050
deadlockpipemaxmessages0000
defaultnetworkpacketsize512#505512512
diski/ostructures25631256256
enablerepagentthreads0000
errorlogpipemaxmessages0000
eventbuffersperengine100#11100100
executablecodesize+overhead020261020261
lockhashtablesize20484820482048
lockspinlockratio8508585
maxcisremoteservers25192525
maxnumbernetworklisteners586855
maxonlineengines121611
maxrolesenabledperuser20#222020
memoryperworkerprocess1024010241024
numberofalarms4034040
numberofauxscandescriptors200#258200200
numberofccbs0000
numberofdevices10#51010
numberoflanguagesincache3433
numberoflargei/obuffers69766
numberoflocks500023441000010000
numberofmailboxes3013030
numberofmessages6436464
numberofopendatabases1212391212
numberofopenindexes500512500500
numberofopenobjects500561500500
numberofremoteconnections20865050
numberofremotelogins20232020
numberofremotesites1017291010
numberofuserconnections2543141250250
numberofworkerprocesses0000
partitiongroups102490410241024
permissioncacheentries15#2271515
plantextpipemaxmessages0000
procedurecachepercent20224262020
processwaitevents0000
remoteserverpre-readpackets3#8333
sizeofglobalfixedheap3000300300
sizeofprocessobjectheap3000300300
sizeofsharedclassheap3072030723072
sizeofunilibcache014000
sqltextpipemaxmessages0000
stackguardsize4096#110840964096
stacksize86016#232698601686016
statementpipemaxmessages0000
totaldatacachesize089698089698
totalmemory471041966089830498304
txntopssratio1636921616
waiteventtiming0000
ParameterNameDefaultMemoryUsedConfigValueRunValue
-----------------------------------------------------
numberofopendatabases1212391212
numberofopenindexes500512500500
numberofopenobjects500561500500
openindexhashspinlockratio1000100100
openindexspinlockratio1000100100
openobjectspinlockratio1000100100
ParameterNameDefaultMemoryUsedConfigValueRunValue
-----------------------------------------------------
Qdiagnosticsactive0000
SQLbatchcapture0000
deadlockpipeactive0000
deadlockpipemaxmessages0000
errorlogpipeactive0000
errorlogpipemaxmessages0000
objectlockwaittiming0000
perobjectstatisticsactive0000
plantextpipeactive0000
plantextpipemaxmessages0000
processwaitevents0000
sqltextpipeactive0000
sqltextpipemaxmessages0000
statementpipeactive0000
statementpipemaxmessages0000
statementstatisticsactive0000
waiteventtiming0000
ParameterNameDefaultMemoryUsedConfigValueRunValue
-----------------------------------------------------
additionalnetworkmemory0000
allowremoteaccess1011
allowsendmsg0000
defaultnetworkpacketsize512#505512512
maxnetworkpacketsize5120512512
maxnumbernetworklisteners586855
numberofremoteconnections20865050
numberofremotelogins20232020
numberofremotesites1017291010
remoteserverpre-readpackets3#8333
syb_sendmsgportnumber0000
tcpnodelay0000
ParameterNameDefaultMemoryUsedConfigValueRunValue
-----------------------------------------------------
maxasynci/osperengine2147483647021474836472147483647
maxasynci/osperserver2147483647021474836472147483647
o/sfiledescriptors0001024
tcpnodelay0000
ParameterNameDefaultMemoryUsedConfigValueRunValue
-----------------------------------------------------
maxparalleldegree1011
maxscanparalleldegree1011
memoryperworkerprocess1024010241024
numberofworkerprocesses0000
ParameterNameDefaultMemoryUsedConfigValueRunValue
-----------------------------------------------------
additionalnetworkmemory0000
locksharedmemory0000
maxSQLtextmonitored0700
sharedmemorystartingaddress0000
totalmemory471041966089830498304
ParameterNameDefaultMemoryUsedConfigValueRunValue
-----------------------------------------------------
maxonlineengines121611
minonlineengines1011
ParameterNameDefaultMemoryUsedConfigValueRunValue
-----------------------------------------------------
enablerepagentthreads0000
ParameterNameDefaultMemoryUsed
升级会员 