Microsoft SQL server virtual machines CPU scheduler status shows "VISIBLE OFFLINE".

Products

• VMware vSphere ESXi

Issue/Introduction

• Virtual Machines (VMs) running Microsoft SQL Server are reporting a mismatch in the number of available CPUs within the SQL Server application itself. Specifically, the CPU scheduler status shows some CPUs as "VISIBLE OFFLINE," indicating they are not being used by the database engine.
• The Windows Task Manager correctly displays the number of virtual CPUs (vCPUs) assigned to the VM, confirming that the resources are available at the operating system level. The discrepancy points to an internal SQL Server configuration or limitation. 
• To verify this issue, you can run the following query inside the SQL Server instance:
  
  SELECT * FROM sys.dm_os_schedulers WHERE status = 'VISIBLE OFFLINE';
  
  If the query returns rows, it confirms that SQL Server is intentionally not using all available cores. If all schedulers are online, the query will return no results. 

Environment

• VMware vSphere ESXi

Cause

• The root cause is not within the VMware ESXi hypervisor, as the vCPUs are correctly presented to the Windows OS. Instead, the issue stems from a configuration or limitation within the Microsoft SQL Server application. Below are some of the potential causes.

1. 1. SQL Server Licensing Limitation: Certain SQL Server editions have license-based limits on the number of logical cores or sockets they can utilize.
   2. Processor Affinity Mask Configuration: A manual configuration in SQL Server can force the instance to use only a subset of the available CPUs if Processor Affinity Mask Configuration is set in the SQL Server properties.

Resolution

• Since the problem is with the SQL Server application layer and not the underlying hypervisor, further action requires expertise in SQL Server. The recommended course of action is to contact Microsoft SQL Server support for further assistance.