How to tune a Query or Stored Procedure in Sybase ?

Getting information about the tables and indexes
sp_help
sp_helpdb
sp_tables

Diagnosing the Problem:

Execute the query again, with some of the below options to get more information:

See the query plan Sybase ASE is using, look for table scans in the results
set showplan on
(optional) set noexec on (this negates statistics io)

Gather some statistics looks for I/O, which usually takes a lot of time.
set statistics io on

Gather time statistics so you can see where the time is spent
set statistics time on

If you think the problem is with an index, here is how to see they are chosen
dbcc traceon(302) and dbcc traceon(310)

Best way to determine size of a table
sp_spaceused

This tool will take samples studying all areas of database activity
sp_sysmon

Sybase ASE has an advanced tool for studying statistics
optdiag

You can get additional network information using sp_monitor, here are some examples
sp_monitor
@@pack_sent – Number of packets sent by Adaptive Server
@@pack_received – Number of packets received
@@packet_errors – Number of errors

Solving the Problem:

Often the solution is to create an index
create index [name] on [table] ([column])

Clean-up tables that have been updated frequently
rebuild reorg

The optimizer needs up-to-date statistics
update statistics [table]

After updating statistics, consider re-compiling the stored procedures
sp_recompile [table]

Related posts:

Comments

comments

Powered by Facebook Comments

%d bloggers like this: