Performance Troubleshooting after upgrade

[former_SQLA_member1694903]

Over the long weekend, I upgraded our database from SQL anywhere 10 to SQL/A 16. It is hosted on WIndows 2008/R2, hosted on VMWare. I did the upgrade 'properly' in that I did a db unload/load to create the .db file from scratch. In the test environment, everything seemed to work just fine. In production and under load, everything is working, but the server instance is using huge amounts of CPU. I'm looking for hints to figure out where the problem is. What I've done so far:

• Configured the Windows OS for high performance power settings
• Checked memory -- the VM is configured for 12GB, the dbSrv16 claims to be using appx 9GB for caching.
• According to Windows PerfMon, disk wait time is low
• CPU load is spread evenly across both configured virtual cores.
• There doesn't seem to be any significant bottlenecks on the network. the virtual machine is on a SAN, the same one as the pre-upgrade environment.
• The server console is not displaying any errors.

Start-up parameters are:

-x tcpip -c 3g -ch 10g -ti 120 -tl 600 -o C:\\DATABASE\\LOGS\\Sybase.TXT -os 2m -dt d:\\temp\\Sybase -n DBSERVER2 D:\\DATABASE\\MYDATABASE.db -n DBNAME2

Any insight appreciated.

[Former Member]

Are users complaining about performance issues (or have any benchmarks degraded)? It's possible that the new server is simply more efficient for your workload. (i.e. before a task took 10 seconds at 50% CPU and now we take 5 seconds at 100% CPU). If there server is not at 100% and there is work to do, we are wasting cycles!

Do you know if the increased CPU usage is distributed evenly across all connections? You should be able to check this by launching the dbconsole application and connecting to the database. Add the "approximatecputime(per second)" property to the connection viewer and see if the problem can be isolated to a single connection or set of connections.

The statistics format changed between these versions, so statistics from the old database will not have carried through on the unload/reload. Bad statistics could result in sub-optimal plans that require more CPU usage. It might be worth a try to recreate these manually using CREATE STATISTICS.

The sum of the CPU time used across all cores may go up when moving from 10 to 16 because we are more likely to utilize parallel access plans. For example: If you have 4 CPUs and we were limited to using 1 on version 10, this will show up as 25% CPU usage. But in version 16, we are able to split this task 4 ways for a speed increase, this would show up as 100% CPU usage. Has the Usage% on the most heavily utilized CPU (independent of the others) gone up across versions?

Did you switch machines or VMs as part of your upgrade?

[former_SQLA_member1694903]

In and of itself, high CPU use doesn't scare me, but users are definitely complaining about performance. Operations that took a few seconds under the old server are now taking many seconds to a few minutes. I will see what I can find out regarding the CPU time on connections, though what people are doing is pretty much the same as pre-upgrade.

I'll try regenerating the statistics. I'm also making an off-line DBBACKUP and will run a validation against that. The CPUs/Cores thing is a bit fuzzy. I licensed by CPU, and I interpreted the license to mean 1 virtual CPU with 2 cores would be OK, and assigned 1 CPU license with DBLIC. But when the server starts, it claimed it can only use one core. I used DBLIC to tell it two CPUs (I am licensed for 2), and now it uses 2 vCores on 1 vCPU. Utilization is spread evenly across them.

I'm also seeing warnings from the Monitor that the "unscheduled request" threshold regularly exceeds 5. Cache size (again according to monitor) is appx 8G out of 12G available, and is fairly stable.

We did switch VM's, going from Win2003R2/64 to a new WIn2008R2/64. They are hosted on the same hardware, same SAN, same network, etc.

[Former Member]

That unscheduled request warning is definitely a sign the server can't keep up with the demand. What does SELECT PROPERTY('UnschReq') return during periods of high usage?

If the problem is quite apparent when running queries, would it be possible to gather and post and execution plan with "Detailed and node statistics"? Even if it isn't a specific query issue, we can observe the accuracy of the estimates for a commonly used table. If you still have the version 10 server available, comparing against this may be helpful as well.

Any patterns for the queries that have regressed the most (queries containing a specific table/UDF)? You mentions that you are using 2 vCores, is this the same setup you were using on 10? Have you upgraded to the lasted EBF for 16? There are known issues with parallel execution in version prior to 16.0.0.1823.

[former_SQLA_member1694903]

I'm hoping that I don't jinx it saying this, but we think we found the problem. I set 'RememberLastStatement', and looked closely at the individual commands (sa_conn_activity). I found several tables that are being searched, directly or in a join, that did not have indexes on the fields being searched. I created indexes as needed, and now response is much better.

I checked the 'reload.sql' that was used to load the v10 data into v16, and the statements to index the fields are not there. I still have the v10 database file available, but I don't have the ambition to re-install the server to see if the indexes were there. I kinda suspect not. Interesting difference in behavior for un-indexed data between the two server versions, though.

We are on 16.0.0.1915

[Former Member]

You may be able to start that v10 database on a v16 server if you want to compare schemas. Just make sure you specify a different server and/or database name to ensure the application(s) don't accidentally connect to the wrong database.

[Former Member]

In regards to the CPU/Core license aspect ... Please run the command "dblic dbsrv16.lic" (called from within Bin32/Bin64 directory and check the output. If the output contains the line "Licensed cores: <number>", than you have a Core-License key. Check your order for the product, if ordered license does not match the license type of the license/registry-key open an incident in the SAP Service Marketplace under the component XX-SER-LIKEY .

Kind regards,

Dietrich

[former_SQLA_member1694903]

I raised an incident on the portal. I've also sent a message to my reseller to confirm that there was no mis-communication somewhere along the way.

[former_SQLA_member1694903]

I opened an incident on this, and was told that a bad batch of SA16 keys had been generated, that were "per core" license. I've been advised to regenerate the keys, but that is apparently not so easily done. Waiting on a reply to my incident now about that.