SQL Server Performance

How to prioritize the queries execution

Discussion in 'Performance Tuning for DBAs' started by vikvin74, Oct 9, 2003.

  1. vikvin74 New Member

    Suppose you have 5 queries lined up for almost simultaneous execution. Each query takes say 20 to 30 minutes to execute. Is there any provisions in SQL Server 2000 to prioritize query exectuion sequence. Like eventhough we say it's simultaneous queries, there will be some fraction of second difference between the two queries. I want SQL Server to execute fist query and put the outher in queue till the first one finishes. Then take the second one and execute it and put the other in queue till that is finished and so on...

    Rational behind this is, suppose each single query takes 10 minutes to execute, but if you process simultaneously, each takes 30 to 40 minutes. So there is no point to put on hold all the four different customers. Better approach will be to adopt the First Come First Serve basis. Well this will be higly senario specific though.

    There should be some way to prioritize the query execution in SQL Server 2000. ANy thoughts will be greatly appreciated.


    Vikas Agrawal
  2. vbkenya New Member

    I am not aware of any quick method to do that but even if it were possible to queue up queries for execution, you would still have to think about the connection timeout settings due to the long waiting periods imposed on each of the connections.

    Nathan H.O.
  3. vikvin74 New Member

    Can you suggest me some other resources to look into....actually I am trying to run some simulation stuff which needs this kind of stuff....
  4. vbkenya New Member

    Have you tried integrating MSMQ (Message Queueing) with SQL Server? It might just work.

    Look at http://www.jmssolutions.com/ for some stuff they offer on this angle of the problem.

    Keep looking in the meantime.

    Nathan H.O.
  5. vikvin74 New Member

    Is message queuing is same as queuing up queries? Actually I want to know how exactly SQL Server executes simultaneous queries. What goes on inside SQL Server engine while simultaneouse queries execution. If suppose processor is getting 100% utilized by the first query, so when next query comes, how processor acts on it? Does it put some of processing stuff of query one and share resources with second query? what happnes is the third query comes after sometime?

    I have experimented it a lot. I have noticed that in such circomstances, all the queries are delayed. So the first query, suppose to take 10 minutes when run alone, will take almost 30 minutes when executed with other SIMILAR kind of queries. So there is no point in putting all the customers on hold requesting some results out of database. isn't better approach will be to adopt First Come First Serve approch. As I already pointed out, it will be very senario specific though, if you know what I mean to say.

    Any thoughts???
  6. cejar New Member

    Can you please privide some samples of the queries you are executing.

  7. homam New Member

    I'm not sure what you mean by different queries? Are they the same SQL query executed by differnet users/processes or different SQL queries?

    I'm not aware of any way to make SQL server bind a particular thread or fiber to a given query/connection and then boost that thread's priority.

    The first thing I would do if my server is hitting the ceiling with multiple queries is to optimize them to execute faster (may be it's time to denormalize) or upgrade the server.

    May be executing all these queries at the same time is causing some locking/blocking problems. Partition your data logically to more tables or physically to more disks, and use dirty reads (NOLOCK) whenever possible.

  8. vikvin74 New Member

    How to use dirty reads? I mean how to do setting for that?

    My question was highly scenario specific. I am already operating under sevier resource constraints....reason for more time taken by simulteneous queries could be related to multithreading concept, thread queuing and timeslicing, OS using roundrobin pattern to execute queries (as pointed out by many people in my next post), but given this system with the current settings, I was thinking that there should be some way to prioritize query execution....like assigning first query the hightest priority, second query should have second highest and so on...

    But it seems queries are handled by threads which are OS issues and one cannot assign or dedicate a thread specific to a query and set the priority. To OS, each request (whether a query or some other process) is just a job and it uses it's set algorithm to execute jobs. This again I guess depends on operating system. It will be different for Windows and different for Unix....

    Thanks for the reply though.
  9. Twan New Member

    Hi ya,

    I think that you'd have to resort to programming a queue mechanism yourself either using MSMQ or a table with a job that polls that table for work to do...?

  10. homam New Member


    Whenever you use the NOLOCK hint after a table name or alias, then you're dirty-reading the table. You can also set a whole block of code to make dirty reads by using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.

    SQL server uses both threads and fibers. Threads are OS kernel objects and their scheduling and priorities are handled by the OS. SQL server uses threads mostly as worker threads waiting for I/O completion ports and async calls. Fibers are SQL server proprietary objects and they are handled by SQL server's internal scheduler. They are used mostly for parallel operations.

    Either way, you can't assign a query to a particular thread of fiber and boost them, and there's a good reason for that because you can easily distablize SQL server. For example, pooling loop on a high priority thread will lock the server dead with 100% CPU utilization and no response whatsover to network connection and admin command. You really don't want that!!

    If you want to execute only one query at a time, then you can queue up the requests in FIFO from a mid-tier component. But that model gets really complicated for multi-user connections and it really doesn't work well for stateless protocols like HTTP because you don't want the clients to time-out.
  11. gaurav_bindlish New Member

    As mentioned, SQL Server internally manages xecution of query and there is no provision for priritizing a query.

    These quesries look like batch jobs to me considerng the response time. For batch jobs I can suggest making a jobs table and putting the execution requests in them. There will be a job running all the time which wll poll this table and execute the queries on the basis of FIFO or priority basis. If the results are needed from the execution od the queries, the same can be stored in predefined temporary then retieved from there.

    Does this sound like a feasible solution?

    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  12. gaurav_bindlish New Member

    I would also recommend looking at the SQL Server audit section mentioned in the site and see if you can reduce the response time of queries.

    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Share This Page