I'm running SQL Server 2012 in Windows 2008R2 in a virtualized environment. I've observed the following under both VMware Workstation 9 and Hyper-V 2012R1 and I don't know how to address it.
I've got a batch that takes around 5 minutes to run when there is a single CPU in the virtual machine. Bumping up anywhere from 2-8 causes it to take over 10 minutes to run. Watching the Task Manager I see that there is not much if any parallel execution and lots of context switching. If I limit sqlservr.exe to a single CPU by setting the processor affinity in Task Manager the time drops back down to 5 minutes.
The particular batch that I'm running is makes heavy use of cursors and dynamic sql which cannot be eliminated.
The query has been profiled and optimized. Statistics are all up to date and indexes are rebuilt.
Is there anything I can do to SQL Server to get better behavior? This seems not right. I would like to add additional CPU resources to the VM so that they can be used if necessary without a drastic performance hit for serialized processing.
CPU is i7-4770K with VT-x enabled both with and without hyperthreading enabled.
EDIT: Some more stats-
Running from the root partition in Hyper-V with 1 CPU takes 4:41 and with 8 CPUS takes 7:30.
Running without virtualization 1 CPU takes 4:11 and with 8 CPUS takes 5:23.
EDIT2: On the recommendation of Jon Siegel I have gotten Query Plan traces for both cases. The query plans are long (500k lines) due to the loops and curors. There are no obvious differences between the two and I don't see any signs of parallelism in use.
Additionally, setting MAXDOP=1 at the server level has the same effect as the affinity setting in that it limits to 1 CPU and makes the batch go fast.
Jon also made me aware of the Resource Governor feature of SQL 2012 EE which should allow the ability to limit CPUs on a per connection basis as a workaround. The relevant setting is the AFFINITY SCHEDULER option for the resource pool.
So, at this point to sum it up mutlicore:loop*cursor*exec() = MS-SQL-no-likey (and virtualization is not helping the matter).
Best Answer
I can only guess about the query because I can't see it or the data. Perhaps it simply doesn't lend itself to parallel execution. In that case, you can use a query hint OPTION ( MAXDOP 1 ) (after the ORDER BY clause) to force the query to execute on only one core.
http://technet.microsoft.com/en-us/library/ms181714.aspx