Harder to solve in 12.0 versus 184.108.40.206 and higher due to the new MDA tables.
You can perform monitoring a number of ways to try to isolate it and sp_who is a horrible way to try to do this.
You will want to query against sysprocesses for only active connections. If the user connections are running stored procedures, you can use this function to find out where they are 'stuck'. However, diagnosing this is far more complex than just a quick answer here. You need a skilled ASE DBA to go in and review things as they are running and dig-up the source of the performance problems. In fact, it could also be outside the server in the area of Host memory, disk setup and so on.
select *, suser_name(suid), object_name(id,dbid), linenum
where status != 'recv sleep'
This should be a good start - however, much more work is needed to diagnose this.
Receive sleep is no problem: the rocess is idle and ASE is waiting to receive from the client. Cursors are a stupid way (ASE is a set-oriented engine) to process relational data; and are known to be very slow. You cannot expect bad code to perform well, specially as the volumes grow, the database will slow down.
Performance problems are not difficult to isolate. First, Read up on sysmon, that is the basic and essential monitoring tool. It provides a wealth of information; and is better than MAD tables for many reasons (other than the fact that you do not have them!). Exec sysmon for at least one hour during the busy part of the day, save the output to a text file, and post the file back here.
Of course monitoring the host o/s is separate, you need to
Given the original post, it could be anything from some kind of memory paging in the OS (ie. AIX not having minperm/maxperm set right) to old index statistics to anything.
I wanted to note that cursors should not be 100% avoided - there are some times when they can be useful and make for meaningful code that can be shown to run similarly to set-based logic when looping constructs are needed. Cursors are not an issue for the original poster and I suspect they are not really his original condition.
What the Original poster probably needs is to have a session online or on-site with a Sybase DBA who knows these methods of finding performance issues and that begins his training for performance monitoring. It's not easy for the uninitated but over the course of weeks by using the right commands - he can learn to find problems. I've worked with DBAs of 5-10 years who don't know some of the basics of P&T.
Some tools can be purchased to display the problem queries and so forth - such as Bradmark's tool, for example.
Unless you set it up in a sub-standard fashion, ASE does not page.
If cursors are not a problem for OP, then they wouldn't be showing up for him, re which he has posted. Recv sleep plus open cursor is a stupid error, period. If the code opens a cursor (or a transaction) and then allows user input, that will result in all kinds of problems.
Cursors hold an additional level of locks. Row-by-row means control moves back and forth between the code and the server for every row; set processing moves back and forth once for the set. It is not possible (physically, from an engineering standpoint) to use more resources; to traverse a table row-by-row, and obtain the performance equal to code that uses less resources, and traverses the entire set of rows. It would be very interesting to look at "cursor code that performs the same as set processing".
There are many ways to find out what the server is doing, from scripts and Sybase utilities to cheap PM products to expensive PM products. sp_sysmon is free on ASE 12.0.
To display the query of any spid, once you have isolated it as a poor performer, you do not need an expensive tool: dbcc sqltext(spid)
No doubt OP has read up on showplan, SET STATISTICS, etc.
12.0 and 12.5.x are not "sensitive" to the UPDATE STATISTICS, as 15.x is.
The paging is not Sybase's fault on AIX - but rather how memory is managed there between applications and file system management and it initializes to be more beneficial to file systems - it's up to the Unix admin to set the minperm/maxperm/maxclient to fix it. But that's speculation.
Ok - missed the one note - he did say OPEN CURSOR. That will be based on the SELECT that he cursor is designed on. WHo knows, could be something like a query that uses a convert() function within the where clause.
I think what the OP is asking us for us "give me some queries which will tell me what is going on.
However, OPEN CURSOR is hard to diagnose, but you can run sp_showplan for the SPID in the midst of an OPEN CURSOR to get the cursor name. There, he could get the name, go into code and find the cursor, then work on tuning it on its own. I'd say if he sees OPEN CURSOR, then he should utilize master..monProcessObject to find out which tables undergo the most IOs and then go find out why that OPEN CURSOR is performing poorly.
1 There is no argument re how Unix works in general. When using Sybase ASE as a database server (rather than generic unix), one is supposed to configure it for the purpose. Without speculation then: generally one sets up ASE according to the manuals and experience. If one does NOT set it up, sure, it will swap. Assuming one sets it up based on experience and configuration parameters available, it will not swap, period.
2 It has nothing to do with minperm and the like. The config options required, for setting up memory usage correctly for ASE (in order to obtain the result of no swapping), re quite different.
3 That is not to say that Solaris resources should not be monitored, by all means do so, but paging is only one item, and the least relevant (if the configuration has been set up with experience and knowledge). Most of us run vmstat (shows paging as well as metrics that are more relevant to a db server) and iostat continuously.
4 OP cannot utilise master.mon% because MAD tables are not available in his release. sp_sysmon is avaliable in his release. It provides substantially more than MDA tables; the figures can be relied upon because they do not change for no reason and they correlate easily to other figures; and it does not stack trace or crash the server.
Last edited by Derek Asirvadem; 11-06-09 at 20:46.
1) How long are you having the performance problem? Did any change happen in dbs before these problems started? If yes, analyze the changes and potential impact.
2) How often these problems happen? Daily? When users perform any specific action? What time exactly.
I would start deeper investigation in the moment when there is some user complaining that he has the problems right now.
1) I would investigate the logged in users processes and analyzed all of them with sp_showplan SPID, null,null, null
- from my experience this is usually enough :-) Look for tablescans and complex nested queries (particularly subqueries with aggregate functions are performance killers) or cursors with nested tablescans in subqueries, etc... :-)
- if there are no table scans or suspicious loops, I would note down indexes the queries are using and compare them with output of optdiag utility to verify that the queries are using CORRECT indexes. Check optdiag output also to verify all statistics are up to date. These things may be result of implemented change.
2) I would look into sysproccesses table and check i/o for the psids and cpu values for the spid and also verify that the processes are not blocked
3) definitely run sp_sysmon and look for high and nonstandard values
4) check CPU and memory utilization on unix level - poor performance may not be necessary ASEs fault