Sybase数据库性能优化的具体过程

Sybase数据库性能优化的具体过程用一个实例讲解了Sybase数据库性能优化的具体过程，具体内容请参考下文：共享锁sp_getapplock 锁定应用程序资源sp_releaseapplock 为应用程序资源解锁SET LOCK_TIMEOUT 1800 锁超时期限设置sp_configure 'deadlock checking period',5000 设置锁检测周期sp_configure 'lock wait period',5000 设置锁的等待时间sp_setrowlockpromote 设置基本个表的最大行锁升级数(锁数)sp_setrowlockpromote 'TABLE',TREECODE,500,500,100sp_setrowlockpromote 'TABLE',LCD05,500,500,100[Lock Manager]number of locks = 50000 #锁数deadlock checking period = DEFAULTfreelock transfer block size = DEFAULTmax engine freelocks = DEFAULTlock spinlock ratio = DEFAULTlock hashtable size = DEFAULTlock scheme = DEFAULTlock wait period = DEFAULTread committed with lock = DEFAULT当很多事务同时访问同一个数据库时，会加剧锁资源争夺，严重时事务之间会发生死锁。

可用sp_object_stats查明死锁位置。

该过程报告资源争夺最激烈的10张表、一个数据库中资源争夺的表和单个表的争夺情况。

语法为sp_object_stats interval [, top_n [, dbname [, objname [, rpt_option ]]]]，查看锁争夺情况只需设置interval为“hh:mm:ss”。

如果显示每种锁的争夺程度超过15%，应该改变加锁方式，比如表的全页锁改成数据页锁，数据页锁改成数据行锁等。

