ASE 15.7 Tuning for BW

[ganimede_dignan]

Hi,

I know it. ASE 15.7 isn't better way for BW... I hope tu upgrade it next year. Now I'm living with poor performance. Really poor performance. We have also an ERP box with the same HW configuration that runs so fat and it is used by more then concurrent users.

Have you got any suggest on ASE tuning side? It's a Windows box with 128 GB ram

############################################################################## # #Configuration File for the Sybase SQL Server # #Please read the System Administration Guide (SAG) #before changing any of the values in this file. # ############################################################################## [Configuration Options] [General Information] config file version = DEFAULT [Backup/Recovery] recovery interval in minutes = 60 print recovery information = 1 tape retention in days = DEFAULT max concurrently recovered db = DEFAULT number of checkpoint tasks = DEFAULT enable backupserver HA = DEFAULT enforce dump configuration = DEFAULT enable dump history = 1 dump history filename = DEFAULT recovery prefetch size = DEFAULT optimize dump for faster load = DEFAULT [Cache Manager] number of oam trips = 1 number of index trips = 1 memory alignment boundary = DEFAULT global async prefetch limit = DEFAULT global cache partition number = 8 extended cache size = DEFAULT [Named Cache:default data cache] cache size = 82920M cache status = default data cache cache replacement policy = strict LRU replacement local cache partition number = 16 [16K I/O Buffer Pool] pool size = 51440.0000m wash size = 2097152 K local async prefetch limit = DEFAULT [128K I/O Buffer Pool] pool size = 10000.0000m wash size = DEFAULT local async prefetch limit = DEFAULT [Named Cache:log_cache] cache size = 4192M cache status = log only cache replacement policy = strict LRU replacement local cache partition number = 1 [32K I/O Buffer Pool] pool size = 4194304.0000k wash size = 61440 K local async prefetch limit = DEFAULT [Named Cache:queue_cache] cache size = 4194304.0K cache status = mixed cache cache replacement policy = relaxed LRU replacement local cache partition number = 4 [Named Cache:systables_cache] cache size = 4194304.0K cache status = mixed cache cache replacement policy = relaxed LRU replacement local cache partition number = 4 [Meta-Data Caches] number of open databases = DEFAULT number of open objects = 60000 engine local cache percent = DEFAULT open object spinlock ratio = DEFAULT number of open indexes = 60000 open index hash spinlock ratio = DEFAULT open index spinlock ratio = DEFAULT partition groups = DEFAULT partition spinlock ratio = DEFAULT number of open partitions = 100000 [Disk I/O] disk i/o structures = 4096 number of large i/o buffers = 32 page utilization percent = DEFAULT number of devices = 200 disable disk mirroring = DEFAULT allow sql server async i/o = DEFAULT number of disk tasks = DEFAULT enable hp posix async i/o = DEFAULT solaris async i/o mode = DEFAULT [Languages] disable character set conversions = DEFAULT [Unicode] enable unicode normalization = 0 enable surrogate processing = 0 enable unicode conversions = DEFAULT size of unilib cache = 524288 dynamic SQL plan pinning = DEFAULT [Network Communication] default network packet size = 16384 max network packet size = 16384 remote server pre-read packets = DEFAULT number of remote connections = DEFAULT number of remote logins = DEFAULT number of remote sites = DEFAULT max number network listeners = DEFAULT tcp no delay = DEFAULT send doneinproc tokens = DEFAULT allow sendmsg = DEFAULT syb_sendmsg port number = DEFAULT allow remote access = DEFAULT number of network tasks = DEFAULT network polling mode = DEFAULT number of early send rows = DEFAULT early row send increment = DEFAULT [O/S Resources] max async i/os per engine = DEFAULT max async i/os per server = DEFAULT [Query Tuning] optimization goal = sap_oltp allow backward scans = DEFAULT abstract plan load = DEFAULT abstract plan dump = DEFAULT abstract plan replace = DEFAULT abstract plan cache = DEFAULT abstract plan sharing = DEFAULT sampling percent = DEFAULT number of histogram steps = 100 enable sort-merge join and JTC = DEFAULT number of worker processes = 100 memory per worker process = DEFAULT max parallel degree = 40 max utility parallel degree = 10 max scan parallel degree = 40 max repartition degree = DEFAULT max resource granularity = DEFAULT enable metrics capture = DEFAULT optimization timeout limit = DEFAULT metrics lio max = DEFAULT metrics pio max = DEFAULT metrics elap max = DEFAULT metrics exec max = DEFAULT sproc optimize timeout limit = DEFAULT min pages for parallel scan = 60000 prod-consumer overlap factor = DEFAULT enable literal autoparam = DEFAULT max query parallel degree = DEFAULT cost of a logical io = DEFAULT cost of a physical io = DEFAULT cost of a cpu unit = DEFAULT auto query tuning = DEFAULT enable query tuning mem limit = DEFAULT query tuning plan executions = DEFAULT enable query tuning time limit = DEFAULT max buffers per lava operator = DEFAULT enable merge join = DEFAULT mnc_full_index_filter = DEFAULT capture missing statistics = DEFAULT enable compatibility mode = DEFAULT optimize temp table resolution = DEFAULT procedure deferred compilation = DEFAULT optimizer level = ase_current search engine timeout factor = DEFAULT deferred name resolution = DEFAULT update statistics hashing = partial utility statistics hashing = DEFAULT enable sticky statistics = 0 builtin date strings = DEFAULT enable deferred parallel = 1 enable streamlined parallel = DEFAULT enable plan sharing = DEFAULT [Physical Resources] [Physical Memory] max memory = 62914560 enable HugePages = DEFAULT enable ISM = DEFAULT additional network memory = 10485760 shared memory starting address = DEFAULT allocate max shared memory = 1 dynamic allocation on demand = DEFAULT lock shared memory = DEFAULT heap memory per user = 524288 engine memory log size = DEFAULT compression memory size = DEFAULT kernel resource memory = 32768 [Processors] max online engines = 24 max online Q engines = DEFAULT number of engines at startup = DEFAULT number of Q engines at startup = DEFAULT statement cache size = 524288 [SQL Server Administration] procedure cache size = 5242880 default database size = DEFAULT identity burning set factor = DEFAULT allow nested triggers = DEFAULT allow updates to system tables = DEFAULT default fill factor percent = DEFAULT default exp_row_size percent = 3 number of mailboxes = DEFAULT number of messages = DEFAULT number of alarms = 1000 number of pre-allocated extents = 32 event buffers per engine = DEFAULT cpu accounting flush interval = 5000000 i/o accounting flush interval = 5000000 sql server clock tick length = DEFAULT runnable process search count = DEFAULT i/o polling process count = DEFAULT time slice = DEFAULT cpu grace time = 1000 number of sort buffers = 2500 size of auto identity column = DEFAULT identity grab size = DEFAULT housekeeper free write percent = 10 enable housekeeper GC = 5 sysstatistics flush interval = 5 allow resource limits = DEFAULT number of aux scan descriptors = 1024 SQL Perfmon Integration = DEFAULT license information = DEFAULT text prefetch size = DEFAULT enable HA = DEFAULT i/o batch size = DEFAULT enable semantic partitioning = 1 enable compression = 1 compression info pool size = 1048576 capture compression statistics = DEFAULT enable xml = 1 enable webservices = DEFAULT enable job scheduler = 1 job scheduler tasks = 25 job scheduler interval = DEFAULT js job output width = 1024 percent database for history = DEFAULT percent history free = 20 percent database for output = 20 percent output free = 33 maximum job output = 16777216 max js restart attempts = DEFAULT enable js restart logging = DEFAULT disable jsagent core dump = DEFAULT js heartbeat interval = DEFAULT enable sql debugger = DEFAULT identity reservation size = DEFAULT kernel mode = DEFAULT max transfer history = DEFAULT disable varbinary truncation = 1 enable console logging = DEFAULT enable async database init = 1 HADR mode = DEFAULT aggressive task stealing = DEFAULT wait on uncommitted insert = 1 prevent automatic upgrade = DEFAULT [User Environment] number of user connections = 500 stack size = 151552 stack guard size = 16384 permission cache entries = 128 user log cache size = 32768 user log cache spinlock ratio = 5 session tempdb log cache size = 32768 max nesting level = 32 number of backup connections = DEFAULT enable pci = DEFAULT max pci slots = DEFAULT pci memory size = DEFAULT transfer utility memory size = DEFAULT column default cache size = DEFAULT replication agent memory size = DEFAULT max native threads per engine = DEFAULT messaging memory = DEFAULT enable real time messaging = DEFAULT histogram tuning factor = DEFAULT rtm thread idle wait period = DEFAULT [Lock Manager] number of locks = 20000000 deadlock checking period = DEFAULT lock spinlock ratio = 40 lock address spinlock ratio = DEFAULT lock table spinlock ratio = DEFAULT lock hashtable size = 1048576 cluster redundancy level = DEFAULT lock scheme = datarows lock wait period = 1800 read committed with lock = DEFAULT print deadlock information = 1 deadlock retries = DEFAULT page lock promotion HWM = DEFAULT page lock promotion LWM = DEFAULT page lock promotion PCT = DEFAULT row lock promotion HWM = 2147483647 row lock promotion LWM = 2147483647 row lock promotion PCT = DEFAULT [Security Related] systemwide password expiration = DEFAULT audit queue size = DEFAULT curread change w/ open cursors = DEFAULT allow procedure grouping = DEFAULT select on syscomments.text = DEFAULT auditing = DEFAULT current audit table = DEFAULT suspend audit when device full = DEFAULT enable row level access = DEFAULT check password for digit = DEFAULT minimum password length = DEFAULT maximum failed logins = DEFAULT enable ssl = DEFAULT FIPS login password encryption = 1 enable predicated privileges = DEFAULT enable granular permissions = DEFAULT restricted decrypt permission = DEFAULT net password encryption reqd = 1 automatic master key access = DEFAULT unified login required = DEFAULT use security services = DEFAULT msg confidentiality reqd = DEFAULT msg integrity reqd = DEFAULT enable pam user auth = DEFAULT enable ldap user auth = DEFAULT enable encrypted columns = DEFAULT secure default login = DEFAULT enable logins during recovery = DEFAULT [Extended Stored Procedure] esp unload dll = DEFAULT esp execution priority = DEFAULT esp execution stacksize = DEFAULT xp_cmdshell context = DEFAULT start mail session = DEFAULT start xp server during boot = DEFAULT [Error Log] event logging = DEFAULT log audit logon success = DEFAULT log audit logon failure = DEFAULT event log computer name = DEFAULT enable rapidlog = DEFAULT rapidlog buffer size = DEFAULT rapidlog max files = DEFAULT [Rep Agent Thread Administration] enable rep agent threads = DEFAULT [Component Integration Services] enable cis = DEFAULT cis connect timeout = 30 cis bulk insert batch size = DEFAULT max cis remote connections = DEFAULT cis idle connection timeout = DEFAULT cis packet size = DEFAULT cis cursor rows = DEFAULT enable snmp = DEFAULT enable file access = DEFAULT cis bulk insert array size = DEFAULT enable full-text search = DEFAULT cis rpc handling = DEFAULT [Java Services] enable java = DEFAULT size of process object heap = DEFAULT size of shared class heap = DEFAULT size of global fixed heap = DEFAULT number of java sockets = DEFAULT [DTM Administration] enable DTM = DEFAULT enable xact coordination = DEFAULT xact coordination interval = DEFAULT number of dtx participants = DEFAULT strict dtm enforcement = DEFAULT txn to pss ratio = DEFAULT dtm lock timeout period = DEFAULT dtm detach timeout period = DEFAULT [Diagnostics] dump on conditions = DEFAULT maximum dump conditions = DEFAULT number of dump threads = DEFAULT memory dump compression level = DEFAULT number of ccbs = DEFAULT caps per ccb = DEFAULT average cap size = DEFAULT [Monitoring] enable monitoring = 1 sql text pipe active = DEFAULT sql text pipe max messages = DEFAULT plan text pipe active = DEFAULT plan text pipe max messages = DEFAULT statement pipe active = DEFAULT statement pipe max messages = DEFAULT errorlog pipe active = 1 errorlog pipe max messages = 500 deadlock pipe active = 1 deadlock pipe max messages = 500 lock timeout pipe max messages = 500 lock timeout pipe active = 1 wait event timing = 1 process wait events = 1 object lockwait timing = 1 SQL batch capture = 1 statement statistics active = 1 per object statistics active = 1 max SQL text monitored = 2048 performance monitoring option = DEFAULT enable stmt cache monitoring = 1 enable spinlock monitoring = 1 execution time monitoring = 1 [Shared Disk Cluster] CIPC regular message pool size = DEFAULT CIPC large message pool size = DEFAULT DMA object pool size = DEFAULT workload manager cache size = DEFAULT idle migration timeout = DEFAULT session migration timeout = DEFAULT automatic cluster takeover = DEFAULT quorum heartbeat interval = DEFAULT quorum heartbeat retries = DEFAULT cluster heartbeat interval = DEFAULT cluster heartbeat retries = DEFAULT cluster vote timeout = DEFAULT enable i/o fencing = DEFAULT enable lock remastering = DEFAULT physical lock cushion = DEFAULT [Dump Configuration:LOGBCK] stripe directory = DBTSMNBP external api name = DEFAULT number of stripes = DEFAULT number of retries = DEFAULT block size = DEFAULT compression level = DEFAULT retain days = DEFAULT init = DEFAULT verify = DEFAULT notify = DEFAULT remote backup server name = DEFAULT [Dump Configuration:NBPDB] stripe directory = P:\DATABCK external api name = DEFAULT number of stripes = DEFAULT number of retries = DEFAULT block size = DEFAULT compression level = 101 retain days = DEFAULT init = DEFAULT verify = header notify = DEFAULT remote backup server name = DEFAULT [Dump Configuration:NBPLOG] stripe directory = P:\LOGBCK external api name = DEFAULT number of stripes = DEFAULT number of retries = DEFAULT block size = DEFAULT compression level = 101 retain days = DEFAULT init = DEFAULT verify = header notify = DEFAULT remote backup server name = DEFAULT [Dump Configuration:DBTSMNBP] stripe directory = DEFAULT external api name = syb_tsm number of stripes = DEFAULT number of retries = 2 block size = DEFAULT compression level = 101 retain days = DEFAULT init = DEFAULT verify = header notify = DEFAULT remote backup server name = DEFAULT [Dump Configuration:TLTSMNBP] stripe directory = DEFAULT external api name = syb_tsm number of stripes = DEFAULT number of retries = 2 block size = DEFAULT compression level = 101 retain days = DEFAULT init = DEFAULT verify = header notify = DEFAULT remote backup server name = DEFAULT [Application Functionality] enable functionality group = 1 select for update = DEFAULT streamlined dynamic SQL = DEFAULT enable inline default sharing = DEFAULT enable permissive unicode = DEFAULT quoted identifier enhancements = DEFAULT suppress js max task message = DEFAULT enable concurrent dump tran = DEFAULT enable large pool for load = DEFAULT enable utility lvl 0 scan wait = DEFAULT expand numeric truncated scale = 0 bind backupserver address = 0 enable bulk inserts = DEFAULT [Bucket Pool:Encrypted Columns Frag] enabled = 1 bucket size = 32 seed = 0 bucket size = 64 seed = 0 bucket size = 96 seed = 0 bucket size = 128 seed = 0 bucket size = 160 seed = 0 bucket size = 192 seed = 0 bucket size = 224 seed = 0 bucket size = 256 seed = 0 bucket size = 288 seed = 0 bucket size = 320 seed = 0 bucket size = 352 seed = 0 instances = 1 instance seed = 0 autotune = 0 overhead = 0 [Bucket Pool:Network Buffers] enabled = 1 bucket size = 32 seed = 0 bucket size = 64 seed = 0 bucket size = 96 seed = 0 bucket size = 128 seed = 0 bucket size = 160 seed = 0 bucket size = 192 seed = 0 bucket size = 224 seed = 0 bucket size = 256 seed = 0 bucket size = 288 seed = 0 bucket size = 320 seed = 0 bucket size = 352 seed = 0 instances = 1 instance seed = 0 autotune = 0 overhead = 0 [Thread Pool:syb_default_pool] number of threads = 16 idle timeout = 250 [Thread Pool:syb_blocking_pool] number of threads = 4

