colin.Leversuch-Roberts (1/6/2011)
well it's a bit of a guess but I'd say the combination of VMware and 2 cores just doesn't provide enough workers.There's some things you could try: disable parallelism, enable fibres.
I'd actually suggest you try to test on a physical box and/or get more cpus.
The whole thing about performance is that it's rerely one golden bullit. For example if your transactions/batches can't finish quick enough then you'll get queuing/blocking etc. this could be that your transaction log drive can't write fast enough ( quite likely on VMware which I've found absolute rubbish under load ) what you might be seeing is the after effect rather than the cause - blocking will ramp up threads, serialisation of stored procedures ( serialised execution plans ) can cause queuing the same. You may not have enough memory, disks may be too slow .. really tricky to know for sure.
I know that this is a really old post at this point, but I happened upon it while doing some research and I have to say that the above information is absolute rubish, even at the time that this actually posted. SQL Server under VMware scales just fine, as far back as ESX 3.0 circa the year 2000 when things are done correctly. There are a number of markers in the data presented that would point to other issues that are non-VM related here that would have required further investigation to determine the root cause of the issue here.
To start off with, this is SQL Server 2005, do the CPUID value of 255 for each scheduler tells that the schedulers are affinity masked in SQL Server to specific CPUs in the system. The 2003 nature would make me assume that this is 32 bit so you have a max worker count of 255, which is purely stipulation for the data presented, but that would make the worker thread pool saturation make a lot more sense here.
I am not trying to stir up a debate about past information here, but I think there is too little information in this thread to determine that the VM aspect is overall a problem here, and it is unlikely that this could ever be substatiated at this point. The reason for this comment is simply that I don't want an incorrect assumption to be made based on the merits of this discussion on more modern and improved virtualization platforms, and it's important to take a note of the overall lake of substantiating information that would lead to applying credibility to the information above. A lot more information would be required to actually ascertain that VM overhead was an issue here.
It is incredibly unfortunate, but I've seen a lot of old threads, like this one, that have been referenced recently for why SQL Server doesn't scale under VM configurations, so I wanted to leave this note here based on that experience. At this point, I doubt seriously that this will ever be something that gets another look from the original poster since it is so old, but something for future readers to consider. Technology changes so rapidly that you have to gauge what was said in the past against current technology and understand.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]