Parameter Name Default Memory Used Config V alue Run V alue-------------- ------- ----------- ------------ ---------allow remote access 1 0 1 1print recovery information 0 0 0 0recovery interval in minutes 5 0 5 5tape retention in days 0 0 0 0Parameter Name Default Memory Used Config V alue Run V alue-------------- ------- ----------- ------------ ---------global async prefetch limit 10 0 10 10global cache partition number 1 0 1 1memory alignment boundary 2048 0 2048 2048number of index trips 0 0 0 0number of oam trips 0 0 0 0procedure cache percent 20 22426 20 20total data cache size 0 89698 0 89698total memory 47104 196608 98304 98304Parameter Name Default Memory Used Config V alue Run V alue -------------- ------- ----------- ------------ ---------cis bulk insert batch size 0 0 0 0cis connect timeout 0 0 0 0cis cursor rows 50 0 50 50cis packet size 512 0 512 512cis rpc handling 0 0 0 0enable cis 1 0 1 1max cis remote connections 0 0 0 0max cis remote servers 25 19 25 25Parameter Name Default Memory Used Config V alue Run V alue -------------- ------- ----------- ------------ ---------dtm detach timeout period 0 0 0 0dtm lock timeout period 300 0 300 300enable xact coordination 1 0 1 1number of dtx participants 500 149 500 500strict dtm enforcement 0 0 0 0txn to pss ratio 16 3692 16 16xact coordination interval 60 0 60 60Parameter Name Default Memory Used Config V alue Run V alue -------------- ------- ----------- ------------ ---------average cap size 200 0 200 200caps per ccb 50 0 50 50dump on conditions 0 0 0 0maximum dump conditions 10 0 10 10number of ccbs 0 0 0 0Parameter Name Default Memory Used Config V alue Run V alue-------------- ------- ----------- ------------ ---------allow sql server async i/o 1 0 1 1disable disk mirroring 0 0 0 0disk i/o structures 256 31 256 256number of devices 10 #5 10 10page utilization percent 95 0 95 95Parameter Name Default Memory Used Config V alue Run V alue-------------- ------- ----------- ------------ ---------event log computer name LocalSystem 0 LocalSystem LocalSystem event logging 1 0 1 1log audit logon failure 0 0 0 0log audit logon success 0 0 0 0Parameter Name Default Memory Used Config V alue Run V alue-------------- ------- ----------- ------------ ---------esp execution priority 8 0 8 8esp execution stacksize 77824 0 77824 77824esp unload dll 0 0 0 0start mail session 0 0 0 0xp_cmdshell context 1 0 1 1Parameter Name Default Memory Used Config V alue Run V alue -------------- ------- ----------- ------------ ---------configuration file 0 0 0 /sybase/hgdParameter Name Default Memory Used Config V alue Run V alue -------------- ------- ----------- ------------ ---------enable java 0 0 0 0size of global fixed heap 300 0 300 300size of process object heap 300 0 300 300size of shared class heap 3072 0 3072 3072Parameter Name Default Memory Used Config V alue Run V alue -------------- ------- ----------- ------------ ---------default character set id 1 0 1 1default language id 0 0 0 0default sortorder id 50 0 50 50disable character set conversi 0 0 0 0enable unicode conversions 0 0 1 1number of languages in cache 3 4 3 3size of unilib cache 0 140 0 0Parameter Name Default Memory Used Config V alue Run V alue -------------- ------- ----------- ------------ ---------deadlock checking period 500 0 500 500freelock transfer block size 30 0 30 30lock address spinlock ratio 100 0 100 100lock hashtable size 2048 48 2048 2048lock scheme allpages 0 allpages allpageslock spinlock ratio 85 0 85 85lock table spinlock ratio 20 0 20 20lock wait period 2147483647 0 2147483647 2147483647max engine freelocks 10 0 10 10number of locks 5000 2344 10000 10000print deadlock information 0 0 1 1read committed with lock 0 0 0 0Parameter Name Default Memory Used Config V alue Run V alue -------------- ------- ----------- ------------ ---------additional network memory 0 0 0 0allow resource limits 0 0 0 0audit queue size 100 42 100 100average cap size 200 0 200 200caps per ccb 50 0 50 50deadlock pipe max messages 0 0 0 0default network packet size 512 #505 512 512 disk i/o structures 256 31 256 256enable rep agent threads 0 0 0 0errorlog pipe max messages 0 0 0 0event buffers per engine 100 #11 100 100 executable codesize + overhead 0 20261 0 20261 lock hashtable size 2048 48 2048 2048lock spinlock ratio 85 0 85 85max cis remote servers 25 19 25 25max number network listeners 5 868 5 5max online engines 1 216 1 1max roles enabled per user 20 #22 20 20 memory per worker process 1024 0 1024 1024 number of alarms 40 3 40 40number of aux scan descriptors 200 #258 200 200 number of ccbs 0 0 0 0number of devices 10 #5 10 10number of languages in cache 3 4 3 3number of large i/o buffers 6 97 6 6number of locks 5000 2344 10000 10000 number of mailboxes 30 1 30 30number of messages 64 3 64 64number of open databases 12 1239 12 12 number of open indexes 500 512 500 500 number of open objects 500 561 500 500 number of remote connections 20 86 50 50 number of remote logins 20 23 20 20 number of remote sites 10 1729 10 10 number of user connections 25 43141 250 250 number of worker processes 0 0 0 0 partition groups 1024 904 1024 1024 permission cache entries 15 #227 15 15plan text pipe max messages 0 0 0 0 procedure cache percent 20 22426 20 20 process wait events 0 0 0 0remote server pre-read packets 3 #83 3 3size of global fixed heap 300 0 300 300size of process object heap 300 0 300 300 size of shared class heap 3072 0 3072 3072 size of unilib cache 0 140 0 0sql text pipe max messages 0 0 0 0stack guard size 4096 #1108 4096 4096stack size 86016 #23269 86016 86016statement pipe max messages 0 0 0 0total data cache size 0 89698 0 89698total memory 47104 196608 98304 98304txn to pss ratio 16 3692 16 16wait event timing 0 0 0 0Parameter Name Default Memory Used Config V alue Run V alue -------------- ------- ----------- ------------ ---------number of open databases 12 1239 12 12number of open indexes 500 512 500 500number of open objects 500 561 500 500open index hash spinlock ratio 100 0 100 100open index spinlock ratio 100 0 100 100open object spinlock ratio 100 0 100 100Parameter Name Default Memory Used Config V alue Run V alue -------------- ------- ----------- ------------ ---------Q diagnostics active 0 0 0 0SQL batch capture 0 0 0 0deadlock pipe active 0 0 0 0deadlock pipe max messages 0 0 0 0errorlog pipe active 0 0 0 0errorlog pipe max messages 0 0 0 0object lockwait timing 0 0 0 0per object statistics active 0 0 0 0plan text pipe active 0 0 0 0plan text pipe max messages 0 0 0 0process wait events 0 0 0 0sql text pipe active 0 0 0 0sql text pipe max messages 0 0 0 0statement pipe active 0 0 0 0statement pipe max messages 0 0 0 0statement statistics active 0 0 0 0wait event timing 0 0 0 0Parameter Name Default Memory Used Config V alue Run V alue -------------- ------- ----------- ------------ ---------additional network memory 0 0 0 0allow remote access 1 0 1 1allow sendmsg 0 0 0 0default network packet size 512 #505 512 512max network packet size 512 0 512 512max number network listeners 5 868 5 5number of remote connections 20 86 50 50number of remote logins 20 23 20 20number of remote sites 10 1729 10 10remote server pre-read packets 3 #83 3 3syb_sendmsg port number 0 0 0 0tcp no delay 0 0 0 0Parameter Name Default Memory Used Config V alue Run V alue -------------- ------- ----------- ------------ ---------max async i/os per engine 2147483647 0 2147483647 2147483647 max async i/os per server 2147483647 0 2147483647 2147483647 o/s file descriptors 0 0 0 1024tcp no delay 0 0 0 0Parameter Name Default Memory Used Config V alue Run V alue -------------- ------- ----------- ------------ ---------max parallel degree 1 0 1 1max scan parallel degree 1 0 1 1memory per worker process 1024 0 1024 1024number of worker processes 0 0 0 0Parameter Name Default Memory Used Config V alue Run V alue -------------- ------- ----------- ------------ ---------additional network memory 0 0 0 0lock shared memory 0 0 0 0max SQL text monitored 0 7 0 0shared memory starting address 0 0 0 0total memory 47104 196608 98304 98304Parameter Name Default Memory Used Config V alue Run V alue -------------- ------- ----------- ------------ ---------max online engines 1 216 1 1min online engines 1 0 1 1Parameter Name Default Memory Used Config V alue Run V alue -------------- ------- ----------- ------------ ---------enable rep agent threads 0 0 0 0Parameter Name Default Memory Used Config V alue Run V alue -------------- ------- ----------- ------------ ---------SQL Perfmon Integration 1 0 1 1abstract plan cache 0 0 0 0abstract plan dump 0 0 0 0abstract plan load 0 0 0 0abstract plan replace 0 0 0 0allow backward scans 1 0 1 1allow nested triggers 1 0 1 1allow resource limits 0 0 0 0allow updates to system tables 0 0 1 1audit queue size 100 42 100 100cpu accounting flush interval 200 0 200 200cpu grace time 500 0 500 500deadlock retries 5 0 5 5default database size 2 0 2 2default exp_row_size percent 5 0 5 5default fill factor percent 0 0 0 0enable DTM 0 0 0 0enable HA 0 0 0 0enable housekeeper GC 1 0 1 1enable sort-merge join and JTC 0 0 0 0event buffers per engine 100 #11 100 100 housekeeper free write percent 1 0 1 1i/o accounting flush interval 1000 0 1000 1000i/o polling process count 10 0 10 10identity burning set factor 5000 0 5000 5000 identity grab size 1 0 1 1license information 25 0 25 25number of alarms 40 3 40 40number of aux scan descriptors 200 #258 200 200number of large i/o buffers 6 97 6 6number of mailboxes 30 1 30 30number of messages 64 3 64 64number of open databases 12 1239 12 12number of open indexes 500 512 500 500number of open objects 500 561 500 500number of pre-allocated extent 2 0 2 2number of sort buffers 500 0 500 500page lock promotion HWM 200 0 200 200page lock promotion LWM 200 0 200 200page lock promotion PCT 100 0 100 100partition groups 1024 904 1024 1024partition spinlock ratio 10 0 10 10print deadlock information 0 0 1 1row lock promotion HWM 200 0 200 200row lock promotion LWM 200 0 200 200row lock promotion PCT 100 0 100 100runnable process search count 2000 0 2000 2000 size of auto identity column 10 0 10 10sql server clock tick length 100000 0 100000 100000 text prefetch size 16 0 16 16time slice 100 0 100 100upgrade version 1100 0 12000 12000Parameter Name Default Memory Used Config V alue Run V alue -------------- ------- ----------- ------------ ---------allow procedure grouping 1 0 1 1auditing 0 0 0 0check password for digit 0 0 0 0curread change w/ open cursors 1 0 1 1current audit table 1 0 1 1max roles enabled per user 20 #22 20 20maximum failed logins 0 0 0 0minimum password length 6 0 6 6msg confidentiality reqd 0 0 0 0msg integrity reqd 0 0 0 0secure default login guest 0 guest guestselect on syscomments.text 1 0 1 1suspend audit when device full 1 0 1 1unified login required 0 0 0 0use security services 0 0 0 0Parameter Name Default Memory Used Config V alue Run V alue -------------- ------- ----------- ------------ ---------default network packet size 512 #505 512 512number of pre-allocated extent 2 0 2 2 number of user connections 25 43141 250 250 permission cache entries 15 #227 15 15 stack guard size 4096 #1108 4096 4096 stack size 86016 #23269 86016 86016 systemwide password expiration 0 0 0 0user log cache size 2048 0 2048 2048user log cache spinlock ratio 20 0 20 20-。