|
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.
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:
VM 2:
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?
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 |
|
The problem where the statistics on some tables. After dropping them the queries where running fast again. However this leaves open questions:
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 :)
(15 Apr '14, 10:48)
Breck Carter
Replies hidden
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.
(15 Apr '14, 13:52)
Arthur Hefti
> 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.
(15 Apr '14, 14:02)
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 :)
One thing I'm not quite clear on from your question - are both VMs using comparable / similar disk systems?
I don't think so, VM 2 uses a SSD in a Notebook. I assume VM 1 uses a SAN.
Is it too late to change my bet? Probability that VM 1 is feeble: fifty-fifty :)
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.
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.
I did this and couldn't find any real differences between the 2 installations, except the CPU usage on VM1 was much higher.
When analyzing VM 1 with Foxhound there were almost no disk reads as most of the data used was in the cache.