By: Simon Liew | Updated: 2013-10-04 | Comments (3) | SQL Server Configurations
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?
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
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.
- 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
Last Updated: 2013-10-04
About the author
View all my tips