2015-06-29

SQL Server - Tuning performance of Project Server 2010 databases

Scenario

As more users complain that different issues derived from the Project Server when managing projects and timesheets, the IT team has to spend more time troubleshooting issues. It not only downgrades users' working efficiency, it consumes internal resources on fixing problems.

Symptoms

To understand what problems users and IT team are currently dealing with, some actions have to be performed: 1. List issues that are reported from users. 2.Gather errors that are generated from system. 3.Monitor status of queue jobs.

Here is a list of the most commonly problems:
  1. Users have to wait for a long time until Project Server finishes updating/publishing projects.
  2. Timesheet issues:
    • Users are unable to update/submit their timesheet.
    • After users' timesheet has been rejected, they fail to resubmit/recall it.
  3. Some reports used for retrieving data from the Project Server seem to be executed inefficiently.
  4. The OLAP Database Build job is being configured to update the OLAP database every 1 hour.

Overview of Project Server

Let's have a brief of the target software. Microsoft Project Server 2010 is used for project portfolio management (PPM). Project stakeholders can prioritize project portfolio investments and deliver intended business value by either utilizing its web portal or Project Professional 2010.

The following diagram shows an overview of the Project Server 2010 architecture.


Here I focus only on tuning database performance. For more information about the architecture and the definition of these databases, please refer to the following links:

  1. Project Server 2010 architecture.
  2. Overview of Project Server 2010.

Cause

One thing I notice that timesheet issues very often happen during the process of updating/publishing projects, especially when some projects take long time to finish the process. It is caused by heavy blocking when doing these tasks in databases, and it is very likely to have poor query performance come across these operations.

Some legacy reports are being deployed with inadequate and inefficient queries to retrieve data directly from tables, and some of which access a large amount of data. It leads to increase the number of locked rows and contention.

The OLAP Database Build job takes up to 15 minutes to finish updating OLAP databases within company time. This can consume extensive time and resources.

Resolution

Reviewing objects of the Published database is primary to solve conflicts between timesheet and project because the Published database contains all of the published projects and hosts the tables that are used by the Timesheet Queue. After examining 2,144 objects such as views, functions and stored procedures, 241 of which have great potential to enhance query performance.

Secondarily, monitor any external connection that communicates with tables directly. The best policy is to redefine security principals such as logins, roles, and users of least privilege. For any reporting services regarding the Project Server, connections should use the Reporting database as the main data source; and further, it should connect to views rather than tables, and also identify whether any operation retrieves enormous data at a time.

The OLAP Database Build job is an expensive task to the system. Use real-time dimensions only when absolutely necessary. The workaround is to adjust updating frequency to mitigate loading, or schedule it to be executed during off-peak hours.

Status

After tuning process 6,342 database objects (except tables and indexes) are reviewed. A list of the number of revised objects is as follows (revised/total):

  1. Archive database: 0 / 260
  2. Draft database: 35 / 1,037
  3. Published database: 241 / 2,144
  4. Reporting database: 74 / 868
  5. Content database: 24 / 2,033
Rectifying these objects has dramatically improved performance of the Project Server, and also has greatly reduced issues. The positive result can be evaluated by the following facts:
  1. The queue jobs are executed efficiently, which jobs are no longer getting stuck in queues. This can be monitored in the Manage Queue Jobs. For instance, even one of the largest projects takes only up to 3 minutes to finish publishing.
  2. The number of issue between timesheet and project becomes considerably small.