We have an application written i PowerBuilder 12.5, which has a lot of code for doing calculation in the database. As of lately, one of our customers has noticed that when this particular code is running, dbsrv12.exe is using 90-95% CPU-power on all cores as the same time. However, when we run the same code against another database (same schema and EBF), dbsrv12.exe is using only 20-25% on 1-2 cores while the other cores is idle.
Is it possible to trace the queries and activity in the database to se if any indexes or something else is wrong?
I have also done the exact same on my laptop running dbeng12.exe, and the same thing is happening. On one database all 8 cores is experiencing 90-95% load while on the other database is utilizing only 2-3 cores at 20% while the rest is idle. So I can trace both database and source code if needed.
asked 13 Jan '16, 16:52
> when we run the same code against another database (same schema and EBF)
If you treat that statement as a hypothesis rather than a fact, you may find the problem is some minor, easily fixed, difference between the Evil Database and Good Database.
The key word is "easy"... most of Nick's excellent suggestions are quite difficult... if you had one single Evil Database that's what you have to do (work hard) but in this case you possess a Good Database so here's an alternative:
answered 15 Jan '16, 08:42
There are a number of Perfomance monitoring and diagnostic and debugging features available with 12.0.1 that can be used for this. Diagnostic Tracing or Request Logging are usually where one starts unless you have already narrowed this down to a specific procedure, or event or query; sometimes that can be identified from the application side(eg. PBTrace) ahead of doing anything on the database side.
If this can be reproduced, you do want to also test with the latest EBFs/SPs available to see if you are running into something that has already been fixed. When not using the latest fixes, you might want to verify if parallelism is a determining/contributing factor by disabling that to see if changing that setting changes the behavior any.
If/Once you can narrow this down to a specific query getting the graphic plans for that will often be where you need to focus to identify causes in the usage, data distribution and schema.