October 10, 2006 at 5:35 am
I had created usage based aggregations on Analysis server 2000 cubes. But this does not improve the performance. Would you please let me know key points to consider while creating usage based aggregations?
October 12, 2006 at 8:46 am
When you created your usage based aggregations, did you use specific criteria (date rage of cube use, specific users, execution frequency, storage mode, or response time, specific queries) ?
Will the queries that exibit poor performance be included within the criteria you used to create the aggregations?
Was your Query log enabled while you executed some poorly performing queries, prior to creating the usage based aggregations?
Did you optimize for real time, scheduled, or somewhere inbetween (ROLAP vs MOLAP)?
What "percentage gain reaches" level did you use?
Can you provide any more details?
-Mark
October 13, 2006 at 12:25 am
Hi Mark,
Steps I followed to check performance improvement:
1. Execute MDX query and record time to execute.
2. Create 10000 entries in QueryLog table with dataset used in previous query and duration = 90 sec (bcoz in QueryLog table I always get Duration=0).
3. Create usage based aggregations using AS 2000.
4. AS creates corresponding aggregations.
5. Process the cube.
6. Again execute same MDX query and record time to execute.
Both time it takes same time to execute.
Steps to record time for MDX query execution:
1. Start_Time = Get system time
2. Execute MDX query: ADOMD_Cellset_Object.Open(MDXQuery,ADODB_Connection_Object)
3. End Time = Get system time
4. Duration = End Time - Start Time
October 13, 2006 at 9:11 am
If you have 10,000 entries within the QueryLog table; then could you compare the differences between the StartTime column between one execution and the next to determine the milliseconds between executions(And thus the elapsed milliseconds of the queries)?
Is your QueryLog table still in the default Access database, or have you moved it to SQL Server?
If it's not in SQL Server yet then here is an excellent article that might help:
http://www.sqlservercentral.com/columnists/rbalukonis/analysisaboutanalysisservices.asp
October 17, 2006 at 12:58 am
Hi Mark,
When I calculate time to execute MDX query using steps I mentioned in my previous message, the query takes 12 minutes to execute.
Using perfmon I observed that query is taking 2.43 msec to execute. I am wondering if query execution is taking 2.43 msec to execute then why I am getting 12 minutes. What other tasks are performed apart from actual query execution?
October 17, 2006 at 8:28 am
Can you run your test as the only active user of the server? If so, try running sp_mointor before and after your test.
That could then perhaps point you in the direction of the problem since it will report the CPU, Disk, and Network activity over the elapsed interval.
Since you are seeing only 2.43 msec for the execution then there must be something else contributing to the 12 minute execution times.
Does anyone else have any ideas?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply