Any way to set a query’s priority? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Any way to set a query’s priority?

I have an web application that can submits tasks to a job processor to stop long running tasks from tying-up the users web browser. General these background jobs are producing reports that the user can pickup at a later time. In SQL servers eyes, the background jobs have the same priority as the interactive web application. Is there anyway to issue an SQL statement to SQL Server that would cause it to process at a lower priority? The application is written in VB6 and ASP with ADO as the data access layer.
Not as such. The best you can do is schedule them as jobs which start when the cpu is idle. You specify the criteria of what it should consider to be idle in server properties.
Chappy, Thanks for the quick answer. It looks like there is no way to do exactly what I want. However, your suggestion about submitting the job when the CPU is idle is a good alternative. What is the best way to determine the CPU level of the DB? Is using the same technique as sp_monitor the only option?
No, Id use performance monitor. If you monitor the cpu over a period of time, you should hopefully see periods where the cpu is less active, or maybe even truly idle. Be aware that if you do schedule jobs to run when the cpu is idle, there is no guarantee this condition will be met daily, and so your job may end up not running at all if the server has a persistently heavy load!
]]>