How to hot-add a vCPU to a virtual SQL Server

By: Simon Liew   |   Updated: 2013-10-04   |   Comments (3)   |   Related: > SQL Server Configurations

Problem

I have SQL 2012 Enterprise installed on a Windows Server 2008 R2 Enterprise Edition virtual server. One of the benefits of running SQL on virtual environment is the capability to present additional vCPUs to the virtual server online and real-time without interruption to running processes. Our VM administrator normally presents only 1 vCPU on the virtual server and extends as required.  Can you describe how SQL Server is able to detect hot-added vCPUs in my virtual server?

Solution

SQL Server on versions 2008 and higher have the capability to support hot-add CPUs, but there are some hardware and software requirements to be able to utilize hot-add CPUs. This tip describes how to add a hot-add vCPU on a virtual server, but the methodology is the same to hot-add a CPU by physically adding new hardware or logically by online hardware partitioning. The reference of vCPU in this tip refers to a CPU assigned to a virtual machine.

SQL Server by design does not automatically start using the hot-added vCPU when presented to the virtual server. In order for SQL Server to start utilizing the hot-added vCPU online, the RECONFIGURE command needs to be executed each time a vCPU(s) is hot-added in order for SQL Server to create a scheduler for the hot-added vCPU.

To better understand the solution, the steps below will guide you.

Simulate a Work Load for SQL Server Virtual Machine

A VM guest is provisioned to present one vCPU on a Windows 2008 R2 SP1 Enterprise Edition that has SQL 2012 SP1 Enterprise Edition installed.  Let's simulate running a workload on the virtual server. The query below will generate a CPU intensive workload by producing a Cartesian product of rows from the same tables.

IF OBJECT_ID('tempdb..#temp', 'U') IS NOT NULL DROP TABLE #temp
GO
SELECT a.*
into #temp
FROM master.dbo.spt_values a
CROSS JOIN master.dbo.spt_values b
CROSS JOIN master.dbo.spt_values c
CROSS JOIN master.dbo.spt_values d
GO

Windows Task Manager shows full vCPU utilization when observing its usage in the virtual server. This indicates vCPU stress on the virtual server and normally the decision would be to add more vCPUs.

Checking SQL Server Processor Affinity

Before adding a new vCPU, let's check the SQL Server Processor properties page to see how SQL Server is setup for this 1 vCPU. Right click on the instance name in SSMS, select Properties and go to the Processors page.  The checkbox "Automatically set processor affinity mask for all processors" is grayed out because there is only 1 vCPU available. Hence, SQL Server will not allow the processor affinity mask to be set.

Checking SQL Server Schedulers

Check the SQL Server Schedulers DMV to confirm there is only one scheduler (Scheduler ID 0) that can accept user requests. The DMV sys.dm_os_schedulers returns one row per scheduler in SQL Server and each scheduler is mapped to an individual processor.  Schedulers with ID numbers less than 1048576 are used to schedule regular queries from users and ID numbers greater or equal to 1048576 are used internally by SQL Server such as the Dedicated Admin Connection.

SELECT * FROM sys.dm_os_schedulers
GO

Check SQL Server Affinity Using T-SQL

Checking the SQL Server affinity mask confirms SQL Server is configured to utilize all vCPU(s) indicated by the value 0 as shown below.

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'affinity mask'
GO

Adding a New vCPU to SQL Server

Now the VM administrator hot-adds 1 more vCPU to the virtual server. This can be done online without interrupting any running processes on the server. After hot-added the vCPU can be seen in Windows Task Manager as expected. Let's stop the workload query that we ran above and re-execute.  Now it seems both vCPUs have a "balanced" load, but they are only being utilized 50% of the time. This is expected since SQL Server is still unable to utilize the hot-added vCPU.

Check Number of Processors SQL Server Can See

In SSMS, if we right click on the server name, select Properties and go to the General page we can check the SQL Server instance properties and see that SQL Server is now showing 2 vCPUs.

SQL Server schedulers are the component within SQLOS that schedules CPU time for a task. If we re-check the steps above we will see that SQL Server still sees 1 vCPU and 1 scheduler.  Hence, SQL Server will not be able to utilize the hot-added vCPU yet because there isn't a scheduler that can assign a workload to the hot-added vCPU.

Run Reconfigure for SQL Server to See Hot-added vCPU

