Project Web App (PWA) site loading pretty slow in Project Server 2013

We had an issue when trying to view My Tasks on Project Web App site on Project Server 2013 it consumed 20-30 seconds to load this. Here is how I started troubleshooting this

1)      Started with the ULS logs but couldn’t go any further.

2)     From some MSDN article suggestion was to disable the proxy server as it can cause the issue. Did so but didn’t help to solve the issue.

There is a good blog which talks about if server not connected to internet can result in PWA loading slow due to validation of certificate , having SP cert in Trusted root store etc.

Performed all those but it wasn’t applicable in my case as each and every unique environment is different.

3)     Enabled the Developer Dashboard, replicated by navigating to My Tasks

·         Understood from Developer Dashboard, that web services consumed lot of time to fetch the info from MS schemas

·         The stored procedures from SQL Server was taking time to respond to the application request

Developer Dashboard was handy to identify the bottle neck. Performed the following steps to resolve this:

• ·         DBCC SHOWCONTIG displays fragmentation information for the data and indexes
• ·         Rebuild the indexes on PWA DB
• ·         Updated the statistics

1) Run DBCC SHOWCONTIG

2) Rebuild the indexes on Project Web App DB:

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

You can change the number for SET @fillfactor between 70 to 100.

3) Update Statistics:

sp_updatestats