Project Server Blogs

Archive for February, 2008

— Next Page »

Managing Microsoft Project 2007 Custom Fields using the PSI

29 February 2008

Popularity: 1% [?]

Micosoft Project 2007 PSI Custom FIeld CRUD

29 February 2008

Popularity: 1% [?]

Project Management, Atlas Shrugged, The Glass Bead Game, and my Years at Hogwart’s School of Witchcraft and Wizardry

29 February 2008

Popularity: 1% [?]

Database Maintenance for SharePoint

29 February 2008

Bill Baer’s white paper: Database Maintenance for SharePoint is now live. 

Table of Contents

• Check for and repair consistency errors by using DBCC CHECKDB 1
• About DBCC CHECKDB 1
• DBCC CHECKDB and performance 2
• Measure and reduce fragmentation 3
• Measure fragmentation in a SQL Server 2005 database (sys.dm_db_index_physical_stats) 4
• Measure fragmentation in a SQL Server 2000 database (DBCC SHOWCONTIG) 5
• Reducing Fragmentation for a Database 6
• Reducing fragmentation for a specific table and its indexes 9
• Using ALTER INDEX 9
• Fine tuning index performance by setting fill factor 10
• Shrinking data files 10
• Shrinking a database by using Transact-SQL commands 11
• Shrinking a database by using SQL Server 2005 Management Studio 12
• Creating SQL Server 2005 Maintenance Plans 13
• Configure a SQL Server 2005 Database Maintenance Plan 13
• Summary 22

Popularity: 1% [?]

Running SQL Server Profiler with Project Server

29 February 2008

Quite often when you are performing customizations or when you want to troubleshoot your Project Server deployment it’s useful to go “deep” and see what’s happening under the SQL hood.

The best way to achieve that is to run a SQL Server Profiler trace on the SQL Server instance you want to analyze. Since Project Server 2007’s architecture is based on a queuing system you will find a lot of “noise” if you run a standard trace.

I have attached at the end of this post a template I have created that eliminates a lot of the extra SQL queries due to the queuing system.

Procedure

• Download trace template and copy it when you’ll be running the SQL Profiler from
• Double click on it:

 

• SQL Server Profiler is launched and you should get this message:

• Click OK and click New Trace then connect to the DB server
• Under Use Template select Project Server 2007 SQL Trace Template, and run trace

• Sample trace should look like this:

Please note that this trace only looks at databases with the suffix Published and ignores commands with prefix MSP_TimesheetQ.
Happy tracing!

Popularity: 1% [?]

OLAP and Data Analysis - timeouts, plan guides and tempdb

29 February 2008

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=”http://schemas.microsoft.com/sqlserver/2004/07/showplan” 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.

Timeouts

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
unsuccessfully

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>
database.

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.

Popularity: 1% [?]

Cool Sample PSI Apps and OLAP Info

29 February 2008

Chris Boyd and Brian Smith do it again.

Check here for two cool sample applications, one for showing how to create, read and update custom fields via the PSI and another used for reading custom field data and then publishing projects, again via the PSI.

Check here for good info about OLAP cubes, Data Analysis (timeouts, plan guides and the tempdb)

Popularity: 2% [?]

Custom Field and Lookup Table Webcast

29 February 2008

Hello,

Attached is my source code and power point presentation of today’s webcast on custom fields and lookup tables:

• CustomFieldCRUD - This a very simple application that shows how to create, read, update and delete custom field definitions on Project Server 2007 using the PSI.
• PublishProjects - This is an application that was written for our internal dogfood effort. We use this application to publish all the Project team’s project plans on our Project Server. It shows how to read project custom fields and lookup table values from the Project PSI.
• Custom Fields and Lookup Tables - This is a PDF version of the slide deck used for the presentation.

We will be publishing the presentation to the web in the next few days. Once it is available, I will post a link to it.

 

Chris Boyd

 

Popularity: 1% [?]

Guidance Automation Feb. Release

27 February 2008

Quick heads up on a great new tool for teams on the patterns and practices msdn site.  The Guidance Automation Extensions and Guidance Automation Toolkit Feb 2008 release is now out.  This tool looks like that it might be a great addition to aid enterprise development groups in emphasizing coding standards. 

Call me crazy, but I see a use for guidance for something like custom sharepoint applications.  For instance, documentation on our standard event handler code, along with sample classes.  I'm not sure if this is possible with this tool, but I'll be investigating.  I'd love to hear if anyone else is using this tool, for this purpose.

Popularity: 1% [?]

Microsoft Project 2003 to 2007 Migration Workbench Part II

25 February 2008

Popularity: 1% [?]

— Next Page »