Is your cube build slower, and your tempdb larger since loading SP1? – UPDATE – See new posting 3/24/2008

See http://blogs.msdn.com/brismith/archive/2008/03/24/slow-olap-cube-builds-and-large-tempdb-revisited.aspx for the latest information on this problem.

There is an issue with some of the SP1 and hotfix 939594 fixes which resolve earlier problems of data missing in the cubes.  The query has become more complex and SQL Server 2005 is not coming up with the right execution plan for this new more complex query.  The upshot of this is much use of tempdb – which can grow very large, and the cube build takes much longer than it did.

You may have already seen the blog which gives a workaround for this issue – but as many of you may see this anew after loading SP1 I wanted to raise awareness again. Thanks to Noel, Kermit, JF and Thuy for helping get to the bottom of this one.

The problem is mostly seen if you have added lots of dimensions to the various cubes and also if you specify a dynamic date range.  A full build from earliest to latest is sometimes a workaround which reduces the impact on tempdb but is still slower than it should be.  My suggested steps which are also mentioned in the referenced blog and given even more coverage in the MSDN article are:-

1. Create a cube set exactly how you want it (earliest to latest please – no dynamic date range) and while it is building capture a trace in SQL Profiler.  SQL Database Engine should be profiled – can be restricted to the reporting db.

2. After a few minutes search in the running trace for MSP_EpmAssignmentByDay_OlapView.  If it isn’t found then wait a little while longer and try again.

3. Once found then right click the line in the trace and select Extract Event Data. (DO NOT COPY FROM THE bottom pane).  In the save as dialog save this as badquery.sql.

4. The current cube can be stopped at this point.  If no one else is using Analysis Service then just restarting will kill this off.  If you do restart then you will need to close out of the Cube Status window otherwise you can get errors with the next cube build.

5. In SQL Server Management Studio open a new query to the Database Engine and select the Reporting DB.  Copy the following text into the query window.

EXEC sp_create_plan_guide N’guide_forceorder’,

    N'<exact query>’,

    N’SQL’,

    NULL,

    NULL,

N’OPTION (force order)’

6. Open the badquery.sql file and select all (Ctrl-A or Edit, Select All) then Copy (CTRL_C or Edit Copy) and then select the <exact query> from the text in the query window and Paste (CTRL-V).  You should have selected the < > character too.  This may leave some space between the ‘ and the SELECT but this is fine.

7. Execute this command – which should finish successfully.  Build a new cube and this should use this execution plan and process more quickly.  To get an indication of the speed you could open badquery.sql and then at the end of the query paste OPTION (FORCE ORDER) and then execute.

8. You can also monitor in SQL Profiler to see if it is finishing quicker by looking for the MSP_EpmAssignmentByDay_OlapView and seeing if the SQL BatchCompleted comes in a reasonable time.

The reason to avoid dynamic date ranges is that this will make the query change every time it is run – and then the execution plan will not match – and will be ignored.

If you use a constant date range then this method can be applied – but read the MSDN article on the need to escape single quotations marks.  The will be around the dates – so for instance ’12/31/2008′ would need to be ”12/31/2008”.  Note that these are two individual quotes, not a double quote.

As an example this workaround can mean the tempdb hardly gets touched (rather than growing 30GB or more) and the cube builds in less than 1 tenth of the time.  Your mileage may vary.

I hope this helps – and if you don’t understand any of the steps above then you probably shouldn’t attempt this – speak to your DBA.  But if you are seeing this issue and need some assistance to address it then please open an incident with our support teams.  Http://support.microsoft.comwill give you the options.

Technorati Tags: Project Server 2007