There are some resources on the web that suggest that allowing snapshot isolation (by using ALTER DATABASE and SET ALLOW_SNAPSHOT_ISOLATION ON), and turning READ_COMMITTED_SNAPSHOT to ON can help performance of SharePoint systems – but none as far as I could find from Microsoft and the SharePoint team – and certainly none from Project. We have seen bad behavior when this option is enabled and definitely no performance improvements. One symptom that might indicate this issue is the Manage Queue page may not load. It isn’t something that can happen by accident, as it requires two specific changes at the database level, but it could well have been changed with good intention – perhaps to avoid locking issues that may have been better prevented by other preventative measures, such as good database maintenance.
To check if you have these settings configured get your DBA to run DBCC useroptions against your PWA databases. The default value they should see at the foot of the list is isolation level = read committed.
If it says “read committed snapshot” that means ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT have been set to ON.
With these options ON, SQL Server will keep additional copies (in tempdb) of the previous values of rows currently being updated. This means that those values can be read – perhaps avoiding locks – but at the cost of making and managing the extra copy. And avoiding the lock (dead or alive) may not be what the application developer ever expected to happen – so you really need to understand the application before turning these options on and hoping for the best. A couple of good resources if you want more background – the TechNet article at https://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.110).aspx covers snapshot isolation – and the video from SQL Certified Master, Kimberly Tripp, at https://technet.microsoft.com/en-us/sqlserver/gg545007.aspx is very well worth a viewing. (Kimberly does also take wonderful photos of eagles – but I’ll let you ‘Bing’ for those…).
While I am in SQL Server mode a couple more things worth bringing up:
- Since Project Server 2013 SP1 we support SQL Server 2014 (the TechNet article is still a bit behind)
- We handle GUIDs a little better in Project Server 2013 – and in many places they are sequential GUIDs which cause less index fragmentation – but still good practice to use a fill factor of 70% and defrag/rebuild indexes frequently (where frequently for some customers is every day, most will be ok weekly and some even monthly). If you have migrated then even more reason to defrag – as you will not have the benefit of sequential GUIDs
- As with previous Project Server versions – auto update statistics should be ON – but the jury is out on auto update statistics async. Some feel that waiting for the right stats will get an overall quicker execution, while others think you should just get on with the execution and update stats later. It probably works better in some places than others – with rapid data changes benefitting from fresh stats (think – lots of jobs joining the queue, AD Sync etc.)