When the query optimizer calculates the cost of an execution plan, can you expect the plan to differ depending on the amount of resource available to it?
Take the following scenario:
- You have a VMware virtual machine running SQL Server 2008 R2 with 32GB RAM, 8 CPU cores hooked up to a SAN. There are 7 VMs sharing the host box.
- You find CPU usage is peaking at 130% regularly, so you move most of the VMs off the host, leaving only 3 of the original 7 VMs including the SQL Server box.
- You immediately find that actual performance is slower now that more CPU headroom is available to it. You can see that CPU utilization on the guest is going much higher (because more CPU resource is available for use) than the 30% it could muster before. Despite this, users of the application see a marked slowdown in performance.
My interpretation of why a SQL VM performs slower when more CPU resource is available to it is that the procedure cache contains plans calculated under/optimized for an environment with less CPU availability. When the extra resource becomes available, the procedure cache will provide sub-optimal plans which may perform less well.
Does this make sense? We had this exact scenario a couple of weeks back and had lots of complaints about performance as soon as we stopped the CPU thrashing on the host. I ran dbcc freeprocache and thereafter performance seemed to improve - either that or the users got used to it.