Sybase ASE - Tracing queries / processes

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.

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

Hi PerformanceDBA, Thanks for the incredibly detailed reply! I haven't had a chance to try out all of the 3 tactics you gave me, but sp_who and sp_lock were exactly what I was looking for. Unfortunately, it didn't help me solve my problem. It did, however, tell me that the spids in Sybase were in the recv sleep state. My program was also waiting on something from Sybase, resulting in a stalemate, and thus the "strange behavior".If it's not too much trouble, I have one more question for you: Is there a way to trace the Sybase ASE Client? (not the DB) For now I am going to try out Ribo.

Jdcc 2010-10-28 21:24:52

If they are stalemated, they are waiting for a resource, which can be readily identified; they will not be "recv sleep" which means sleeping, waiting to receive a packet from the client. Check the AWAITING COMMAND column beware the volumes of output. The other is Auditing. Both have a setup labour cost, but Auditing is configurable.

PerformanceDBA 2010-10-29 13:58:05

Yup, my processes are definitely in the "recv sleep" and "AWAITING COMMAND" states. sp_lock does not show anything related to my processes. I'm guessing my client and server end up waiting on each other, rather than an being blocked by an actual lock. If there is no tracing on the ASE client, then I will have to monitor what my program sends to the client, and the SQL my client sends out to the server. I will give Auditing a try, as it sounds like it doesn't use a man-in-the-middle approach. I didn't know it was configurable, so thanks for that info :)

Jdcc 2010-10-29 21:10:59

I posted this as a total newbie, and now I feel a lot better about maneuvering around Sybase. Thanks for all your help PerformanceDBA!

Jdcc 2010-10-29 21:12:05

Ribo may be easier for you then. The server (connection, spid) is always in a state of "waiting" for commands from the client, except when active (then you will see states change; pio and cpu increasing; etc). So the job is hung on the client side at 7pm (my 2nd last para above). hang around and do some furious checking from 6:45 onwards. That end is easier to check without the labour reqd for Ribo. Don't forget to vote.

PerformanceDBA 2010-10-30 00:30:22

ansaurus

tags:

views:

answers:

Sybase ASE - Tracing queries / processes

related questions