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.




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |