Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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).

share|improve this question
    
You'd need to look at the query plans for good vs. bad. Without any other information, I would try updating statistics and see if that helps. –  Jon Seigel Sep 14 '13 at 15:45
    
Query plan is identical and statistics have been updated. Does not fix the problem. –  CoderBrien Sep 14 '13 at 17:17
    
They're identical? Really? Did you run a text diff on the execution plan XML to make sure? –  Jon Seigel Sep 14 '13 at 17:44
    
I'm running from a clean VM snapshot in 2 different hardware configurations. I would expect them to be the same. –  CoderBrien Sep 14 '13 at 17:45
    
That's interesting, because I expect them to be different. Could you please actually go and compare the execution plans? –  Jon Seigel Sep 14 '13 at 17:47

2 Answers 2

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

share|improve this answer
    
Thanks, but it's not a select statement. It's a loop that includes cursors that call stored procedures using exec(). The stored procedures do insert/merge/updates. Seems like the SQL scheduler is not liking this and I don't know how to make it less angry. –  CoderBrien Sep 14 '13 at 1:52

Have you tried analysing the performance in a non-virtualised environment? You will often see processes that are not naturally easy to parallelise (especially those that at slightly paralisable) get bounced between CPU cores (you often see this exibited as a single process on an otherwise fairly quiet system consuming ~50% of two cores instead of ~100% of one) as the OS and apps assume that such context switching is relatively cost free. In a virtualised environment this context switching could be much more expensive as the VMM needs to move your VM's processes' state around the real cores and clearing (and storing where apprpiate) other VM's state - this extra switching overhead could be significant in itself but may also result in much less good use of the CPU's cache as an expensive secondary affect. Also if your processes are using multiple cores it may be that at each time-slice the VM gets it needs to wait for all the needed cores to be free from tasks from elsewhere, so things running in parallel really are in parallel (or as close to as possible) to reduce the risk of the virtualised environment introducing novel timing issues, where in the single vCPU case any wait latency is just until one core is free.

Sometimes the VMM can be bright and see that this is happening, and try keep one VM's processes locked to particular cores to improve cache use, but this requires specific knowledge of the cache arrangements of the system (is L2 shared between all cores, or is there separate L2 caches per pair of cores, and so on) - though under significant load from other VMs it would be careful about trying this as it could affect other VM's access to vCPUs and the VMM's priority is overall performance not the performance of a single VM.

lt;dr: it may simply be the way the vCPUs are being managed, and not something that SQL Server can control (or you can control via SQL Server). You will most likely find that for loads with high concurrency (have you tried testing the timings for this process running with many other operations or concurrently with other instances of itself if locking needs allow this?) the extra vCPUs are useful, but if most of your work is low concurrency you are better off without - in which case you'll need to do some more extensive benchmarking to see if the trade-off is worth it to your application(s) overall.

Still lt: This one process may be an oddity, an edge case where how MSSQL handles multiple cores and how the VMM manages the physical cores conflict to produce inefficiency: benchamrk more widely to see if adding vCPUs is worth it for your application.

share|improve this answer
    
Thanks for the insight David. The test is being run on an idle system. With HT off I've got 4 physical cores and 2 assigned to the VM I still see this behavior. I will test outside the VM and see if there is a change due to VM overhead (context switching or CPU bouncing). I do think this is a pathological case due to running a large amount short running statements. However, I'm surprised SQL server cannot handle this efficiently given the push towards mulit-core and virtualization. At worst I would accept a marginal slowdown of around 10% when going from 1 core to 2+ cores –  CoderBrien Sep 14 '13 at 12:08
    
Many dynamic SQL runs via cursor is hardly a generally recommend case though (I doubt I'd agree with "can't be optimised" unless you are meaning it is provided by an external party and you aren't allowed to try!) so it may not be a use case that the MSSQL dev team have optimised anything for, and it could be that some optimisation that is there works counter under a VMM. The single/multi core difference is only doubling (~30%->~60%) when you move of bare metal which is more than I'd expect but not astronomical. –  David Spillett Sep 15 '13 at 8:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.