August 13, 2004 at 3:20 am
I am attempting to performance tune our olap cubes using the Usage Based Optimization Wizard. When I go thru the wizard and select criteria "Queries by these users:" and set to a particular user, I do not see the expected duration times. This user has complained of certain queries taking 10 minutes or more, yet in this Wizard it only shows a few queries, and all of them have a duration of 0:00:00.
Why is it not picking up these long queries? Does something have to be enabled (to gather all statisitcs?) for the Usage Based Optimization to work?
THANKS
August 13, 2004 at 7:28 am
In Analysis Manager, right click your server and ensure you've got query logging turned on. You can decrease the number to record more queries (default is 1 in 10 queries). Note that this records the partition used to answer the query, so if you go looking at the log, don't expect to see much more than the details of this. If you are using partitions, make sure you have slices set for these also.
Steve.
August 13, 2004 at 7:41 am
Query loggin IS enabled. Write to log once per "1" queries.
No partitions. Yet.
Anything else?
August 13, 2004 at 9:03 am
I would check the query next (ie get the mdx and test it in the sample app). Is the query using extensive calculated members, or anything else likely to draw execution to the client rather than being performed on the server? If you've still got a copy of vb6 around you could doctor the sample app src code and put a timer in to determine the runtime of the query versus the display of the results. You'll get a fair indication of this also if you're using say Excel as the client, and the server shows only slight activity as the query is received but then the client machine churns. I also like to keep the network connection icon in my taskbar tray so i can watch for network traffic. If its a query returning many members, you could see heavy network activity on the client (and server too i guess).
If it is the query, there are ways to move the processing back to the server, but you may want to review the query to see if there is an alternate way to return the same information but with a different query.
Steve.
August 17, 2004 at 2:08 am
Ok, first maybe I need clarification on what is actually being timed in Analysis Services. I presumed it was then the result set was returned to the client. Correct me if I'm wrong in this sequence:
1) User initiates query with Excel (2000 or XP) pivottable service
2) AS receives request (log time starts) and sends query to SQL for un-aggregated data
3) SQL returns unaggregated data to AS
4) AS aggregates the SQL data
5) AS calculates any calculated members
6) AS sends result set back to Excel (log time ends)
But it sounds like from your explanation, that the calculated members are being done in the pivottable service. Is this correct?
We are using calculated members, but the 0 second queries times are constant across the board for nearly all logged queries. Also, if I run the Usage Analysis report for a particular user (our project manager), she does not even appear in the report. And she is the one reporting the long query times.
Thanks for the help!
August 17, 2004 at 6:47 am
I will try to find it again but there is an article/whitepaper/preso from MSft (i think) that details when/why calculated members resolve on the client rather than the server.
Getting to your theory on data retrieval, what storage mode are you using for the cube? If you're using MOLAP, when you process the cube, this is when the data gets stored in the partition/s of the cube. So when executing a query, there is no query made against the relational system, only the molap data store, so what's being recorded in this case is the time it takes to locate the data in the partition.
Have you tried viewing the processor usage on the client (excel) machine when running the queries that perform poorly? Alternatively have you captured the mdx and run it within the client sample app?
What sort of size is your cube, and have you created aggregations for it? Also, are there any large dimensions being returned in the query/s?
Steve.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply