We’ve added some performance configuration suggestions for SQL in this release, based on feedback from early test customers and from the results of our own performance testing. These settings are intended to help the overall performance of the system.
The following properties should be set on your Project Server databases.
AUTO_CLOSE set to False
Auto close is typically set to False by default when Project Server 2010 creates the databases for a farm setup. If set to True, this property tells SQL Server to automatically close the database when the last user has ended their connection and all other processes have completed. This makes sense in single user scenarios when you are using the desktop version of SQL Server with limited resources. However, on a multiple user system, this creates unnecessary overhead.
This property can be set accidentally, if you prototyped an environment using a Standalone install and then moved the databases to a production farm.
AUTO_UPDATE_STATISTICS and AUTO_UPDATE_STATISTICS_ASYNCHRONOUSLY set to True
Slow queries are annoying to everyone. One of the most common factors we’ve found that cause this slowness to occur is the database statistics being out of date. The two settings above are recommended so that your statistics are kept up to date AND that query processing doesn’t wait for the statistics refresh to complete. Previously, if you set AUTO_UPDATE_Statistics to True, if SQL found stale statistics, it would halt a query and make it wait until the statistics were updated. Depending on the size of the database and the query, this can lead to a substantial wait time, leading the user to think the system is hung.
In SQL Server 2005, the AUTO_UPDATE_STATISTICS_ASYNCHRONOUSLY property was added. This enabled SQL to automatically refresh the statistics in the background while allowing queries to continue execution. This leads to a better overall user experience since the operation may be a bit slower initially but it still completes. Note, Project Server 2010 does not set this property by default. Also, note, if you are migrating Project Server 2007 databases, you should update these properties post Upgrade. For more information, go to this link: http://msdn.microsoft.com/en-us/library/ms190397.aspx
If you upgrading databases from SQL Server 2000 to SQL Server 2005/2008, the sampling algorithm changed in the 2005 release. It is recommended that you run sp_updatestats with the RESAMPLE option to update the statistics, using the new algorithm.
Enable the SQL Common Language Runtime (CLR)
Custom field performance will see the most benefit from this setting. As the number of custom field values grow, the query performance will decline as the number of records to query grows.
Note, I said custom field values, not number of custom fields. For example, if you have a task level custom field with a large lookup table and assignment roll down enabled, this one field will create a lot of potential data to query. The new departments feature of Project Server 2010 may also lead to more custom fields and custom field values on the server.
Enabling the CLR on the SQL Server allows us to execute queries in a more efficient manner by reducing stress on the application server, reducing SQL roundtrips and performing queries closer to the data. The resulting Custom Field performance gains are significant. For more information on how to enable the CLR, go to this link. http://technet.microsoft.com/en-us/library/ee662108(office.14).aspx#section3
Need More Information?
I recommend starting with the SharePoint Guidance for SQL Server Database Administrators. The document is targeted to SharePoint Server 2007 but the concepts are valid for SharePoint Server 2010 as well. This document can be found here: http://technet.microsoft.com/en-us/library/ee721075.aspx
Another article on Project Server 2007 Performance and Capacity Planning best practices white paper would also be another great read. This document can be found at http://technet.microsoft.com/en-us/library/dd823304.aspx
The initial documentation for planning a SharePoint Server 2010 Server farm may also be of interest to you. The documentation can be found here: http://technet.microsoft.com/en-us/library/cc789337(office.14).aspx
Lastly, there are three videos from the Project Conference which may be of interest to you. Each cover aspects of SQL Server and Performance best practices.
Chalk Talk: Project Server 2007 Performance Tuning (Also covers SQL Best Practices)