More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
How do I capture a process's SQL in Sybase?
This is a bit of a wide question, and there are many answers to it. Primarily, it depends on why you are trying to capture it. If you are trying to debug a troublesome stored procedure that is behaving differently in production to how it did in testing, then you might look at the DBCC method. Alternatively, if you wanted to do some longer term profiling, then auditing or one of the third party tools might be the way forward. If you know of methods that are not included here, please let me know.
DBCCs
If you want to look at the SQL a particular process is running at the moment, one of the following should work. Not sure which versions of ASE these work with. Remember to issue dbcc traceon(3604) before running any of the dbcc's so that you can see the output at your terminal.
* dbcc sqltext(spid)
* dbcc pss(0, spid, 0)
The first of the commands issues the SQL of the spid only a bit like this:
[27] BISCAY.master.1> dbcc sqltext(9)
[27] BISCAY.master.2> go
SQL Text: select spid, status, suser_name(suid), hostname,
db_name(dbid), cmd, cpu, physical_io, memusage,
convert(char(5),blocked) from master..sysprocesses
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
[28] BISCAY.master.1>
The second issues an awful lot of other stuff before printing the text at the bottom. Mercifully, this means that you don't have to scroll up to search for the SQL text, which is in much the same format as with dbcc sqltext.
There are a number of third party tools that will execute these commands from a list of processes. One of the problems is that you do have to be 'sa' or have 'sa_role' in order to run them.
Certainly the first, and possibly both, have one major drawback, and that is that they are limited to displaying about 400 bytes worth of text, which can be a bit annoying. However, if what you are trying to do is catch a piece of rogue SQL that is causing a table scan or some other dastardly trick, a unique comment in the early part of the query will lead to its easy identification.
Monitor Server
Since ASE 11.5, monitor server has had the capability for capturing a processes SQL. See Q1.6.2 for how to configure a Monitor Server Client. When you are done, you can get see the SQL text from a process using the "Process Current SQL Statement" monitor. The output looks like this.
Auditing
The second way of wanting to do this is for a number of processes for a period of time. There are several methods of doing this. Probably the most popular is to use auditing, and it is almost certainly the most popular because it requires no additional software purchases.
Auditing is a very powerful tool that can collect information on just about everything that happens on the server. It can be configured to capture 'cmdtext' for any or all users on a system. The data will be loaded into the sysaudits database for later perusal. The SQL captured is not limited to a number of bytes, like the previous examples, but if it is more than 255 bytes long, then it will span several audit records, which must be put back together to see the whole picture. To be honest, I am not sure what happens now that varchars can be greater than 255 bytes in length. Personal experience with auditing leaves to think that the load on the server is up to about 3%, depending on the number of engines you have (the more engines, the more of a load auditing is) and, obviously, the number of processes you wish to monitor. I calculated 3% based on auditing all of 400 users, each of which had 2 connections to the server, on a server with 7 engines.
Ribo
Another option for capturing the SQL text is to use the free Ribo utility that is provided with as part of ASE these days. This is a small server written in Java as an example of what can be done using jConnect. This utility is nice in that it does not place any load on the ASE server. However, it probably has an effect on the client that is using it. This utility's other draw back is that each client that you wish to monitor via Ribo must be directly configured to use it. It is not possibly mid-session to just magically turn it on.
The way it works is to act as an intermediary between the ASE server and the client wishing to connect. All is SQL is passed through and executed exactly as if the client was directly connected, and the results passed back. What the Ribo server does is enable you to save the inbound SQL to a file.
3rd Party Tools
Again, there are a number of third party tools that do this job as well, OpenSwitch being one of them. There are also a number of third party tools that do a better job than this. They do not have any impact on the client or the server. They work by sniffing the network for relevant packets and then put them pack together. In actuality, they do a lot more than just generate the SQL, but they are capable of that.
(Continued on next question...)
