Sybase ASE - Tracing queries / processes

Question

If there are any Sybase ASE experts out there, I was wondering if there is a way to get Sybase to trace all the queries that it receives.

I currently have a program running that usually begins displaying some strange behaviour after 7 hours from startup. I would like to find out what Sybase is doing at this time, so that I may fix the problem.

I am using Sybase ASE 15.5. And my monitoring server will not start up, for some weird reason.

Solution

Yes, there are several ways to trace all queries that Sybase receives.

However, given the second part of your question, as I understand it, you need to (1) understand what your query is doing (2) monitor your active spid in the context of other active spids and ... if that does not suffice, then (3) monitor the server; not trace, and all queries. Therefore I will go into that and defer responding re the tracing elements available in Sybase.

Your connection to ASE is a Server Process Id, or spid.

1. SET SHOWPLAN ON and SET NOEXEC ON, and execute your query. This will give you a very good insight into what your SQL is actually doing, under the covers. This is an essential requirement, something every developer should be familiar with, and demanded before testing. Whenever it feels like your query is slow, always check the I/O being performed: SET STATISTICS IO ON.

2. sp_who; sp_lock; and watch your spid ticking over, hung, waiting for locks, blocked, which other spids are holding it up, etc. This is the basic set that every developer should be using, all the time.

3. sp_sysmon monitors the (entire) server. It can be used in two ways: as an ongoing monitoring tool, eg grab a full hour of stats, which is the basis for server configuration changes; and as a snapshot, eg. grab a 5 minute snapsot of the server when your process is running and when it is not, and examine the differences. Generally this is for experienced DBAs, not developers, and you need sa_role.

It is all in the manuals, both online and PDF.

Seven hours is a very long time, so you must be using a cursor or similar, and processing single rows rather than sets. Check for regular nightly batch jobs (dumps, update stats, reorgs) weighing in at the 7 hour mark; some of them hold table locks. Of course, if the server is running on Windoze, all manner of strange things, from memory leaks upwards, is standard fare; bounce the box every week at least.

With that order of processing burden, you should keep on eye on your transaction log and tempdb usage.