How to improve Project Server 2007 database performance?

You have been using EPM for a while and recently started experiencing some performance issues? Perhaps the projects are taking longer to save or perhaps certain Project Centre views have stopped displaying projects….you get a message indicating ‘…there are no projects to display’. The reason may be fragmentation of database table indexes.

Over time you will experience database performance issues if they not properly maintained on a regular basis. Some of things to bear in mind when setting up the database maintenance plans are mentioned here. 

You can quickly indentify if there is any problem by running the following SQL query on the Draft and Published databases to determine the index fragmentation in the key tables.

Note: Feel free to run the query for other key tables if you wish for example msp_tasks, msp_assignments. In my experience the key indicator of problems has always been the index fragmentation of ‘msp_projects’ table. 

DBCC SHOWCONTIG (msp_projects)

And, run the following query on the Reporting database;

DBCC SHOWCONTIG (msp_epmproject)

Review of the result of the queries. The following example will help those who are not SQL DBAs/GURU!

Example output of the above query is shown below;

- Pages Scanned................................: 333557 - Extents Scanned..............................: 41805 - Extent Switches..............................: 262556 - Avg. Pages per Extent........................: 8.0 - Scan Density [Best Count:Actual Count].......: 15.88% [41695:262557] - Logical Scan Fragmentation ..................: 75.11% - Extent Scan Fragmentation ...................: 13.04% - Avg. Bytes Free per Page.....................: 2853.9 - Avg. Page Density (full).....................: 64.74%

A low percentage for scan density is bad. A high percentage for logical scan fragmentation is bad. Further info on this can be found in the SQL online book.

To resolve the issue run the following queries on all the Project Server Databases. Rebuild Indexes in databases followed by updating Statistics. See below.

Note: If you are using project workspaces, then suggest you do the same on the content database(s).

Step (1) – Rebuild Index in Database

---T-SQL script which will rebuild each index of all tables of any particular database. ---Following script will work with SQL Server 2005 (SP2) and SQL Server 2008. ---It is simple cursor going over each table and rebuilding every index of database. DECLARE @TableName VARCHAR(255) DECLARE @sql NVARCHAR(500) DECLARE @fillfactor INT SET @fillfactor = 90 DECLARE TableCursor CURSOR FOR SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName FROM sys.tables OPEN TableCursor FETCH NEXT FROM TableCursor INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' EXEC (@sql) FETCH NEXT FROM TableCursor INTO @TableName END CLOSE TableCursor DEALLOCATE TableCursor GO

Note: Keep the Fill Factor values between 75 and 100. I tend to leave this at 90 and works for me well. There are other considerations but I won’t bore you with all the superfluous details.

Step (2) – Update Statistics on Databases

sp_updatestats

Hope these precise steps helps all the EPM application administrators who have to double up as SQL DB administrators for their EPM deployment.

Note: Thoroughly test all changes on your test/development system prior to applying on live/production system.