Cascade Skyline - with Microsoft Logo and Project Support header - author Brian Smith

Slow OLAP Cube Builds and Large TempDB - Revisited

Slow OLAP Cube Builds and Large TempDB - Revisited

Rate This
  • Comments 12

After quite a bit of work on this one it appears that SP1 (and a slightly earlier hotfix) were almost coincidental in this problem rather than being the cause - and it can occur even without SP1 if you add enough dimensions to your cubes.  The best resolution is a combination of the first 2 or all 3 of the following items:

1.  Keep you statistics up to date!  In minor cases updating the statistics on your reporting database can help - but in all cases it is required to get the full benefit.  You should set up a maintenance plan or initial for testing you could use sp_updatestats on your reporting database.  See Christophe's recent blog for some guidance here - database maintenance is one of the keys to better queue performance.

2.  if you see a "No Join Predicate" warning in the execution plan of the large query causing the problem then apply SQL Server 2005 SP2 Cumulative Update 6 http://support.microsoft.com/kb/946608 .  The SQL bug is resolved by Hotfix 942444 which is in that roll up.  Please also read http://support.microsoft.com/kb/942444 carefully, particularly the details of setting Trace Flags 4101 and 4121 - which are required to be set to active this hotfix.  See the remarks section at http://msdn2.microsoft.com/en-us/library/ms188396.aspx for more details of trace flag setting.  Use the command line option -T4101 and -T4121, and set these in the SQL Server Configuration Manager for the instance of SQL Server 2005 used for your reporting database.

*** Update - SQL Server 2008 R2 - CU7 + setting the flag for -T4136 will get you back to good build times if you have upgraded and gone slow again - thanks to Flo ***

***Update 2 - I think this should be -T4199 for 2008 R2 to do the exact same - -T4136 is for parameter sniffing - perhaps that results in the good execution plan too ***

3.  If this still isn't giving you the speed of cube build you require then it might be time to consider upgrading your server hardware.  In our tests with very large cube builds you can improve the build times with more RAM - and also x64 hardware (which also allows you to support even more RAM).

If you are not sure about step 2 - then see my previous posting which will help you identify the bad query - and then paste this into a SQL Query window against you reporting database and click to see the estimated query plan.  In the execution plan - which will be quite large - you are looking for something like this:

image

And if you hover over the warning triangle you should see a warning of "No Join Predicate".  Also note the expense (74% of the cost of the query) attributed to the Table Spool.  This is where tempdb is getting heavily used.

As an example of potential improvements after following these steps (without any hardware upgrades) we have seen cube builds of 20+ hours complete in 45 minutes, and a 1 hour 15 minute build come down to less than 1 minute.  Your mileage may vary :).

You may still see quite heavy tempdb usage and this is to be expected with such a large query - and should not be a concern.  If Your server doesn't have the space for the tempdb SQL is trying to use after following these steps then time to invest in more disk space!

Leave a Comment
  • Please add 8 and 8 and type the answer here:
  • Post
  • PingBack from http://blogs.msdn.com/brismith/archive/2007/12/21/is-your-cube-build-slower-and-your-tempdb-larger-since-loading-sp1.aspx

  • UPDATE: Be sure to see the more recent post - http://blogs.msdn.com/brismith/archive/2008/03/24/slow-olap-cube-builds-and-large-tempdb-revisited.aspx

  • I don't know about being coincidental...the moment I completed SP1 my cube build time increased 600%.  

  • Hi Brian

    ProjectServerOLAPCubeGenerator.exe is taking up to 3 hours to complete. I assume it is creating scripts for the cube and also populating the membership list for the cube database In our situation this list is nearly 3000 thousand strong.  Is this correct?  In another sandbox environment (lower spec kit) it takes an hour less, and we have a theory that access to Active Directory is contributing to the difference.  Is this a "normal" performance? Other than reducing the membership list is there any action we could take to reduce this time?

    thx

    Mike

  • Hi Mike, sorry for the slow response - been out and about. The usual things that will slow down the cube build are data volume, additional dimensions (custom fields), out of date indexes and db statistics and sometimes a date filter range.  Although the last item can reduce the data volume, it increases the complexity of the query so can slow things overall.  I'd make sure you indexes are up to date and then compare the individual steps of the cube build reported on the status page( or possibly using SQL Profiler) to see where the difference is coming from.  I am not aware of the membership list slowing things down - although it may be directly related to data volumeas this also indicates a large resource pool.  Hope this helps - let me know how it goes.

    Best regards,

    Brian.

  • It looks like SQL 2008 has a similar problem. We migrated two Project Server instances from SQL 2000(x86) to SQL 2008(x64). For one of these instances, OLAP cube build times went from 3 hours to 8 hours. We identified the No Join Predicate problem in the query, and manually created the execution plan/hints.

    The cube build time dropped from 8 hours to 7 minutes.

    Interestingly, the 3 hour build on SQL 2000 required 50 GB of Temp DB, but the SQL 2008 queries haven't expanded tempdb beyond 1GB.

    Anyway, do the same trace flags work on SQL 2008?

    James Fraser

  • Hi James,

    I am not aware if the same trace flags work for 2008, if you get a chance to try it before I do you may be able to tell me.  Certainly the same trace flags are still required for any CU or SP on 2005.  Thanks for posting this comment.  I'll ask around...

    Best regards,

    Brian.

  • Hi James,

    Talked this through with my colleagues, and we hadn't seen this issue yet.  It would be good to log a support incident with SQL Server to get a fix for this.

    Best regards,

    Brian.

  • I still had to turn on the trace flags for the bug to be fixed even with SQL Server 2005 + SP3.

  • Hello Brian,

    we ran into the same problem on SQL Server 2008 R2. So i checked out the SQL-Flags and tryed the same as for SQL 2005 but this was not working so i searched a bit and found out that the flags changed for SQL 2008 R2 so i used the Following flags: -T4135 and -T4199 but this didn't solve the problem so i installed SQL Server 2008 R2 CU7 and set also the flag -T4136 (i found this in a blog post about sql settings for Dynamics AX) after a restart of the SQL Server i tryed it again and what a wonder Cubebuild within 3 minutes (on our old System it was 7 minutes and without this Flags it was between 4 - 12 hours.

    now its running as it should.

    Regards

    Florian Biermaier

  • Thanks very much for the feedback Flo - glad to hear it is working well for you!  I will update the main post with the 2008 R2 flags.

  • Hi Brian,

    thats great the importent thing is that the CU7 is installed

Page 1 of 1 (12 items)