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

asked 10 Apr '14, 11:53

Arthur%20Hefti's gravatar image

Arthur Hefti
1668816
accept rate: 0%

edited 10 Apr '14, 15:09

1

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 :)

(10 Apr '14, 14:11) Breck Carter
Replies hidden
1

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

(10 Apr '14, 15:04) Justin Willey
Replies hidden

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

(10 Apr '14, 15:07) Arthur Hefti
Replies hidden
1

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

(10 Apr '14, 15:08) Breck Carter

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.

(10 Apr '14, 15:18) Justin Willey

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.

(11 Apr '14, 10:16) dejstone

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

(15 Apr '14, 05:17) Arthur Hefti

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

(15 Apr '14, 05:18) Arthur Hefti
showing 4 of 8 show all flat view

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
permanent link

answered 15 Apr '14, 05:21

Arthur%20Hefti's gravatar image

Arthur Hefti
1668816
accept rate: 0%

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×239

question asked: 10 Apr '14, 11:53

question was seen: 602 times

last updated: 15 Apr '14, 14:02