Sybase dataserver parameter tuning

有些可能是15.7的參數, 先留著紀錄一下....

重要參數:
--Tune max memory  (單位為4k), 以下總和為8GB
sp_configure 'max memory',4194304
go
sp_configure 'lock shared memory', 1
go
sp_configure 'procedure cache size',65536
go
sp_configure 'number of user connections', 512
go
--default 10 worker processes
sp_configure 'number of worker processes', 20
go
sp_configure 'max parallel degree', 3
go
sp_configure 'max scan parallel degree', 3
go
sp_configure 'number of locks', 100000
go
sp_configure 'number of open objects', 50000
go
sp_configure 'number of open databases', 32
go
--limitation is relevent to No# of OS file descriptors
sp_configure 'number of devices', 32
go
--default : page size (sql, data資料都在這邊, 調越大, 就避免過多的disk io, by user每一個人都分配到這塊空間)
sp_configure 'user log cache size', 4096
go
--If for 4 CPU , set to 3
sp_configure 'number of engines at startup',3
go
sp_configure 'max online engines',3
go
--設定成跟engines數量相同的partition 數量, 來減少spin lock的發生率
sp_cacheconfig 'default data cache', '1024M', "cache_partition=3"
go
--number of large i/o buffers default = 6
sp_configure 'number of large i/o buffers',64
go
--set to the default except when you are creating indexes in parallel
sp_configure 'number of sort buffers',500
go
--TDS(Tabular Data Stream) Packet Size (預設512byte, 可調整為2k, 或與page size一樣)
sp_configure 'default network packet size',4096
go
sp_configure 'max network packet size',16384
go
sp_configure 'additional network memory',10485760
go
--減少query plan產生的時間, 類似Oracle : cursor_sharing : force的作法
sp_configure 'enable literal autoparam', 1
go
--增加SQL cache
sp_configure 'statement cache size', 5000
go
--reduce tempdb I/O, direct i/o off,dsync off
exec sp_deviceattr 'tempdb', 'dsync', 'false'
go
-- Store Buffers from the session’s tempdb data cache pool as a work area
sp_configure 'max buffers per lava operator',20480
go
sp_configure 'session tempdb log cache size',32768
go