Recommended performance enhancements (Project Server 2010)
Applies to: Project Server 2010
Topic Last Modified: 2010-02-10
There are two Microsoft SQL Server settings that we recommend that you implement to help achieve optimal performance for your deployment:
SET AUTO_CLOSE OFF
SET AUTO_UPDATE_STATISTICS_ASYNC ON
We recommend that you configure these settings for the four Microsoft Project Server 2010 databases (Draft, Published, Archive, and Reporting) for each Microsoft Project Web App site that you create.
These are not required settings. You should evaluate them based on the needs of your organization and any other applications that might be using the same instance of SQL Server where the Microsoft Project Server 2010 databases reside.
When you have completed this section, go to the next article, Configure reporting for Project Server 2010.
AUTO_CLOSE
By default, this option is set to ON for all databases when you are using SQL Server Desktop Engine (also known as MSDE 2000), and OFF for all other editions, regardless of operating system. The AUTO_CLOSE option should not be used for databases accessed by an application that repeatedly makes and breaks connections to SQL Server, such as Project Server. For this reason, we recommend that you set AUTO_CLOSE to OFF for the Project Server databases.
AUTO_UPDATE_STATISTICS_ASYNC
Statistics updates can be either synchronous (the default) or asynchronous. In synchronous statistics updates, queries always compile and execute with up-to-date statistics; when statistics are out-of-date, the query optimizer waits for updated statistics before it compiles and executes the query. For Project Server 2010, we recommend that you set AUTO_UPDATE_STATISTICS_ASYNC to ON for optimal query performance. This lets queries execute immediately without waiting for the statistics to update.