February 7, 2012 at 7:13 am
I processed my cube against a backup DB (as restored from backup) & a replicated DB (as live replication from db in production).
The backup DB took only 3 minutes but the Replicated DB took 11+ hours. At the time of processing, there is no user activity on the live server and thus no replication going on this server. To make sure the hardware is not an issue, both processing were done on the same SQL server instance.
What else could affect the cube processing time on replicated server? Should the replication process be turned off in order to process the cube in a timely manner? Any suggestion would really be appreciated. I have been working on this for a couple days now to no avail.
TIA,
Jane
February 7, 2012 at 7:26 am
It very much depends upon data volume & cube algorithm. If multidimensional processing is complex, it will certainly take time.
There could be other reasons as well for example server configuration on database & SSAS.
February 7, 2012 at 8:09 am
Dev (2/7/2012)
It very much depends upon data volume & cube algorithm. If multidimensional processing is complex, it will certainly take time.There could be other reasons as well for example server configuration on database & SSAS.
The data volume is about 3GB. As for the configuration, they both run on the same server with same SQL & SSAS. I processed the dimensions & Cube separately; dimensiond first then cube. Dimensions processing time between those 2 dbs were close.
3 minutes vs 11+ hours is too big of a different for the cube processing. 🙁
February 7, 2012 at 8:15 am
Does the Backup DB & Replicated DB have same database schema (tables, indexes etc.) and same data volume? In simple words, are they identical in all aspects?
February 7, 2012 at 8:26 am
Yes. I used the backup of the replicated db that i was testing.
February 7, 2012 at 8:32 am
I am sorry I didn’t get you.
I am writing my understanding; please correct me if I am wrong. You have one source (base) database and two copies of it. You created first copy with backup / restore & second with replication. Is this correct?
I have a serious doubt that you are missing on indexes and want you to crosscheck it once.
February 7, 2012 at 8:56 am
Like you said, there are 3 different copy of DBs.
1- the live DB in production;
2- DB with replication setup based on the live DB mentioned above (1); and
3- a backup/restore of the live db as mentioned in (1). (sorry for the error in the previous post)
For olap, I was hoping to run it against the (2), but it's taking 11+ hours just for the cube processing part.
I restored the backup (3) onto the same server, same SQL & SSAS instances, where the replication reside, for trouble shooting purposes to rules out the configuration/hardware bottleneck.
I have always assumed that the replicated & backup DB have the same indexes as the production version which they're based on.
I will check the indexes and post the result back.
February 7, 2012 at 9:32 am
Hi
You may also want to investigate the queries that SSAS is making to your SQL Database during the Cube\Partitions processing.
This could be done by using 'traditional' T-SQL performance tuning tools e.g. SQL Profiler and Plan analysis. Try comapring the Execution plans between each of your databases to see if there are any deifferences.
Cheers
-Matt
February 8, 2012 at 7:24 am
The indexes are same as those in the live DB. Whatever indexes exist in the production, also exist in the replicated DB.
February 8, 2012 at 7:26 am
hi Matt,
Assuming the execution plans are different, is there anyway to "force" the process to use one or the other?
February 8, 2012 at 7:51 am
I'm not sure to be honest, table statistics might be a starting point, you'd possibly be looking at some in-depth T-SQL performance tuning techniques - which are probably best answered on the appropriate forum.
But I do think you need to investigate the queries that are being made to the relational database during the ProcessData phase. At least to be able to eliminate them from your investigations
When I'm looking at a new cube deployment, and tuning Processing Performance one of the things I look at is what tsql queries are being made to the relational database. This involves looking at the Executuion plan and looking at ways to improve their performance, this may include new indexes, updating or creating statistics or creating views.
It maybe helpful to look at the Microsoft White paper - Analysis Services Performance Guide
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply