Moved SQL Anywhere 12 database from windows 2003 to windows 2012 server and performance is slow

[fvestjens]

We moved a SQL Anywhere 12 database from a dedicated windows 2003 SP2 server to a new virtual machine running windows 2012 R2 SP1 server.

Windows 2003 server specs: 6134 MB memory 8 cores SQL Anywhere 12.0.1 build 3910

Windows 2012 server specs: 16384 MB memory 8 cores SQL Anywhere 12.0.1 build 4403

From another database we use proxy tables to this database to fill the database. On the windows 2003 server this takes 2338 seconds On the new windows 2012 server this takes 3867 seconds

I would expect that it would run faster on the new machine or at least at the same speed.

It must be something in the setup of the windows 2012 server. Does anyone have a clue where I should look?

TIA,

Frank

[thomas_duemesnil]

Did you change the storage for the DB ? The Disk performance in VM's is normally not as fast as direct access on a dedicated server.

[fvestjens]

No, I just moved the database file.

What should I change?

[Vlad]

As a prompt check, I'd suggest to try any RAM Drive to improve I/O (if your DB file size is less than ~12 GB). Plus you can measure the HDD speed with CrystalDiskMark and compare the result.

Usually Breck says that the performance analysis should be done from the beginning, e.g. how much data is transferred, what is the execution plan, how much time does SA spend to go through the records, what is about the cache size etc.

Another thing is to compare the network speed (latency + bandwidth). E.g. I use LAN Speed Test (Lite) or iPerf.

[thomas_duemesnil]

That's what I mean. The dedicated server had probably local attached storage that he could use exclusively. Now in a VM setup the VM has to share the access to the physical disks with different VM's running on the same Host. And the Disk in your VM is probaly a file stored in a network share. This will all limit the speed of SQLA.

[Former Member]

I support Vlad's suggestion to check the network performance. If you don't have a system tool, you may simply retrieve all rows from the proxy tables (or some or one of them) e.g. by querying them in dbisql and outputting the result to a file (or the nul: device). It also may be interesting to connect directly to the back-end with dbisql (using ODBC if it's not SQL Anywhere or IQ) and measure the performance when querying the remote table directly (again with OUTPUT), which for reasons I fail to understand I've occasionally observed to be faster than the transport from remote server to SQL Anywhere server via proxy table.

HTH

Volker Stöffler DB-TecKnowledgy

[former_SQLA_member1694993]

What is your license ? If you have a chip license and configured the vm with cpu instead cores than your affinity ist on only one cpu. Go to taskmanager -> rightklick on dbsrv12.exe and select Set affinity. The you see on how many cores the db is running.