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 @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
