Huge Performance Difference between 2 VMs

[ArthurHCH]

Hi when I run a quite simple query (select a couple of columns, 3 simple sub selects, 1 with max) I have very different behavior on different VMs.

• SQL Anyhwere 12.0.1
• Same Startup Parameter for DB Server -nDBNAME -ec simple -xtcpip -gd all -c5G -gp 4096 "D:\\DBFile.db"
• Database contains 2 DB Spaces (4 und 2 GB)
• VM has 2 Processors and 8 GB RAM

1st VM is a server running on an ESX and I do the queries from a workstation 2nd VM is a Windows 8. VM running on a Mac with an i7 processor and VMWare fusion. The database file is on a TrueCrypt encrypted disk. The database is take from a backup (DBBackup) from database 1.

VM 1:

• I do a query and 1 CPU goes up to 100% and stays there for about 30 seconds
• I do a 2nd query and 2nd CPU goes up to 100% and stays there for about 30 seconds
• -> So the machine is at about 99% CPU and is very slow on further requests
• Using the plan viewer and getting "Detailed and node statistics" takes about the same time

VM 2:

• Same queries but they take about 5 seconds of CPU incl. "Detailed and node Statistics"

The result set is about 30'000 rows (about 5MB) and of course sending them over the network with VM 1 takes some more time than doing this locally. However will CPU load be 100% for feeding the rows to the client?

The question is where to start looking into this issue?

• Will -ec simple have an impact as all communication has to be encrypted? Should not matter when getting the plan.
• Will backing up and take a copy of the database change the statistics or cache?

EDIT: The problem started when we deployed a minor version of the application. Not really big changes, updated Stored Procedures (they where not running during the performance issues) and a couple of lookup tables with an additional column. No database applied.

Any input appreciated Arthur

[Breck_Carter]

Probability that the VM 1 setup is not optimal: 90%.

Probability that VM 1 is feeble: 9%

Probability that it's SQL Anywhere's fault: 1%

If it was me, [advertising alert] I'd open up two Foxhound Monitor windows side-by-side and try to see where SQL Anywhere is starving (besides the CPU usage) which might give a clue where to look in the VM 1 setup.

Assuming, of course, that you don't have a VM 1 Support Person on site that you can yell at 🙂

[justin_willey]

One thing I'm not quite clear on from your question - are both VMs using comparable / similar disk systems?

[ArthurHCH]

I don't think so, VM 2 uses a SSD in a Notebook. I assume VM 1 uses a SAN.

[Breck_Carter]

Is it too late to change my bet? Probability that VM 1 is feeble: fifty-fifty 🙂

[justin_willey]

OK - so the $64,000 question is: how is the SAN physically connected to the VM1 server - is it a) (good) Fiber Channel or similar or b) (not good) iSCSI

Our experience has been that performance of servers linked to SANs by iSCSI is awful - never mind how good the SAN itself is. I think the problem is related to latency rather than actual data throughput. People tell me that you can get good performance with iSCSI but I've never seen it. With Fibre Channel we have seen excellent results.

[Former Member]

Shot in the dark: First try removing -ec simple to eliminate encryption as a possible factor. Then, if you still see a large discrepancy, reduce the cache (-c) or set the bottom with a -cl 1g. I have seen large caches actually slow performance--possibly because of unhelpful disk swapping.

[ArthurHCH]

I did this and couldn't find any real differences between the 2 installations, except the CPU usage on VM1 was much higher.

[ArthurHCH]

When analyzing VM 1 with Foxhound there were almost no disk reads as most of the data used was in the cache.

[ArthurHCH]

The problem where the statistics on some tables. After dropping them the queries where running fast again. However this leaves open questions:

• Why wasn't the problem reproducible on the 2nd VM with a backup of the database? Does the backup drop statistics or are they recalculated on starting on a new server? We used to be able to reproduce statistics problems on other machines
• Analyzing a plan in all details gave about the same numbers on VM 1 and VM 2. VM 1 however took much longer to find the solution
• Running a query in "old" isqlc showed the usage of the same indexes on both VMs

[Breck_Carter]

When you say "backup" do you mean dbbackup or the equivalent?

Assuming "yes" then everything's going to be copied as-is... which may be a problem if the target computer has different hardware. After all, there's a statement and two sa_functions devoted to "recalibrating the cost model" (following that link is akin to taking the red pill 🙂

[ArthurHCH]

Backup is done by dbbackup and the backup copies used on VM 2 without any changes. Interesting is that performance gets better on different hardware.

[Breck_Carter]

> Interesting is that performance gets better on different hardware.

Why should that come as a surprise? It's one of the main reasons folks buy different hardware.