Limiting the number of CPUs used in SQL Server 2012 Enterprise The platform under consideration is SQL Server 2012 Ent. SP3.

We are in the process of migrating a SQL Server environment to VM but highly feel the server was over spec'd to start.  

We want the customer to feel good about going to a VM with less resources as they don't appear to be needed.  

To start we want limit the number of CPUs that are currently being allocated.  Is setting the Processor affinity a safe approach? Any gotchas?

The server was a dedicated physical SQL Server with 32 cores moving to VMware. The thought was to step down the number of CPUs on the old server prior to going to VM to show the customer.  According to the VM team, having a server with that many CPUs is not necessary due to VM.   Also, the server is basically using less than 10% of the available CPUs.
The question is a little hard to understand.


  We are in the process of migrating a SQL Server environment to VM but
  highly feel the server was over spec'd to start.
  
  We want the customer to feel good about going to a VM with less
  resources as they don't appear to be needed.
  
  To start we want limit the number of CPUs that are currently being
  allocated. Is setting the Processor affinity a safe approach? Any
  gotchas?


Is the current server a physical and you want to limit the CPU resources it has access to in order to highlight to the customer that it will run fine on a VM with fewer CPU resources?

Assuming yes, processor affinity may work for this, however, it can cause SQL Server to adopt a 1:1 ratio of Schedulers on SQLOS to Cores on the server, which can cause poor utilisation of even the now reduced CPU resources. See this blog post for more information.

A better solution might be to use Resource Governor to limit the amount of CPU for the default pool to control the amount of CPU resources it can use without introducing the 1:1 affinity issue. See here for more information.

Basically, if you have, say, 24 cores at the moment and want to reduce to 12, then you could set the default pool MAX_CPU_PERCENT to 50. It is not an exact analog for reducing the available cores, but it will give you some confirmation that your workload will be fine with reduced CPU resources.

NOTE: If you enable trace flag 8002 and use processor affinity you can avoid the 1:1 scheduler to core issue. See here for more info. Enabling this trace flag requires a restart.

NOTE 2: If your current server is not physical, then just lower the vCPUs for that server to validate (if you're confident the workload can be sustained on fewer cores).