Sorry if this has been posted before, couldn't find a good thread on this one.
I have a problem with Sybase ASE 12 at a customer site and am in need of some advise on this espect of tuning my server as I haven't schooled myself in this yet.
Anyways, the thing that seems to happen is that when a user runs a rather long running query, that process gets 99% of all resources and all my other users, running small quick queries only get a little bit of resources and seem to run forever.
The situation I want however is that my long running query only gets all processor power and cache usage if no other processes require this. When another process executes a query, it should get priority over the longer running query so it can do its business quickly, temporarely slowing down my big query. This way my big query can take its time (this user knows he'll be waiting long for his results anyways, usually we are talking about a report) however my other user gets his/her results just as quick asif the server was doing nothing.
How do I manage this? I know I can configure prioritized engines, possible that would be an area to look at? Also in what sort of way can this be automized. It may be that a certain rapport takes a second to run when the user selects a very restrictive filter (runs a planning rapport for a single day) but that same rapport can also be a long running one if a less restrictive filter is selected (runs a planning rapport for a whole month).
I would give less resources to large queries for which users can wait for the output so that other users are not affected. I don't think based on the filter or on expectation that a query will take more time you can automatically adjust the resources.
Specific question on this one. Just been reading about execution classes and how I can setup that certain programs, stored procedures, etc. can be assigned an execution class to give them priority, or take it away.
Can I also change the execution class for my current session on the fly? So lets say my rapport generator knows that a large rapport is going to be run and wants to change the execution class temporarely for that, but keep it as is when it needs to run a small rapport (usually based on # of days the rapport spans.
The answer is probably in the section I was just reading but somehow I missed it....