SQL Server Performance

Query priority?

Discussion in 'Performance Tuning for DBAs' started by arb, Dec 2, 2002.

  1. arb New Member

    One of my colleagues recently asked me an interesting question which I was unable to answer: Is it possible to modify the priority of a single query? The specific example he was concerned about was a very long running batch he needed to run which was going to take about 2-3 hours. Unfortunately it would chek up a lot of CPU time, and slow down the system for the rest of the users. It would have been nice to be able to run the query at a lower priority, so it still ran during the day and he could check the results before he left for the day (he is a contractor only in once a week), but would not affect the performance of the rest of the system.

    After much digging around, I could not find any way to do this and we resorted to scheduling a job to run overnight and trusted that it was going to work okay, which it did. (He had done extensive testing in our test environment, but is a natural pessimist - which is a handy thing some times.)
  2. Chappy New Member

    I dont think this is possible, though it would be a useful option.

    The closest I can see is scheduling it as a job for when the CPU is idle in SQL Agent (you can define what constitutes idle on the Agent properties sheet). However, if its already a scheduled job, I expect youd probably prefer to leave it to run at a set time.
  3. bradmcgehee New Member

    I have received this question a lot from people, and as Chappy has said, you can't change the priority of a single query. Scheduling a job to run a low use times is about your only choice, assuming the batch's performance itself can't be boosted. Have you looked into speeding up the batch job?


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  4. arb New Member

    We have spent quite a while optimising the batch and it is almost as fast as we can make it. Unfortunately it is a very complex task, but thankfully it is also used very infrequently. In this case we ran it over night, but it did put our rerlease schedule back a week for that particular product due to availability of staff. I guess I should submit this to Microsoft's wishlist for SQL Server...
  5. joechang New Member

    if you have a query that:
    1) takes several hours to run
    2) updates and inserts a large number of rows into existing tables
    3) can be done during off hours

    you might look at the impact of existing indexes on your query
    and consider dropping some of the existing indexes during this procedure
    and recreating the index after the procedure

    and possibly creating and indexes for specific steps in your procedure

    also, take a look at disk loading for the data and log on both the database and the tempdb, some older disk drives cannot sustain very high sequence small block transfers, Good drive should be able to >2,000 0.5K sequential I/Os per sec while some older drives can do only ~150/sec
  6. sqljunkie New Member

    I've never heard of a way to set the priority of a query although it would be a nice option to have.

    There is a way to restrict the amount of cpu resources a query can use. You can specify the OPTION (MAXDOP 2) to restrict the amount cpus a query can run on to two. Check out BOL on MAXDOP.
  7. arb New Member

    quote:There is a way to restrict the amount of cpu resources a query can use. You can specify the OPTION (MAXDOP 2) to restrict the amount cpus a query can run on to two. Check out BOL on MAXDOP.

    Unfortunately this batch is more than just a single query, and besides, the query plans used for most of the individual queries in the batch that I looked at did not use any parallelisation. However, I did not realise that this option could be set on a per query basis - it might come in handy for other, similar time-intensive tasks.
  8. arb New Member

    quote:Originally posted by joechang

    if you have a query that:
    1) takes several hours to run
    2) updates and inserts a large number of rows into existing tables
    3) can be done during off hours

    you might look at the impact of existing indexes on your query
    and consider dropping some of the existing indexes during this procedure
    and recreating the index after the procedure

    and possibly creating and indexes for specific steps in your procedure

    also, take a look at disk loading for the data and log on both the database and the tempdb, some older disk drives cannot sustain very high sequence small block transfers, Good drive should be able to >2,000 0.5K sequential I/Os per sec while some older drives can do only ~150/sec

    Unfortunately we really wanted to be able to run this query during hours without overly impacting on other users, so the idea of dropping/recreating indexes would not have worked in this case. We do drop/create specific indexes in several of our overnight batch jobs though, and it has an incredible impact on the performance of some of the longer running batches we have.
  9. sqljunkie New Member

    For my own reference arb, how are you determining the parallelisation with specific queries?
  10. arb New Member

    quote:Originally posted by rortloff

    For my own reference arb, how are you determining the parallelisation with specific queries?

    I simply viewed the execution plan for the queries involved, and the execution plans did not show any parallelisation.
  11. Chappy New Member

    I believe SQL 2000 allows up to 32 instances of SQL Server running in the same machine. Now, Im not suggesting this is a good solution... but I wonder if it would be possible to start a second instance of SQL Server running at a fairly low NT process priority, and have this instance service your low priority query.

    Of course, this server would eat up a chunk of RAM which personally Id resent giving it, but Id be very interested in playing around with this scenario to see how it fared.

    Has anyone ever done anything similar ?
    What circumstances would you normally want to run more than one instance on the same server. Unless the server is massively multiprocessed with a lions share of RAM, I cant see why youd want to add overhead of a second instance.
  12. bradmcgehee New Member

    The only practical case where I have seen people use more than once instance of SQL Server on a box is for testing and development purposes. Using multiple instances is a big drag on hardware resources for production databases.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  13. gaurav_bindlish New Member

    One solution that I can think of is to replicate the database on a available small machine and run the batch job on the same. This shall free up the resources on the main server and the batch job shall have enough time window to finish.

    Gaurav
  14. satya Moderator

    Arb, what is the value set for 'max degree of parallelism ' on the server.
    Run SP_CONFIGURE from QA to get the values.

    HTH

    Satya SKJ
  15. alzdba Member

    IMHO querypriority is something everyone would want to have, so what's the point of it. Now we can use the sqlagent to scedule processes. If you know what's running on the server, when and what's the impact, you can manage it. If you don't, how can you determine priority ?

    After queryperformance tuning, maybe you could try to break it into logical pieces that can be run in parallel.

Share This Page