Sybase

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

Example

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.

Example

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

Example

ssp_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