The command below will trigger SQL Server to detect the hot-added vCPU as a resource

RECONFIGURE
GO

Check the SQL Server Processors properties page again and the hot-added vCPU appears as CPU1.

Execute the query below to check the SQL Server Schedulers DMV again, SQL Server has now created a scheduler (Scheduler ID 1) for the hot-added vCPU with the description VISIBLE ONLINE HOT_ADDED in the status column.

SELECT * FROM sys.dm_os_schedulers
GO

Stop and re-execute the workload query. It still appears the workload behavior is similar where we only have 50% utilization. Even though an additional vCPU is presented to Windows Server, SQL Server somehow does not seem to be utilizing the hot-added vCPU.

The workload query is an intentional flaw introduced to execute the query in serial mode or single threaded. How do I know? The workload running the CROSS JOIN is on session id 59. If I execute the query below, it will provide the query details and number of threads created to execute the workload. In my case, only 1 thread is running the workload, essentially meaning that the query is running serially on Scheduler ID 1.

SELECT 
 STasks.session_id, SThreads.os_thread_id, Txt.text, Sch.scheduler_id
FROM sys.dm_os_tasks AS STasks
INNER JOIN sys.dm_os_threads AS SThreads
 ON STasks.worker_address = SThreads.worker_address
INNER JOIN sys.dm_os_schedulers Sch
 ON Sch.scheduler_address = SThreads.scheduler_address
INNER JOIN sys.dm_exec_requests Req
 ON Req.session_id = Stasks.session_id
CROSS APPLY sys.dm_exec_sql_text(Req.sql_handle) Txt
WHERE STasks.session_id = 59
GO

The reason to introduce a high load serial operation is to show sometimes observing high vCPU utilization does not mean adding more vCPUs would improve performance if the highest load operation is not benefiting from parallelism. It is an extreme example, but it highlights the importance of understanding your workload especially when adding CPU(s) to SQL Server on a physical server, it is a very costly exercise so you would want to get it right.

Simulate an OLTP Workload to Use Multiple Processors

Now to simulate an OLTP workload, I execute the below query using 12 query sessions that perform INSERTs into a temp table.

-- Execute the query below in 12 separate query windows
CREATE TABLE #test
 (
 col1 int NOT NULL IDENTITY (1, 1),
 col2 varchar(50) DEFAULT 'test values',
 col3 datetime DEFAULT GetDATE()
 ) 
GO
SET NOCOUNT ON
WHILE 1 = 1
INSERT INTO #test DEFAULT VALUES

Observe SQL Server scheduling the request, SQL has the smarts to properly balance the 12 INSERT sessions across the 2 schedulers to handle the workload.

SELECT 
 STasks.session_id, SThreads.os_thread_id, Txt.text, Sch.scheduler_id
FROM sys.dm_os_tasks AS STasks
INNER JOIN sys.dm_os_threads AS SThreads
 ON STasks.worker_address = SThreads.worker_address
INNER JOIN sys.dm_os_schedulers Sch
 ON Sch.scheduler_address = SThreads.scheduler_address
INNER JOIN sys.dm_exec_requests Req
 ON Req.session_id = Stasks.session_id
CROSS APPLY sys.dm_exec_sql_text(Req.sql_handle) Txt
WHERE STasks.session_id <> 61
GO

 In the Windows Task Manager, the vCPU utilization has increased on both processors.

Changing CPU Scheduler Status

If the SQL Server service or the Windows Server is restarted, the hot-added vCPU description in the scheduler DMV status column will change to VISIBLE ONLINE as shown below.

Next Steps

• SQL Server requirement on hot add CPU support http://technet.microsoft.com/en-us/library/bb964703(v=sql.105).aspx
• sys.dm_os_schedulers http://technet.microsoft.com/en-us/library/ms177526.aspx
• SQL Server affinity mask configuration http://technet.microsoft.com/en-us/library/ms187104.aspx
• vCPU vs CPU definition http://www.mssqltips.com/sqlservertip/2416/sql-server-virtualization-overview-part-1-of-5/
• sys.dm_os_threads http://technet.microsoft.com/en-us/library/ms187818.aspx

About the author

Simon Liew is an independent SQL Server Consultant in Sydney, Australia. He is a Microsoft Certified Master for SQL Server 2008 and holds a Master’s Degree in Distributed Computing.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips