Performance problems occur in Project Server 2010 when the number of shadow table records increases quickly

Symptoms

The number of shadow table records that are generated in an Enterprise Flag field increases quickly in Microsoft Project Server 2010. This issue causes performance problems.

Resolution

To resolve this issue, apply the following hotfix package:

2597138 Description of the Project Server 2010 hotfix package (Pjsrvwfe-x-none.msp): February 28, 2012

More Information

This hotfix manages the number of shadow table records so that the number of records does not increase too much. Every time that you open or save a project plan in Project Professional after you apply the hotfix on the Project server, the shadow table records that are relevant to the project are cleaned up. If you want to clean up the shadow table records in bulk, you can use the following script. Before you run the script, make sure that you read the following points:

• Make a backup of the Project server database before you run the script.

• To avoid unexpected database content issues, stop the Queue Service on the Project application servers when there has been no activity on the servers for several hours.

• Validate the script in a development environment by using the current backup of the Project server database.

• Time how long it takes to complete the script in the development environment. The time that is required to complete the delete script depends on the SQL server and the number of the records that have to be removed. Make sure that the script is completed within the planned time.

To clean up the shadow table records in bulk, follow these steps:

1. Run the following script on both the Draft and Published databases, and reclaim the free space in the databases:
   

   --Clean up       MSP_TASK_CUSTOM_FIELD_VALUES_SHADOW 
         IF object_id('tempdb..#TempTaskCFShadow') IS NOT NULL 
         BEGIN  
         drop table #TempTaskCFShadow  
         END  
   
       CREATE table #TempTaskCFShadow (pid uniqueidentifier, tid uniqueidentifier, revCount int, mdpid uniqueidentifier)
   
         INSERT #TempTaskCFShadow (pid, tid, revCount, mdpid)
         SELECT PROJ_UID, TASK_UID, MAX(DELETED_REV_COUNTER), MD_PROP_UID
                           FROM MSP_TASK_CUSTOM_FIELD_VALUES_SHADOW
                           GROUP BY PROJ_UID , TASK_UID, MD_PROP_UID 
   
         DELETE 
         FROM MSP_TASK_CUSTOM_FIELD_VALUES_SHADOW 
           FROM MSP_TASK_CUSTOM_FIELD_VALUES_SHADOW as originalTable, #TempTaskCFShadow AS newtable
         where originalTable.MD_PROP_UID = newtable.mdpid AND
         originalTable.PROJ_UID = newtable.pid AND 
         originalTable.TASK_UID = newtable.tid AND
         originalTable.DELETED_REV_COUNTER <> newtable.revCount
   
           DROP TABLE #TempTaskCFShadow
   
   --Clean up MSP_ASSN_CUSTOM_FIELD_VALUES_SHADOW 
         IF object_id('tempdb..#TempAssnCFShadow') IS NOT NULL 
         BEGIN  
         drop table #TempAssnCFShadow  
         END
   
         CREATE table #TempAssnCFShadow (pid uniqueidentifier, asid uniqueidentifier, revCount int, mdpid uniqueidentifier)
   
         INSERT #TempAssnCFShadow (pid, asid, revCount, mdpid)
         SELECT PROJ_UID, assn_uid, MAX(DELETED_REV_COUNTER), MD_PROP_UID
                           FROM MSP_ASSN_CUSTOM_FIELD_VALUES_SHADOW
                           GROUP BY PROJ_UID , ASSN_UID, MD_PROP_UID 
   
         DELETE 
         FROM MSP_ASSN_CUSTOM_FIELD_VALUES_SHADOW
           FROM MSP_ASSN_CUSTOM_FIELD_VALUES_SHADOW as originalTable, #TempAssnCFShadow AS newtable
         where originalTable.MD_PROP_UID = newtable.mdpid AND
         originalTable.PROJ_UID = newtable.pid AND 
         originalTable.ASSN_UID= newtable.asid AND
         originalTable.DELETED_REV_COUNTER <> newtable.revCount
   
           DROP TABLE #TempAssnCFShadow
   
   
   --Clean up       MSP_PROJ_RES_CUSTOM_FIELD_VALUES_SHADOW
         IF object_id('tempdb..#TempProjResCFShadow') IS NOT NULL 
         BEGIN  
         drop table #TempProjResCFShadow  
         END  
   
         CREATE table #TempProjResCFShadow (pid uniqueidentifier, resid uniqueidentifier, revCount int, mdpid uniqueidentifier)
   
         INSERT #TempProjResCFShadow (pid, resid, revCount, mdpid)
         SELECT PROJ_UID, RES_UID, MAX(DELETED_REV_COUNTER), MD_PROP_UID
                           FROM MSP_PROJ_RES_CUSTOM_FIELD_VALUES_SHADOW
                           GROUP BY PROJ_UID , RES_UID, MD_PROP_UID 
   
         DELETE 
         FROM MSP_PROJ_RES_CUSTOM_FIELD_VALUES_SHADOW 
           FROM MSP_PROJ_RES_CUSTOM_FIELD_VALUES_SHADOW as originalTable, #TempProjResCFShadow AS newtable
         where originalTable.MD_PROP_UID = newtable.mdpid AND
         originalTable.PROJ_UID = newtable.pid AND 
         originalTable.RES_UID = newtable.resid AND
         originalTable.DELETED_REV_COUNTER <> newtable.revCount
   
         DROP TABLE #TempProjResCFShadow
   
   
    --Clean up MSP_PROJ_CUSTOM_FIELD_VALUES_SHADOW 
         IF object_id('tempdb..#TempProjCFShadow') IS NOT NULL 
         BEGIN  
         drop table #TempProjCFShadow  
         END  
   
         CREATE table #TempProjCFShadow (pid uniqueidentifier, revCount int, mdpid uniqueidentifier)
   
         INSERT #TempProjCFShadow (pid, revCount, mdpid)
         SELECT PROJ_UID, MAX(DELETED_REV_COUNTER), MD_PROP_UID
                           FROM MSP_PROJ_CUSTOM_FIELD_VALUES_SHADOW
                           GROUP BY PROJ_UID , MD_PROP_UID    
   
         DELETE 
         FROM MSP_PROJ_CUSTOM_FIELD_VALUES_SHADOW 
           FROM MSP_PROJ_CUSTOM_FIELD_VALUES_SHADOW as originalTable, #TempProjCFShadow AS newtable
         where originalTable.MD_PROP_UID = newtable.mdpid AND
         originalTable.PROJ_UID = newtable.pid AND 
         originalTable.DELETED_REV_COUNTER <> newtable.revCount
   
           DROP TABLE #TempProjCFShadow

