Longest Query Response Time Since Startup (msec) - Alert summary of BI report

[steven_alb]

Hi SAP, 

We run the BISupportTool to check our BOBJ production system. In the report, we noticed an alert - Longest Query Response Time Since Startup (msec). 

SOMBOBPRDCI.CentralManagementServer

The Longest Query Response Time Since Startup metric provides the longest running query response time in milliseconds since the CMS has been started. A query time of more than 300 seconds could indicate a bottleneck in the Central Management Server process, the CMS database, or the host Operating System. This metric may also indicate a custom application or user query which is not optimized for performance.

For more information, refer to KB Article 1982576.

We want to clearly the meaning of the "query". Does it also include user query/report, like analysis office workbook or webi report? 

357771ms = 375s = 6min is quite common in our system. Most queries with long execution time are scheduled in bobj jobs. Is the threshold value for query execution time of 300 second too low? Or my understanding regarding this is incorrect.

And, as a solution, should not we need to optimize the model in the backend system instead of CMC? 

If it is a standard query, how can we find it in the system? And how can we fine tune the CMS process? (It is hard to tune the DB and OS.)

Thanks

[bernhard_keimel]

As already stated, that metric has NOTHING to do with Webi Reports. It is how fast the CMS can work in the background. The longer the CMS needs to query itself, the slower the platform processes and user experience gets.

However, this is only a reason to panick when it is an AVERAGE value. I observed in the platforms i manage that sometimes the CMS database may respond slow for reasons to be found in the database. Mostly, it was just a short period of time when that happened and the metric "LONGEST ..." indicates that it might be an exceptional occurence. If the "AVERAGE ..." metrics increases, then you really should consult the database administrators of the CMS database (the one you configured when you have installed BO) to check the queries and assure that it can run to best performance. As this is ONLY database dependend, you cannot fix it on BO side except keeping the number of instances in schedules low and consequently deleting objects you do no longer need.

To track the historical development of those metrics, use the BI Admin Studio and assure you have the monitoring database properly configured and that the metrics are written to the database. then you can check how it behaves over time to get a better picture if an immediate action is appropriate or not.

[LepreuxLoic]

Hi,

CMS queries are used to get details about objects in BOE landscape (Users, Folders, Documents, Universes ...)
They are used for instance to get content when navigating in BILaunchpad and CMC, but it's separate from database query executed by products like Analysis Office or WebIntelligence.
These queries are supposed to be quite fast since it is only fetching metadata, and they mostly retrieve small amount of data.

You can add some parameters in CMS command line to get traces of long running queries.
This is explained in SAP Note https://launchpad.support.sap.com/#/notes/2743951
You could start by configuring a minimum duration of 10 seconds, to limit amount of these CMS query logs, and see how often it happens.

You may also want to look at increasing size of objects cache in CMS.(see KBA https://launchpad.support.sap.com/#/notes/1981894)

Regards,
Loic