sybperl-l Archive

From: "Peter Collard" <collardp at rabo-bank dot com>
Subject: RE: Sybase Query Analysis
Date: Apr 12 1999 9:30AM

I missed the start of this thread as I was on hols, but 11.5.1 allows SQL 
Central to view active queries. However if you have open server its easy to 
modify the example code sybase ship to log the sql and use your open server 
as a gateway.


___________________________________________________________________
Regards
Peter Collard (x3863)
Senior Sybase DBA
Rabobank UK

The views expressed in this correspondence are those of the author and do 
not necessarily represent those of Rabobank International
_______________________________________________________________


-----Original Message-----
From:	Ashu Joglekar [SMTP:ajogleka@LTCM.COM]
Sent:	Thursday, 08 April 1999 16:34
To:	SybPerl Discussion List
Subject:	Re: Sybase Query Analysis

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
sybsecurity.dbo.sysaudits.

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 :-)

Cheers,
Ashu