Can I programatically tell SQL Server how to allocate CPU resources?

Question

My SQL Server is running on a dual processor box. What I would like to do is to use one of the CPUs for background tasks, and the other processor for online tasks. And if there are no background tasks running, I would like both processors to serve online tasks. How can I do this programmatically?

Answer

The short answer is that you can't. I assume that by online tasks you are referring to OLTP activity (such as SELECTS, INSERTS, UPDATES, and DELETES), and that background tasks refer to batch jobs, or perhaps database maintenance jobs.

SQL Server treats all jobs equally, and you can't give one user connection a higher priority than another, nor can you assign a specific user connection to a specific processor.

I assume that your goal is to balance the workload on the server. The easiest way to do this is to schedule batch jobs or maintenance jobs using the SQL Server Agent to occur during times of lower "online" activity. In most cases, this should allow you to balance out the workload throughout a typical day.