[ganimede_dignan]

Hi,

I've re-check the note and best practice and I attach che configuration file as nbp.txt

Thank you.

[madhvi_pai]

Hi Ganimede,

Cache config & proc cache etc looks fine... In addition to above suggestions of cube compression & stats update, please let us set the server-wide optimization goal on this system to 'sap_oltp' and try a custom optimization goal specifically for BW queries, like so in isql:

sp_configure 'optimization goal', 0, 'sap_oltp'
go

Please do the following next in an attempt to set up optimal BW config. In isql, issue:

set plan optgoal sap_oltp
go
set merge_join on
go
set hash_join on
go
set multi_table_store_ind on
go
set advanced_aggregation on
go
set bushy_space_search on
go
set join_bloom_filter on
go
set store_index 0
go
sp_optgoal 'bwgoal', 'save'
go

Then adding above goal as a BW hints in RSADMIN per SAP Note 1718575 - SYB: Optimization goals and criteria for BW queries .

Hints to be added:

(use optgoal bwgoal)
(use parallel_query on)

Please let me know if above helps. You may check query sql in RSRT to see if the hints are appended to the generated BW queries.

Thanks,

Madhvi Pai

SAP Product Support

[victoria_normand]

Dear Ganimede,
I'm afraid that the output is unreadable. For a next time please attach it as a file.
Anyway, an output of configuration file only can be useful to understand your sizing configuration but it won't the unique information required for a BW tuning.

Check the guide from SAP Note 1680803 - SYB: SAP Adaptive Server Enterprise - Best Practice for SAP Business Suite and SAP BW , ie Chapter 6.2.5 regarding configuration of the default data cache, your current size is 82920Mb, the suggestion is to have 80% of that size configured to the 128K I/O pool and the remaining to the 16K I/O pool. Start with that. Chapter 6.2 ASE Sizing.

You might need to identify the most consuming queries, if any (that can be seen thru DBACockpit). Ensure that your infocubes are being compressed. Ensure statistics are up-to-date. Recommended to read Chapter 7.6 of that Best Practices Guide.

Consider to upgrade this year 😉

HTH,
Victoria.