Thursday, May 26, 2005


Optimizing Sybase Queries and Stored Procedures

This is meant for technical specialists, not DBAs, although some of the information obtained can be passed onto DBAs for server-level tuning.

This is also not a step-by-step or a "how to," is it instead a collection of tools that can be used to find and resolve common performance issues.


How to log your results

isql -P password -e -i input_file -o outputfile
isql -P password < input_file > outputfile

Getting information about the tables and indexes

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

This tool will take samples studying all areas of database activity

Sybase ASE has an advanced tool for studying statistics

You can get additional network information using sp_monitor, here are some examples
@@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]

There are also more advanced tuning techniques to turn options on and off, but consult an advanced DBA for those.

Here is a useful article on the same topic by Todd Boss:
Here's tips on tuning in general:
And one more:
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?