Michael Peppler
Sybase Consulting
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
Sybase on Linux FAQ
Sybperl FAQ
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: Ashu Joglekar <ajogleka at ltcm dot com>
Subject: Re: Sybase Query Analysis
Date: Apr 8 1999 3:34PM

I think your options are:

1) Buy Cyrano's product (see Michael's earlier email)
2) If you have purchased open server already, use it to write your own simple
Cyrano-like program
3) If all your queries are stored procedures, there are other commerical tools
that will check the execution plan of each stored procedure and report those
that do table scans, etc.
4) You could use auditing : sp_auditlogin "loginname", "cmdtext", "on".  This
will store all the SQL sent by loginname in the extrainfo column in

NOTE: Auditing generates HUGE amounts of logging. I have had serious problems
while auditing selects on tables because if the audit database fills up the
server disallows all audited activity. The worst thing is running out of space
when your are auditing logins to the server because the server won't allow any
more logins, including those by SA.

5) You could try running SQL Monitor (or Sybase Central) and watch for tables
that are hot spots and try examining the queries that run against those tables.
Not as thorough as examining all queries, of course!
6) Are all your clients written in C - DBLIB? You could call dbrecftos(char
*filename) in each app to record all SQL sent from that app to a file and then
rerun the queries using showplan, statistics io and friends. Does Sybperl
support this call, Michael? Does anyone know what the CTLib equivalent is ?

7) I just thought of this and have no idea if it might work: I believe Sybase
publishes their TDS format, so it should be possible to write a C program that
(a) listens in on the machine:port that the SQL server sits on by fudging the
interfaces file and (b) using it to log start time, query text, end time, etc.
for each query by sitting in between the real server and the clients.... if
someone does this as free/share ware, the sybperl community will be very
greatful :-)