Microsoft Office Project Server 2007 and Microsoft Project Server 2010 internally manage cache synchronization by tracking revisions to major entities such as projects and maintaining records for deletions of those entities. Although the Draft, Published, and Archive databases in Project Server are not documented, and direct access to those databases is not supported, a basic understanding of cache synchronization in Project Server can be useful in determining issues of performance optimization. (This article was written by Patrick Conlan, Microsoft Corporation.)
Warning: The information in this article is applies only to the specified versions of Project Server, and is subject to change without notice.
In Project Server 2007, we made a series of changes that were designed to improve the scalability and reliability of Project Server, especially when it is under usage stress. One of the most significant changes was the introduction of several caches, specifically:
The location of the master copy of data varies by content. Projects use the Draft database; most other data uses the Published database.
The caches are synchronized during data movement operations such as the following:
We use a fairly complicated versioning scheme to work out what data needs to move as part of the synchronization. Some of you may have already worked out some of the versioning processes!
Depending on the number of project managers who use Project Professional within an organization, there can be hundreds or even thousands of caches - one on every Project Professional desktop plus the caches in the SQL Server databases. Depending on when they were last synchronized, the caches can contain different versions of project entities.
Each entity has a primary table that controls the entity version number. For example, MSP_Projects is the primary table for projects. When an entity is created and each time the entity is saved, Project Server uses the entity version to aggregate the edit session changes.
Version Tracking Schema
The following schema fragment from the MSP_Projects table shows the fields that are used to track changes at the project level:
[REV_NEXT_SEED], [REV_RANK], [PROJ_CURRENT_REV_COUNTER], [PROJ_CURRENT_REV_RANK], [CREATED_REV_COUNTER], [MOD_REV_COUNTER]
The REV_RANK and PROJ_CURRENT_REV_RANK fields are used to signal an archive restore. If synchronization shows a disparity of rank between caches, then Project Server performs a full resynchronization (in effect resetting the REV_RANK field with a revision = 0)
The REV_NEXT_SEED field contains the next version number to be used for any changes. The PROJ_CURRENT_REV_COUNTER and MOD_REV_COUNTER contain the active version number. There are two numbers to enable us to see when the document was lasted edited by Project Professional or by another application.
The CREATED_REV_COUNTER field is either 1 or 0, depending on how the project was created. When Project Professional creates a project, CREATED_REV_COUNTER = 1.
Handling Deletes
We had two options for handling deletes in the cache coherency model:
Flagging would give better short term performance (less impact on SQL Server), but would quickly cause data bloat within the most important tables. We decided to use the second option of deleting the data and creating a record of the deletion.
We use shadow tables to record delete operations for tables that are in the cache synchronization scheme. Shadow tables are very narrow tables that contain the minimum data needed to track deletes. Project Server updates the shadow tables when major entities are deleted.
The following schema is for the project shadow table, MSP_PROJECTS_SHADOW.
[PROJ_UID] [dbo].[UID] NOT NULL, [DELETED_REV_COUNTER] [int] NOT NULL, [DELETED_DATE] [datetime] NOT NULL
When a project is deleted, it uses just 28 bytes of data in a row of the MSP_PROJECTS_SHADOW table (16 bytes for the GUID + 4 bytes for the INT + 8 bytes for the DATETIME value), plus normal SQL Server page overhead.
Administrators of Project Server installations that have a lot of data churn should maintain an active archive and delete policy to take advantage of the shadow tables and improve performance.