I have to run stored procedures with different arguments a couple of times (10 000+) on a sybase server. I would like to launch the queries in parallel from a script. Before starting a new query I would like to determine the load of the Sybase server to decide automatically and dynamically how many queries can be launched in parallel.
Now I count the average response time of the queries and if it grows too high, then I stop starting new query, if it is lower than a predefined value then I can start more queries. But it is very dependent on the stored procedure and sometimes even on the arguments passed to it, so I would like to get some more objective measures similarly to the Linux load average returned by cat /proc/loadavg (uptime, top). Gmake has a similar feature to run in parallel and start a new process only if the load average is less then a specified value (see -j and -l options).
Is there a way to get some metric which correlates to the server's actual load?
I'm not an administrator and I'm launching the queries remotely.
After some search I found a stored procedure which can provide a lot of detailed information about the system load of a particular sysbase server. It is called sp_sysmon.
It provides a lot of information. Some of them may be useful. The only issue I could find that the user need SA role to run this stored procedure.
An example (this prints almost all statistics):
sp_sysmon "00:01:00", noclear, @applmon = appl_and_login
It provides information about all "Engines" belong to the Sybase server, Network traffic, Disk I/O (even per device), Worker Process, Parallel Query, Task Context Switches, Task, Buffers, Transactions, Indices, Locks, Data Cache, Memory etc.
The output can be reduced adding different arguments to it. I have to play a little bit with it, but it seems a pretty cool tool.
It seems the 'sa_role' problem also can be solved (Description Granting SA rights to non-SA users, Source: grant_sa.sql).
But of course there is a drawback. Documentation states:
sp_sysmon contributes 5 to 7 percent overhead while it runs on a single CPU server, and more on multiprocessor servers. The amount of overhead increases with the number of CPUs.