Is there any way to allocate processor utilization to SQL Server jobs?

Question

In the past couple of weeks, we have been running some jobs that take a lot of the CPU usage. I would like to know if there is any possible way to :

1. Distribute between processors, the scheduled jobs.
2. Minimize the maximum amount of CPU usage that a job can take.

If there are any other solutions, I would grateful to accept them and learn them.

Answer

Your question is a very common one, unfortunately, there is no good answer. While SQL Server has some limited ability to control how many CPUs it uses, and there is the ability to give the mssqlserver service a priority boost over other applications running on the same physical server, there is no way to assign specific SQL Server processes or jobs to a specific CPU. Nor is there any way to assign only a portion of a CPUs resources to a specific process or job.

I assume you are asking this question because you want to more efficiently make use of the CPU resources on your server, and perhaps because the CPU utilization of your server has hit a bottleneck.

Assuming you have a CPU bottleneck, these are some of the most common ways to overcome it:

• Get faster CPUs, or add additional CPUs.

• Get CPUs with a larger L2 cache.

• Tune your application so that it doesn't have SQL Server does not have to access the disk as often. For example, add indexes so table scans aren't needed, normalize your database to eliminate redundant data, etc.)

• Schedule jobs so that they don't overlap.

• Tune your queries to reduce the CPU load.

• Move some of the processing load to another SQL Server.

• Consider turning on Windows NT fibers.

• Be sure that both OLTP and OLAP queries are not being run on the same server. These different database applications should be performed on separate servers.

• Ensure that SQL Server is the only application running on the server (other than server management utilities).

• Remove or disable all unnecessary services.

I mentioned earlier that SQL Server has some limited ability to control the number of CPUs it can use, and that there is the ability to give the mssqlserver service a priority boost over other applications running on the same physical server. Generally, if you are running SQL Server on a dedicated physical server, then there is little or no advantage in taking such action, and I don't personally recommend playing with the SQL Server settings that permit this.

If you are running SQL Server on a shared server (which I don't recommend from a performance perspective) fiddling with the above options might provide some benefits. But be prepared to do a lot of testing to be sure you get the results you expect.