Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-11-22 : 07:15:59
|
| Tate writes "1. Does SQL server do multitasking in query processing? How can I use it?2. Can one assign priority on SQL queries? How can I assign the priorities?Most Important Question3. Is it possible to pause a query and start another query? Let the second query finish first and then resume the first query? How this can be done in SQL server, or in any data base?Example:Query 1 takes 2 hours to finish. Query 2 takes 5 minutes to finish.Query 1 matches non-urgent job to nurse-availability.Query 2 matches urgent jobs to nurse-availability. (High priority query)This means both queries uses same nurse-availability table, and mark availability open slot to "filled" once the job is found for her.Both query has different set of jobs to process.Let's say: Query 1 is under process; query 2 came in.I want query 1 to pause and query 2 to start.Once query 2 is finished then query 1 should resume where it left off.Obviously, once query 1 resume it work, it will find less availability slot to fill because some of the availability slot might have been filled by the query 2 (urgent) job.Tate " |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-11-22 : 07:24:32
|
1. Yes it can. There is a server setting called "max. degree of parallelism" that can be changed to limit the number of processors used on a multi-processor machine. It defaults to 0, which actually uses all available processors, so you probably don't have to change this.2. You can, but it involves C++ code and is not supported by Microsoft. It allows you to change thread priority of the query that is running. There is a definite risk in doing this and it's not recommended. For details, pick up a copy of Ken Henderson'sThe Guru's Guide to SQL Server Stored Procedures, XML, and HTMLHe has a code listing that shows how to do this.3. You can't pause a query, if you cancel it will start over again. Worse, if the operation creates temp tables or does INSERT/UPDATE/DELETE operations, those will be rolled back. So if you cancel a 2-hour query when it's 1-1/2 hours in, it could easily take 1-1/2 hours to roll back.I would suggest looking at the queries you are running and trying to improve them instead. For instance, if your code is using cursors, that's almost certainly the reason they take 2 hours to run. Changing the procedure to use set-based operations will speed it up, so much so that it could quite possibly run in only a few minutes, or less.We've had people post 8 hour cursor code that we converted to 5 minute set-based code. If you'd like to post your code we could take a crack at it. (I always shoot myself in the foot when I say this)  |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-22 : 09:20:49
|
| 3.You can change query 1 so that it works in batches of say 5 mins each (make the batch size configurable). After every baatch it checks to see if query 2 wants to run - if it does it lets it run before continuing. Also means that if query 1 fails you don't have to start at the begining again.You might want to run both queries inside a control proc which decides which batch to run.That's how http://www.nigelrivett.net/Scheduler.htmlworks.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
Prioritized Queries