2

A while ago a consultant said that I should set processor affinity when I'm running SQL Server on VMWare. The advice was to disable CPU0 so it was free for the OS.

When I read the "Architecting Microsoft SQL Server on VMware vSphere"-PDF on the site of VMWare I find following:

VMware recommends not using CPU affinity in production because it limits the hypervisor’s ability to efficiently schedule vCPUs on the physical server.

To me it looks like overkill to set processor affinity so a core would be free for Windows. What is your advice about this?

  • I would personally follow the manufacturer's advice over a "consultant's". Did the consultant give a specific reason or cite a reference where the "benefits" of performing such an action would produce viewable changes? – SQL_Hacker Aug 29 '17 at 14:37
  • @SQL_Hacker: The only thing he said was that CPU0 should be free for Windows – Frederik Vanderhaegen Aug 29 '17 at 14:51
2

The Microsoft documentation states not to change processor affinity unless it is necessary.

affinity mask Server Configuration Option

Because setting SQL Server processor affinity is a specialized operation, it is recommended that it be used only when necessary.

It also states that the feature will be removed in a future version.

This, combined with the VMware documentation stating that it should not be changed, should be quite convincing. Additionally, recommendations from the SQL Server community online to change it by default seem to be lacking.

Lastly, Window kernel processes run at a higher priority than other processes on the system (unless they are unwisely set to real-time priority), so all critical processes in Windows will get the CPU they need regardless of how much CPU SQL Server wants at any given time. Ref. Thread Priorities

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.