Embarcadero DB Optimizer
 
Search this site

DB: Sybase



Setting up Sybase for use with the DBOptimizer SQL Profiler feature

     Profiler
       uses:
             master.dbo.sysprocesses,
             master.dbo.monWaitEventInfo,
             master.dbo.monProcess            
             master.dbo.monProcessSQLText

1.       Enable the following System Configuration settings:

·       Enabling Monitoring
·       Wait Event Timing
·       Max SQL Text Monitored
·        SQL Batch Capture  

Ex:      sp_configure "enable monitoring", 1
           sp_configure "wait event timing", 1
           sp_configure "SQL batch capture", 1
           sp_configure "max SQL text monitored", 4096* *

This is the maximum length for SQL text stored by Sybase, but we suggest 4096 as a good starting point.

2.       Grant the user mon_role.
 
3.       Install the Sybase MDA tables (Sybase official documentation has directions - these should only be installed by a DBA).
·         If the monProcess table is missing, the user will not be able to see currently connected sessions.
·         If the sysprocesses table is missing, the user will not be able to see information about Adaptive Server processes.
·         If the monWaitEventInfo table is missing, the user will not be able to see information about wait events.
·         If the monProcessSQLText table is missing, the user will not be able to see currently executing SQL statements.

Ex:  isql -U sa -P yourpassword -S YOURSERVER -i $SYBASE/$SYBASE_ASE/scripts/installmontables

NOTE: The above command has to run on the server, not the client.

More info on MDA table install
     
       Tuner
                          Ex:   sp_configure "allow resource limits", 1

This will enable resource limits (requires server restart). When executing the statements, DBO will also send “set showplan on” command. This will cause Sybase to display the explain plan and also the I/O costs when running statements (if the resource limits are disabled, no costs will be displayed).
                          Hints used

Engines and CPUs
      The number of "engines" is displayed as a red horizontal line on the load chart. The number of engines is the maximum number of concurrent users. If the number of concurrent, ie active, users in the load chart is higher than the number of engines then there is a bottleneck.
THe number of engines is limited by the number of CPUs on the machine and the what the license from Sybase allows.The usual recommendation is number of engines should never exceed the number of physical CPUs as performance will be worse. On a Sybase server with many CPUs “8 or more”, the recommended formula is Engines = CPUs – 1

Additional Info

       Sysprocesses
       Sybase Waits
     MDA Tables
Subpages (1): Sybase Hints