SQL Server Performance Forum – Threads Archive

Controlling batch/statement priorities

Hi, I am looking at ways to run large batch jobs and OLTP applications agains SQL server without the batch jobs stepping on the OLTP applications. OLTP is run in the form of classic 2 tier VB6 applications.
Batch or reports are in the form of Crystal reports running stored procedures on SQL server and rendering reports or running scheduled SQL agent jobs. Is there a way from within Crystal or SQL agent jobs to instruct SQL server to run the stored procedures at a lower priority than everything else? I realize that SQL server has a process priority but are there priority levels that can be assigned to threads or batches of statements that are run within SQL server. Thanks
Dilan

---

I don’t think there is any way to accomplish what you need…
MohammedU.
Moderator
SQL-Server-Performance.com

---

Rather stating to run them in lower priority, you can perform the batch jobs as a smaller batch jobs to avoid any performance loss. At the same time you have to consider the SQL SErver configuration and server resource usage, memory, cpu and disks etc. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

At the very least, you can do SELECTs with NOLOCK’s. It might allow dirty reads. depending on how critical the reports are or how impt the up-to-date numbers are you can decide. ***********************
Dinakar Nethi
SQL Server MVP
***********************

---

]]>