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.

Basics:

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
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]

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

Comments:
Here is a useful article on the same topic by Todd Boss:

http://www.bossconsulting.com/sybase_dba/sublevels/index.troubleshooting
 
Here's tips on tuning in general:

http://www.edbarlow.com/document/top_tune.htm
 
And one more:
http://www.edbarlow.com/document/optimize.htm
 
Post a Comment

<< Home

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