Is there a way to control which CPU performs what tasks in SQL Server?

Question

Our SQL Server is dual processor server and I notice that overnight when we run certain jobs than one CPU is favored over the other. The favored CPU varies from day to day, but if I look at the difference between the CPU's utilization, there is a definite trend to favor one CPU at a time. Is this normal? Also, is there a way to control which CPU performs what tasks in SQL Server?

Answer

The behavior you have been noticing is normal. The operating system is responsible for how threads submitted to it by applications are scheduled to be run on CPUs. The operating system does its best to try to balance the load on the CPUs it has available to it, but rarely will you ever see an exact even distribution of load on your server's multiple CPUs.

There is no performance penalty when loads are not evenly distributed among CPUs on a server.

By default, SQL Server is designed to use all of the available CPUs in a server, and this is generally the ideal setting for a dedicated SQL Server. SQL Server does have an option that allows you to specify to use less than the total number of CPUs in a server, but this option should not normally be used, as it will hurt SQL Server's performance.

Other than this, there is no way to specify how SQL Server can use a CPU. For example, you cannot tell SQL Server to run queries on one CPU and jobs on another. All SQL Server can do is submit its processing requests to the operating system, and then the operating system decides which CPU to use to run the request.