Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2003
    Location
    Netherlands
    Posts
    63

    Unanswered: Resource allocation for processes

    Hiya,

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

    Any insight would be greatly appreciated.
    Greetz,

    Bastiaan Olij

  2. #2
    Join Date
    Nov 2002
    Posts
    207
    Check this out...

    http://sybooks.sybase.com/onlinebook..._BookView;hf=0

    Chapter 37 & 38

    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.

  3. #3
    Join Date
    Feb 2003
    Location
    Netherlands
    Posts
    63
    Hey mkalsi,

    Thanks, should have known to start there. Loads of reading for me to do Although if people can give me some pointers, that would still be very nice.
    Greetz,

    Bastiaan Olij

  4. #4
    Join Date
    Feb 2003
    Location
    Netherlands
    Posts
    63
    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....

    Thanks for any insights!
    Greetz,

    Bastiaan Olij

  5. #5
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    Server affinity can be specified using sp_bindexecclass
    1) in session level
    2) in program level
    3) in stored procedure level


    By process, you can change the priority using
    Code:
    sp_setpsexe spid, "priority", "HIGH"

  6. #6
    Join Date
    Feb 2003
    Location
    Netherlands
    Posts
    63
    fadace, many thanks! missed the bit on using it directly with spid
    Greetz,

    Bastiaan Olij

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •