We are trying to implement usage-based optimization with our AS2K
database. For some reason, the query duration appears equal to zero in
the optimization wizard and querylog. The other fields are populated
correctly. In some cases, the queries run for several minutes. Does
anyone know why this would occur?

Hi Jeremy,

The query log only tells you part of the story about query performance, and sometimes it's pretty confusing too...!

The first thing to say is that the query log probably is telling you the correct query duration, but you need to understand that one query run by a user does not necessarily equate to one entry in the query log. In fact, a single user query can be split up into hundreds or even thousands of sub-queries on the server and it's these subqueries that are logged; what might be happening on your server is that a single sub-query could be running in a fraction of a second but obviously hundreds of such queries take a lot longer. Aggregations are still very useful though - if your average subquery now returns in 0.2 seconds instead of 0.4 seconds, it will make a big impact overall.

Secondly, poor query performance could also be due to a number of other things. The most likely culprit is calculated members; poor network latency and bandwidth and poor cube design can also have an influence.

HTH,

Chris




----- Jeremy Highsmith wrote: -----

We are trying to implement usage-based optimization with our AS2K
database. For some reason, the query duration appears equal to zero in
the optimization wizard and querylog. The other fields are populated
correctly. In some cases, the queries run for several minutes. Does
anyone know why this would occur?

Same problem here. Query runs for more than a minute, but the Usage
Analysis Wizard reports 00:00. Same for the performance monitor
counter "Last Query\Time (ms)".
Logging is enabled, write to log once per 1 query.
Restarting the service didn't help.

Any ideas ?

Many thanks,
Tom



jhighsmith4 (AT) hotmail (DOT) com (Jeremy Highsmith) wrote in message news:<e34074f4.0405040755.21b75080 (AT) posting (DOT) google.com>...