I often came across stuations where end user complained that the queries are consuming more CPU seconds than usual. I would like to understand what is the correlation between CPU cycles and query performance.Can anyone explain this?
What is th optimal amount of CPU that can be used by a query?
#1. The end user probably meant the "Response time" is longer than usual.
The usual causes for a frequently sumbitted SQL to take longer than usual to respond are
(a). The user's session may have been blocked or delayed. (b). The system may have been busier than usual. (c). The volume of data the SQL deals with may have increased.
You may want to ask the user how he/ she determined the CPU seconds consumed is higher.
#2. If the user has actually determined the CPU consumed is higher than usual (via DBC.AmpUsage or DBQL), the cause is probably a poor execution plan triggered by stale statistics or lack of statistics on certain columns.
A resource intensive query can be identified by working out the ratio between CPU used and IO performed by the query. Such queries satisfy the following expression.
(CPU used in milliseconds)/IO performed > 100
#3. The end user can help the optimizer choose the most efficient plan by making sure that statistics are collected on all the required columns (join columns, indexes, etc).
He/She must also EXPLAIN the query to see if the row estimates are made with good confidence.
I have a similar problem with some of our tactical queries, that I suspect is caused by different sessions (using the same logonid) blocking eachother... But I have a problem documenting this, as "blocktime" unfortunately isn't a column in DBQL... I'm trying the sql below to show me the 10 longest blocktimes for the tactical application, but I'm not 100% sure that it gives me what I want... Anyone had any success resolving this kind of problem?
sel a.queryid, a.starttime, ((a.FirstRespTime - FirstStepTime) second) AS Blocktime, ((a.LastRespTime - FirstStepTime) second) AS Responsetime, TotalCPUTime from dbc.dbqlogtbl a where a.appid = 'Kunde' qualify rank() over(order by blocktime desc) <= 10;