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

  • Brian Smith's Microsoft Project Support Blog

    Lies and out-dated statistics


    The saying goes that you can prove anything with statistics - and I am sure the same figures are being used by the McCain, Obama and Clinton camps to tell vastly different stories.  But enough about politics - what has this to do with Project Server 2007?  SQL Server uses statistics too - and if they are not up to date it might make the wrong decisions!

    As an example if you were trying to find me in a database and there were indexes on my department, surname and date of birth, and you knew may name, birth month (but not the actual day or year) and my department what is the best way to find me?  With a name like Smith, you probably wouldn't want to start there.  So perhaps you would find all the people in Project Support, then look for any with surname Smith, then trim down to the ones born in the right month and finally see if any (or hopefully the only one) is called Brian.  If you were looking for Chris Boyd, or Christophe Fiessinger then you might start your filtering on their names first - as there aren't as many Boyds or Fiessingers as there are Smiths. What you are doing is building an execution plan, and usually the most efficient way is to trim down the number of results at the earliest point in the plan.  SQL Server does this by keeping statistical data about the contents of data tables and indexes, and builds plans according to this data.  If the data is not accurate it can lead to execution plans that are not optimal.

    Maintenance plans in SQL Server give you the ability to schedule jobs to reorganize your indexes to ensure they are efficiently stored both for reading and writing - and also update your statistics to ensure SQL will make the best possible decisions when deciding a query plan.  For SharePoint databases you should avoid using the default rebuild index option of the maintenance plan wizard as it does not re-apply existing index options - but you can script your own tasks to do this.

    There are some great resources online for maintenance of your databases - I'd suggest the recent Whitepaper titled Database Maintenance for Microsoft® SharePoint® Products and Technologies and also for general background reading the SQL Books Online, Database Engine Administrator InfoCenter is an excellent resource. 

    I will be following up in the next few weeks with blogs specifically about two areas where a good set of up to date statistics can improve the performance in Project Server 2007.

    • Building Data Analysis cubes - outdated statistics are one of the causes of recent problems seen with slow cube building and heavy tempdb usage.
    • Project Center Views - the error that can appear after around 30 seconds if you have many projects, and also large project that you drill into.
  • Brian Smith's Microsoft Project Support Blog

    OLAP and Data Analysis - timeouts, plan guides and tempdb


    UPDATE: Be sure to see the more recent post -  

    Some of this has been in previous postings or comments on postings - but thought it was time for a round-up.  As a quick intro this blog will look at more information on tempdb growth some of you may have experienced since Project Server 2007 SP1, as well as timeouts that you might have hit due to the same problem.  I'll also talk more about SQL Server 2005 plan guides used to overcome this problem - and finally errors you may see after your reporting database gets rebuilt - or refreshed as we call it in the queue.

    TEMPDB Growth

    In SP1, and in a hotfix just prior to SP1 we introduced some dimensions to the cube that we had missed in the initial release.  These related to some of the flags for generic and team resources and a few other things.  For customers that were also using many other dimensions and had large datasets these changes gave longer queue build times and also increased the use of tempdb as the SQL execution plan made use of table spools which create temporary tables in the tempdb database.  In many cases the workaround described in my previous blog  gives some relief from this problem.  You may still need a reasonable amount of tempdb so don't bother shrinking tempdb below the level it would normally use - it will just slow things down as it has to grow again next time.

    In some cases the increased cube build time may lead to the timeout issue dealt with below - and also applying the workaround can be challenging.  To help understand the workaround a bit more on to plan guides...

    Plan Guides

    SQL Server Books Online have always been a great resource since the early days of SQL Server.  This is still true and the plan guide documentation can be found at Understanding Plan Guides.  Basically whenever SQL runs a query it will look for any plan guides (in the system table sys.plan_guides - so select * from sys.plan_guides will show what is set) and if it has query_text in that table that matches the query it will follow the hints in that table.  The query needs to be an exact match so any change in date or dimensions (or even order of dimensions added to a cube) may "break" the plan guide and the workaround would need to be re-applied.  As the default name of the plan (from the workaround) is already in existence either a new name needs to be used or the plan guide needs to be dropped from the database.  An example of the command to drop a plan guide called guide_forceorder would be:

    EXEC sp_control_plan_guide N'DROP', N'guide_forceorder';

    You can also enable and disable the plan guides using the sp_control_plan system stored procedure.

    To see if a plan guide is being used you can run the profiler trace and add the Showplan XML event type (located under the Performance node).  Then in the trace you should find the Showplan XML event for the affected query and it will start something like:

    <ShowPlanXML xmlns="" Version="1.0" Build="9.00.3054.00"><BatchSequence><Batch><Statements><StmtSimple PlanGuideDB="pwa_Reporting" PlanGuideName="guide_forceorder"><QueryPlan CachedPlanSize="1463" CompileTime="336" CompileCPU="336" CompileMemory="9432"><RelOp NodeId="0" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="1625" EstimatedTotalSubtreeCost="778.134" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Column="Expr1007"/><ColumnReference….

    If you don't find this then perhaps the query in the plan doesn't match what Analysis Services is using to pull the data.


    One of my tips for support engineers is that if something fails at very specific times then be suspicious.  So when a cube fails at just over an hour suspect that the default External Command Timeout is still set in Analysis Services.  The default is 3600 and is in seconds - so is equal to 1 hour.  Right click the AS server in Management Studio, then set Show Advanced (All) Properties and check the External Command Timeout.  Increase as appropriate - 36000 would be 10 hours.  For the SP1 issue you may find that this is the first error you hit - then once you get past this the tempdb problem may give you issues.

    The error for the timeout issue is:

    Failed to build the OLAP cubes. Error: Analysis Services session failed with the
    following error: Failed to process the Analysis Services database <cube name> on
    the <Analysis Server name> server. Error: Internal error: The operation terminated

    Internal error: The operation terminated unsuccessfully. Internal error: The
    operation terminated unsuccessfully. OLE DB error: OLE DB or ODBC error:
    Unspecified error. Errors in the OLAP storage engine: An error occurred while
    processing the 'Assignment Timephased' partition of the 'Assignment Timephased'
    measure group for the 'Assignment Timephased' cube from the <cube name>

    The initial part of the error may be different depending on the exact point the timeout stops things.  The first part will also be localized if you are running a language pack.

    Refreshing the reporting database loses the Assignment dimensions

    The error for this problem is:

    Failed to build the OLAP cubes. Error: Invalid call to GetJoinForCustomFieldDimension. Cannot join "Task Non Timephased" fact with a custom field of entity Assignment

    And is caused when you have added dimensions to your assignment cube and then have done something that will have caused the reporting database to refresh.  Restoring from certain administrative backups will do this.  The fix is easy - just remove the dimensions from the assignment cube, save the configuration - then add them back again and save again.  To be sure this is your issue take a look at the view MSP_EpmAssignment_OlapView in the reporting database.   If the last column is AssignmentBaseline10BudgetMaterialWork then this means no dimensions are added.  If the repository is expecting some dimensions you will get the error.  A screenshot of the view with added dimensions will look something like:


    I hope this helps to understand more about the cube build process and some of the problems you can run into.

  • Brian Smith's Microsoft Project Support Blog

    COMException on x64 platforms when automating the Project client via the Primary Interop Assembly (PIA)


    ***UPDATE*** Hotfix now available -  

    You may see this error when using the CodePlex Test Data Population sample for creating project data using the WinProj tab, or just using your own code to automate winproj.exe (the Microsoft Office Project Professional 2007 client application).  It is only a problem with the object model interaction and not an issue with PSI calls.  It is the Tasks.Add() method which is the trigger for the problem, and it will work just fine on x86, but fails on x64.

    Currently the x64 platforms do not support more than 1024 methods on an object (which comes down to around 1017 once the COM standard methods are deducted) and the Tasks object has a lot of methods.

    The error is:

    Error HRESULT E_FAIL has been returned from a call to a COM component. System.Collections.ListDictionaryInternal.

    One work around we have found is re-writing to avoid using the Tasks method.  So the following code:

    protected void Page_Load(object sender, EventArgs e)
    string filename = "c:\\test.mpp";
    ApplicationClass a = new ApplicationClass();
    a.FileNew(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
    Microsoft.Office.Interop.MSProject.Project p = a.ActiveProject;
     p.Tasks.Add("test", Type.Missing);
    p.SaveAs(filename, PjFileFormat.pjMPP, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, "MSProject.mpp.9", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
    lblfilename.Text = filename;

    could be re-written as:

    protected void Page_Load(object sender, EventArgs e)
    MSProject.Application objAppProject;
    MSProject.Project objProject;
    string filename = "c:\\test.mpp";
    objAppProject = new Microsoft.Office.Interop.MSProject.Application();
    objAppProject.FileNew(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
    objAppProject.EditGoTo(1, Type.Missing);
    objAppProject.SetTaskField("Name", "Test", true, Type.Missing, 1, Type.Missing);
    objProject = objAppProject.ActiveProject;
    objProject.SaveAs(filename, MSProject.PjFileFormat.pjMPP, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, "MSProject.mpp.9", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
    lblfilename.Text = filename;

    and would work in both x86 and x64 environments.

    This is currently being worked on by the Windows teams and we are anticipating a fix.

  • Brian Smith's Microsoft Project Support Blog

    Loading projects from server or mpp files can be slow with some security products


    One thing we have seen recently in support is several cases where security products can slow down the loading of projects into Project Professional 2007 both from mpp files or from Project Server 2007 connections.  An example of this is McAfee Host Intrusion Prevention, and we have seen this make projects load up to ten times slower.  We are working with McAfee to see if there is a workaround for this and I would certainly not suggest disabling any security products on your servers.  It might however be worth testing in a secure environment if you think your speed might be affected by something like this - if only to eliminate it as a "suspect".

    I'm hoping we may just need to exclude something and things will speed up - if anyone has experience of HIP and can give me pointers I'm happy to talk.

  • Brian Smith's Microsoft Project Support Blog

    Timesheet Classifications - what they don't do


    With the change in relationship between time tracking in timesheets, and the updating of status in tasks, that we introduced in Project Server 2007 there has been some confusion.  One area that after working on a case yesterday needs a little more explanation is timesheet classifications.  Where you create these in PWA it give the following description:

    Edit, Enter Line Classification

    You can duplicate timesheet lines for business purposes or accounting reasons. To do this, define timesheet line Classifications, which will become the unique identifier for a timesheet line.

    What it doesn't say directly is that these classifications are not for tasks - and only the timesheet classification of "Standard" can be imported to the "My Tasks" page through "Import Timesheets".  All other classification types will be ignored and any lines will not display in the grid to be imported.

    The intention behind this design is that other classifications can be used for business analysis of time related to the task that was not productive as far as working the task.  So you may have spent 2 hours traveling to perform a task - and you may bill the customer, but this wasn't something directly related to performing the task - and in this scenario was not included in the planned time to perform the task.  Obviously there could be other ways to handle this and the travel may well be a task on its own. 

    In the support incident where this caused some issues the customer was trying to use the classifications as a way of flagging different activities within the same task - but as the only the standard line goes through to the time tracking in the project this would not work.  The solution agreed here is that the project requires tasks for all the different activities being performed.  You could also do some sort of server side event that allows you to add time in different timesheet classifications against the same task - and then these get consolidated in the "Standard" row - but analysis would only be available in the timesheet and reporting database.


    As an example only the 6h highlighted for the ABC Outlook Task 1 would be available for import to "My Tasks" - the research and travel time would not. 

    I hope this helps clear up any misunderstanding of this feature.

Page 81 of 98 (490 items) «7980818283»