2. Run the following script on the Published database after you run the script from step 1 on both the Draft and Published databases:
   

   --Clean up MSP_RES_CUSTOM_FIELD_VALUES_SHADOW
   
         IF object_id('tempdb..#TempResCFShadow') IS NOT NULL 
         BEGIN  
         drop table #TempResCFShadow  
         END  
   
         CREATE table #TempResCFShadow (resid uniqueidentifier, revCount int, mdpid uniqueidentifier)
   
         INSERT #TempResCFShadow (resid, revCount, mdpid)
         SELECT RES_UID, MAX(DELETED_REV_COUNTER), MD_PROP_UID
                           FROM MSP_RES_CUSTOM_FIELD_VALUES_SHADOW
                           GROUP BY RES_UID, MD_PROP_UID 
   
         DELETE 
         FROM MSP_RES_CUSTOM_FIELD_VALUES_SHADOW
           FROM MSP_RES_CUSTOM_FIELD_VALUES_SHADOW as originalTable, #TempResCFShadow AS newtable
         where originalTable.MD_PROP_UID = newtable.mdpid AND
         originalTable.RES_UID = newtable.resid AND
         originalTable.DELETED_REV_COUNTER <> newtable.revCount
   
           DROP TABLE #TempResCFShadow

   
   

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.