frederico_fonseca (4/27/2016)
I see to main issues here1 - VM setup seems to indicate an issue if performance of HD's is that bad compared to the physical.
What is the VM type? Hiper-V or VMWare? getting all hardware/config for this would eventually allow some here to help on it. And are there other VM's apart from the SQL cluster on that physical?
2 - number of recompiles - this would seem to indicate a few possibilities
- something different on both sql server instances (compare sys.configurations and startup options)
- memory issues (can be derived from VM setup)
- some issue with CU3 - this will be harder to tell
but... you say you replaced table variables with temp tables - has this change been done in both the VM and the physical server? or is the VM running with temp tables and the physical with table variables? using temp table will cause recompiles.
It's VMWare. The VMs were ordered from a hosting company, so there are probably other VMs. I asked if the CPU and Memory resources are dedicated for the cluster and the answer was Yes.
I've replaced the table variables with temp tables on the Cluster environment and improved the execution on Server 1. It's done only for one stored procedure. The same change on the Server 2 (stand alone) didn't bring any improvements. The code in all objects is identical.
Igor Micev,My blog: www.igormicev.com