question

rki1966 avatar image
rki1966 asked

Why are queries slower in sql 2012 with Vmware than 2008 without Vmware

My queries are slower since we upgraded to SQL 2012 with vwmare. I have the same query but different environments. Here is the Parallelism for both servers Cost Threshold for Parallelism = 5 Max Degree of Parallelism = 0
sql 2012
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

what's different about the environments? We don't know anything about it other than one is virtual SQL 2012 and one is physical SQL 2008. RAM? CPUs? Power saving settings? Disk? Network? Statistics? Index fragmentation? VMware resource reservations? Limits? vCPUs? vRAM? Other workloads in the virtual host? What are the SQL wait stats on each server when executing the query? See more in this question where the physical environment was slower than the virtual one - http://ask.sqlservercentral.com/questions/114202/physical-vs-virtual-conundrum.html
0 Likes 0 ·

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
There are huge numbers of reasons why you could have differences. Your starting from two different versions of SQL Server. That alone could cause differences in execution plans due to changes in how the optimizer works between versions. Then you need to take into account that you're going from, assuming, dedicated hardware to a shared environment within VMWare. There are a large number of settings within the VM environment that can negatively impact SQL Server. I suggest doing a search on the VMWare web site. They have a document on how to configure VMWare optimally for SQL Server. You didn't even get into the differences in memory and CPU between the VM and the hardware. Even if they are the same, they could still be different because within a VM environment those may be, usually are, share resources. And, they can be affected by those settings within VMWare that I mentioned earlier. Parallelism is not the only way to adjust query behavior. But, one thing I would suggest, on all your servers, change the default value for the Cost Threshold from 5 to something much higher. I usually recommend 40 as a starting point.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.