Hi,

I have a Windows Server 2003 (SP1) with SQL2000 (SP4) and Analysis
Services 2000 (SP4) running to provide datawarehousing with OLAP. In
order to meet the performance requirements of the users i tried to run
the usage-based optimization wizard. While doing this, I figured out
that AS it not logging the duration for the queries in the QueryLog. If
I have a closer look at the table, I see all queries successfully
logged, but the Duration column equals 0 (zero) for all rows. Anybody
knows what could cause this problem and how to resolve it?

Logging is enabled on the server with a threshold of 10.

Thank you in advance!

Dieter

Hello, DiGa,

The duration column is in seconds, not in miliseconds, so it may be some
rounding. Also please consider that calculated memebrs are calculating in
client side. So, if you think that query takes 10 seconds, all 10 seconds
could be spent in client side + network transfer, not for query. So, check
the network workload also - if you use excel - this tool transfers megabytes
of data via network.
Additionally, please check the analysis services locks in performance
monitor. I'm not sure, but it seems, that the duration is starting to
calculate when the query is not in the latch.

Try set logging every query to see more details in querylog.

Ramunas Balukonis


"DiGa" <dgasser (AT) ch (DOT) oetiker.com> wrote

Hi Ramunas

Thank you for your reply. I see the point you were mentioning about
calculated members, client side processing and network traffic.
Nevertheless I'm sure that it is not a rounding problem because all
rows in the querylog (75000 rows) show up 0 in the duration column in
the database.

I just cleared the log, configured AS to log after every query, and ran
a rahter complex query against AS using Microsoft Office Web
Components. The query resulted in about 900 rows added to the QueryLog,
all with the same starttime, user, dataset etc. All rows show 0 (zero)
in the duration column. The query took about 12 seconds until the
result was displayed to the client.

At the same time, i set up perfmon to monitor Analysis Server Locks. It
resulted in 1 Lock Request, 1 Lock Grant and 1 Unlock Request for the
duration of the query. The other meters were 0... :-(

Any more clues are greatly appreciated.

DiGa


Ramunas Balukonis wrote:

Hi DiGa,
Using perfcounters see the counter Current queries under Analysis Server:
Query with sampling 1 second. Do you see any queries running more than 1
seconds?
Another point from my expirience when working with Excel: Excel generates
much more queries that you expect, especially when drilling . So, instaed of
1 query running 10 seconds, excel could show 100 queries running for 100-200
ms, so "0" seconds. So, you can known this looking at the column dataset.
How to resolve dataset column you can read in
http://www.sqlservercentral.com/colu...dimensions.asp .
I thing the best way to test the behaviour of query log is to create advaced
mdx and tun it from mdx sample application.

Ramunas


"DiGa" <dgasser (AT) ch (DOT) oetiker.com> wrote