Performance and capacity planning best practices
This Office product will reach end of support on October 10, 2017. To stay supported, you will need to upgrade. For more information, see , Resources to help you upgrade your Office 2007 servers and clients.
Topic Last Modified: 2016-11-14
This article contains two white papers:
Performance testing white paper
Performance and capacity planning best practices white paper
Performance testing white paper
This white paper contains a description of an actual performance testing lab for Microsoft Office Project Server 2007.
The first chapter (“Running a Project Server 2007 Performance Test Lab”), describes how to run performance tests against a Office Project Server 2007 deployment by using Visual Studio Team System 2008 and a set of community-based tools built for the purpose.
The second chapter (“Test Environment”), documents the specifics of the lab environment that was used for the tests we ran for collecting data for this white paper.
The third chapter (“Test Results”), describes in detail the test scenarios that were conducted, and it summarizes the data collected for every scenario.
The paper is available from the Microsoft Download Center.
Download size: 4 MB
Performance and capacity planning best practices white paper
The purpose of this guide is to extend the Microsoft Office Project Server 2007 Performance Testing Lab white paper by providing best practices and recommendations.
This paper examines a wide array of system objects, including:
Platform-related objects — including the farm, shared service providers, application pools, web applications, databases, disks, network, memory, CPU, logs, and performance counters
Data-related objects — including projects, tasks, assignments, resources, custom fields, and security
User-related objects — including localization, workloads, queue job processor threads, and interface feeds
For each system object, the following information is provided:
Definition — The definition of the object
Guidelines for acceptable performance — The best practices and supporting statements
Calculation factors — The performance and scalability calculations to support the best practices
Scope of impact — A list of objects affected in the system when best practices are not implemented
The paper is available from the Microsoft Download Center.
Download size: 1.0 MB
Project system Health Report script
The following script from the "best practices" white paper is provided here to makes it easy for you to copy and paste this query into SQL Server query analyzer for running a report on system health and to determine scalar depth across the data profile.
/*
-- **************************************************** SUMMARY NOTES ****************************************************** --
PROJECT SYSTEM HEALTH REPORT V2.0
Extracts a series of data points from Draft & Reporting databases to qualify the data in a Project system.
Run the report against your Draft database and save the results to file, send the resulting .CSV file with
Column Headers to Microsoft (We are working on making the data analysis public as a series of reports to
remove the need to send data to Microsoft)
CUSTOMIZATION
1. Set local variable @DRAFT_DB_NAME with your DRAFT database name.
1. Set local variable @PUBLISHED_DB_NAME with your PUBLISHED database name.
3. Set local variable @REPORTING_DB_NAME with your REPORTING database name (and SERVER if on a separate server).
4. Set local variable @ERADICATE_PROJ_NAME to 1 if require masking project name for privacy information
PRIVACY/INFORMATION PROTECTION NOTES
1. Report contains data that can be used to quantify the amount of project work in the org,
please review this for sensitivity prior to sending the data to Microsoft
CHANGELOG
01/05/2009 pmc: Prepared for Customer Release, some p
01/05/2009 pmc: Changes made for Project Server 14 Alpha (Project Server 2010)
05/11/2009 EPMGP: Added values to incorporate Quality Assurance data points for the Health Check Tool
*/
-- ************************************************ BEGIN DECLARE VARIABLES ************************************************ --
DECLARE @SQL_SELECT nvarchar(max); -- Variable to contain SELECT statements
DECLARE @SQL_JOINS nvarchar(max); -- Variable to contain JOIN statements
DECLARE @PS_TASK_START varchar(20); -- Variable to swith column name between PS2007 and PS2010 implementation
DECLARE @PS_TASK_FINISH varchar(20); -- Variable to swith column name between PS2007 and PS2010 implementation
DECLARE @DRAFT_DB_NAME varchar(100); -- Variable to set the DRAFT database name
DECLARE @PUBLISHED_DB_NAME varchar(100); -- Variable to set the PUBLISHED database name
DECLARE @REPORTING_DB_NAME varchar(100); -- Variable to set the REPORTING database name
DECLARE @ERADICATE_PROJ_NAME CHAR(1); -- Variable to switch between displaying project name or masking the value
---------------------------------------------------- END DECLARE VARIABLES --------------------------------------------------
-- *********************************************** BEGIN SET VARIABLE VALUES *********************************************** --
SET @DRAFT_DB_NAME = 'BLANK_Draft'; -- Set local variable @DRAFT_DB_NAME with your DRAFT database name
SET @PUBLISHED_DB_NAME = 'BLANK_Published'; -- Set local variable @PUBLISHED_DB_NAME with your PUBLISHED database name
SET @REPORTING_DB_NAME = 'BLANK_Reporting'; -- Set local variable @REPORTING_DB_NAME with your REPORTING database name
SET @ERADICATE_PROJ_NAME = 0; -- Set local variable to 1 to mask project name for privacy or default to 0
SET @PS_TASK_START = 'TASK_START_DATE'; -- Set local variable to TASK_START_DATE for PS2007 and REM PS2010
SET @PS_TASK_FINISH = 'TASK_FINISH_DATE'; -- Set local variable to TASK_FINISH_DATE for PS2007 and REM PS2010
--SET @PS_TASK_START = 'TASK_SCHED_START'; -- Set local variable to TASK_SCHED_START for PS2010 and REM PS2007
--SET @PS_TASK_FINISH = 'TASK_SCHED_FINISH'; -- Set local variable to TASK_SCHED_FINISH for PS2010 and REM PS2007
-------------------------------------------------- END SET VARIABLE VALUES ---------------------------------------------------
SET @SQL_SELECT = N'use [' + @DRAFT_DB_NAME + N']
SET NOCOUNT ON;
WITH ProjHierarchy
AS (SELECT Proj.PARENT_PROJ_UID, 1 AS DEPTH
FROM dbo.MSP_PROJ_HIERARCHIES AS Proj
UNION ALL
SELECT Proj.PARENT_PROJ_UID, Hier.DEPTH+1
FROM dbo.MSP_PROJ_HIERARCHIES AS Proj
INNER JOIN ProjHierarchy AS Hier
ON Proj.CHILD_PROJ_UID = Hier.PARENT_PROJ_UID)
SELECT
Proj.PROJ_UID AS [PRJ: PROJ ID]
,CASE ' + @ERADICATE_PROJ_NAME + N' WHEN 0 THEN Proj.PROJ_NAME ELSE N''*** NAME ERADICATED ***''END AS [PRJ: NAME]
,CASE(Proj.PROJ_TYPE)
WHEN 0 THEN N''Project''
WHEN 1 THEN N''Template''
WHEN 2 THEN N''Global''
WHEN 3 THEN N''Resource Global''
WHEN 4 THEN N''LightWeightProject''
WHEN 5 THEN N''Inserted Project''
WHEN 6 THEN N''Master Project''
WHEN 100 THEN N''New Project''
WHEN 101 THEN N''New Template''
WHEN 102 THEN N''New Global''
WHEN 103 THEN N''New Resource Global''
WHEN 1000 THEN N''Inactive Project''
WHEN 1001 THEN N''Inactive Template''
WHEN 1002 THEN N''Inactive Global''
WHEN -1 THEN N''Void''
ELSE ''Unknown''
END AS [PRJ: TYPE]
,ISNULL(CollabRes.RES_NAME, Proj.PROJ_PROP_AUTHOR) AS [PRJ: AUTHOR]
,ISNULL(CONVERT(VARCHAR(10), Proj.CREATED_DATE,111), ''0000/00/00'') AS [PRJ: CREATED]
,ISNULL(CONVERT(VARCHAR(10), Proj.PROJ_INFO_START_DATE,111), ''0000/00/00'') AS [PRJ: START_DATE]
,ISNULL(CONVERT(VARCHAR(10), Proj.PROJ_INFO_FINISH_DATE,111), ''0000/00/00'') AS [PRJ: FINISH_DATE]
,DATEDIFF(mm, Proj.PROJ_INFO_START_DATE,Proj.PROJ_INFO_FINISH_DATE) AS [PRJ: DURATION (Mnth)]
,ISNULL(CONVERT(VARCHAR(10), Proj.MOD_DATE,111), ''0000/00/00'') AS [Project Modified]
,ISNULL(CONVERT(VARCHAR(10), Proj.PROJ_INFO_STATUS_DATE,111), ''0000/00/00'') AS [PRJ: STATUSED]
,ISNULL(Task.TASK_PCT_COMP, 0) AS [PRJ: %COMPLETE]
,CASE
ISNULL(Proj.PROJ_SESSION_UID,N''00000000-0000-0000-0000-000000000000'')
WHEN N''00000000-0000-0000-0000-000000000000''
THEN 0 ELSE DATEDIFF(d,Proj.PROJ_CHECKOUTDATE,getdate())
END AS [PRJ: CHECKED_OUT(Days)]
,CAST(Task.TASK_ACT_WORK / 60000 AS DECIMAL(10,2)) AS [PRJ: TOTAL_ACTUAL_WORK(Hrs)]
,CAST(Task.TASK_REM_WORK / 60000 AS DECIMAL(10,2)) AS [PRJ: TOTAL_REMAINING_WORK(Hrs)]
,ISNULL(LinkData.[Cross Project Links],0) AS [PRJ: #_CROSS_PROJECT_LINKS]
,ISNULL(HierSummaryData.[Number of SubProjects],0) AS [PRJ: #_SUB_PROJECTS]
,ISNULL(HierSummaryData.[SubProject Depth],0) AS [PRJ: SUB_PROJECT_DEPTH]
,ISNULL(AllTaskData.[Task Count],0) AS [TSK: TOTAL_TASK_COUNT]
,ISNULL(RecentTasks.[Recent Changes],0) AS [TSK: TOTAL_RECENT_CHANGE]
,ISNULL(CONVERT(DECIMAL(18,0), CONVERT(FLOAT, RecentTasks.[Recent Changes])/CONVERT(float, AllTaskData.[Task Count])*100), 0) AS [TSK: %_CHANGED]
,ISNULL(SRA.[SRA Count], 0) AS [TSK: SRA_COUNT]
,ISNULL(LeafTaskData.[Leaf Task Count],0) AS [TSK: LEAF_COUNT]
,ISNULL(LeafTaskData.[Max Leaf Calendar Duration (Days)], 0) AS [TSK: MAX_LEAF_TASK_CALENDAR_DURATION(Days)]
,ISNULL(LeafTaskData.[Average Leaf Calendar Duration (Days)], 0) AS [TSK: AVERAGE_LEAF_TASK_CALENDAR_DURATION(Days)]
,ISNULL(LeafTaskData.[Total Leaf Task Ignoring ResCalendar], 0) AS [TSK: TOTAL_LEAF_TASK_IGNORING_RES_CALENDAR]
,ISNULL(LinkData.[Total Number of Links],0) AS [TSK: TOTAL_NUMBER_OF_LINKS]
,ISNULL(AssnDataAggregated.[Average Resources on Assigned Tasks],0) AS [ASN: AVG_RESOURCES_ON_ASSIGNMENTS]
,ISNULL(AssnDataAggregated.[Total Number of Assignments],0) AS [ASN: #_TOTAL_ASSIGNMENTS]
,ISNULL(AssnDataAggregated.[Number of Tasks with Assignments],0) AS [ASN: TOTAL_TASK_WITH_ASSIGNMENTS]
,ISNULL(RealData.[Actual Count],0) AS [ASN: TOTAL_ASNBYDAY_COUNT]
,ISNULL(ProjCF.[Number of Project Custom Field Values],0) AS [CF: PRJ_CF_VALUES]
,ISNULL(ProjLCF.[Number of Local Custom Field Definitions],0) AS [CF: PRJ_LOCAL_CF_DEFINITIONS]
,ISNULL(TaskCFAggregated.[Total Task Custom Fields],0) AS [CF: TOTAL_TASK_CFs]
,ISNULL(TaskCFAggregated.[Max Task Custom Fields],0) AS [CF: MAX_TASK_CFs]
,ISNULL(TaskCFAggregated.[Average Task Custom Fields],0) AS [CF: AVG_TASK_CFs]
,ISNULL(TaskCFAggregated.[Number of Tasks with Custom Fields],0) AS [CF: #_TASK_WITH_ CFs]
,ISNULL(AssnCFAggregated.[Total Assn Custom Fields],0) AS [CF: TOTAL_ASN_CFs]
,ISNULL(AssnCFAggregated.[Max Assn Custom Fields],0) AS [CF: MAX_ASN_CFs]
,ISNULL(AssnCFAggregated.[Average Assn Custom Fields],0) AS [CF: AVG_ASN_CFs]
,ISNULL(AssnCFAggregated.[Number of Assn with Custom Fields],0) AS [ASN: #_ASN_WITH_CFs]
,ISNULL(TaskBaseAggregated.[Number of Baselines],0) AS [BSL: #_BASELINES]
,ISNULL(TaskBaseAggregated.[Total Task Baseline Rows],0) AS [BSL: TOTAL_TSK_BASELINE]
,ISNULL(AssnBaseAggregated.[Total Assn Baseline Rows],0) AS [BSL: TOTAL_ASN_BASELINE]
,ISNULL(ResBaseAggregated.[Total Resource Baseline Rows],0) AS [BSL: TOTAL_RES_BASELINE]
,ISNULL(Resources.[Total Resources],0) AS [RES: TOTAL_TEAM]
,ISNULL(Resources.[Enterprise Resources],0) AS [RES: TOTAL_ENT_TEAM]
,ISNULL(ActiveResAggregated.[Active Resources],0) AS [RES: TOTAL_ACTIVE]
,ISNULL(CONVERT(VARCHAR(10), Proj.WPROJ_LAST_PUB,111), ''0000/00/00'') AS [PWS: PUBLISHED]
,ISNULL(Collab.WPROJ_STS_SUBWEB_NAME, 0) AS [PWS: NAME]
,ISNULL(Collab.PROJ_TOTAL_DOC_COUNT, 0) AS [PWS: DOC_COUNT]
,ISNULL(Collab.PROJ_ACTIVE_ISSUE_COUNT, 0) AS [PWS: ISSUE_COUNT]
,ISNULL(Collab.PROJ_ACTIVE_RISK_COUNT, 0) AS [PWS: RISK_COUNT]
,ISNULL(CONVERT(DECIMAL(18,2),(CAST(AssnDataAggregated.[Total Number of Assignments] AS REAL) / AllTaskData.[Task Count])),0) AS [RATIO: ASN:TSK]
,ISNULL(CONVERT(DECIMAL(18,2),(CAST(AssnCFAggregated.[Total Assn Custom Fields] AS REAL) / AssnDataAggregated.[Total Number of Assignments])),0) AS [RATIO: ASNCF:ASN]
,ISNULL(CONVERT(DECIMAL(18,2),(CAST(TaskCFAggregated.[Total Task Custom Fields] AS REAL) / AllTaskData.[Task Count])),0) AS [RATIO: TSKCF:TSK]
,CONVERT(VARCHAR(10), GetDate(),111) AS [RECORDED_DATE]'
SET @SQL_JOINS = N'
FROM dbo.MSP_PROJECTS AS Proj WITH(NOLOCK)
-- ************ Project Summary Task
INNER JOIN dbo.MSP_TASKS AS Task WITH(NOLOCK)
ON (Proj.PROJ_UID = Task.PROJ_UID AND Task.TASK_OPTINDX = 1) -- Use the Project Summary Task for rollup values
-- ************ Count the number of resource assignments on summary tasks
LEFT OUTER JOIN (Select MP.PROJ_UID AS PROJ_UID, COUNT(DISTINCT MA.Task_name) AS [SRA Count]
from msp_assignments as MA inner join msp_projects as MP on MA.Proj_uid=MP.proj_uid
where MA.task_uid in (select task_uid from msp_tasks where task_is_summary=1)
GROUP BY MP.PROJ_UID) AS SRA
ON (PROJ.PROJ_UID = SRA.PROJ_UID)
-- ************ Get the Project Manager Name
LEFT OUTER JOIN ' + @PUBLISHED_DB_NAME + N'.dbo.MSP_RESOURCES As CollabRes
ON Proj.PROJ_PROP_AUTHOR = CollabRes.WRES_ACCOUNT
-- ************ Get the rowcount from our (usually) largest table
LEFT OUTER JOIN (SELECT Assn.ProjectUID as PROJ_UID,COUNT(*) + 1 AS [Actual Count]
FROM ' + @REPORTING_DB_NAME + N'.dbo.MSP_EpmAssignmentByDay AS Assn
GROUP BY Assn.ProjectUID) AS RealData
ON (Proj.PROJ_UID = RealData.PROJ_UID)
-- ************ Project Workspace data from PublishedDB
LEFT OUTER JOIN ' + @PUBLISHED_DB_NAME + N'.dbo.MSP_PROJECTS AS Collab
ON (Proj.PROJ_UID = Collab.PROJ_UID)
-- ************ Project Custom Field Data
LEFT OUTER JOIN (SELECT pcf.PROJ_UID,COUNT(*) AS [Number of Project Custom Field Values]
FROM dbo.MSP_PROJ_CUSTOM_FIELD_VALUES AS pcf WITH(NOLOCK)
GROUP BY pcf.PROJ_UID) AS ProjCF
ON (Proj.PROJ_UID = ProjCF.PROJ_UID)
-- ************ Project Local Custom Fields
LEFT OUTER JOIN (SELECT plcf.PROJ_UID
,COUNT(*) AS [Number of Local Custom Field Definitions]
FROM dbo.MSP_PROJECT_CUSTOM_FIELDS AS plcf WITH(NOLOCK)
GROUP BY plcf.PROJ_UID) AS ProjLCF
ON (Proj.PROJ_UID = ProjLCF.PROJ_UID)
-- ************ Project Hierarchy Below each Project
LEFT OUTER JOIN (SELECT HierData.PROJ_UID
,COUNT(HierData.DEPTH) AS [Number of SubProjects]
,MAX(HierData.DEPTH) AS [SubProject Depth]
FROM (SELECT Proj.PROJ_UID
,Hier.DEPTH
FROM ProjHierarchy as Hier
INNER JOIN dbo.MSP_PROJECTS AS Proj WITH(NOLOCK)
ON Hier.PARENT_PROJ_UID = Proj.PROJ_UID) AS HierData
GROUP BY HierData.PROJ_UID) AS HierSummaryData
ON (Proj.PROJ_UID = HierSummaryData.PROJ_UID)
-- ************ Project resource data
LEFT OUTER JOIN (SELECT Pres.PROJ_UID
,COUNT(*) AS [Total Resources]
,SUM(CAST(Pres.RES_IS_ENTERPRISE_RESOURCE AS INT)) AS [Enterprise Resources]
,SUM(CAST(Pres.RES_BOOKING_TYPE AS INT)/2) AS [Proposed Resources]
FROM dbo.MSP_PROJECT_RESOURCES AS Pres WITH(NOLOCK)
WHERE Pres.RES_ID > 0 -- Ignore internal resources
GROUP BY Pres.PROJ_UID) AS Resources
ON (Proj.PROJ_UID = Resources.PROJ_UID)
-- ************ Resources with Assignments
LEFT OUTER JOIN (SELECT ActiveRes.PROJ_UID
,COUNT(*) AS [Active Resources]
FROM (SELECT Assn.PROJ_UID
,Assn.RES_UID
FROM dbo.MSP_ASSIGNMENTS AS Assn WITH(NOLOCK)
INNER JOIN dbo.MSP_PROJECT_RESOURCES AS Pres WITH(NOLOCK)
ON (Assn.PROJ_UID = Pres.PROJ_UID AND Assn.RES_UID = Pres.RES_UID)
WHERE Pres.RES_ID > 0 -- Ignore internal resources
GROUP BY Assn.PROJ_UID, Assn.RES_UID) AS ActiveRes
GROUP BY ActiveRes.PROJ_UID) AS ActiveResAggregated
ON (Proj.PROJ_UID = ActiveResAggregated.PROJ_UID)
-- ************ Leaf task data
LEFT OUTER JOIN (SELECT Task.PROJ_UID
,COUNT(*) AS [Leaf Task Count]
-- IMPLEMENTATION NOTE - Use _SCHED_ below for PS2010, Ignores User Scheduled dates
,MAX(DATEDIFF(d,Task.' + @PS_TASK_START + N', Task.' + @PS_TASK_FINISH + N')) AS [Max Leaf Calendar Duration (Days)]
,AVG(DATEDIFF(d,Task.' + @PS_TASK_START + N', Task.' + @PS_TASK_FINISH + N')) AS [Average Leaf Calendar Duration (Days)]
,SUM(CAST(TASK_IGNORES_RES_CAL AS INT)) AS [Total Leaf Task Ignoring ResCalendar]
FROM dbo.MSP_TASKS AS Task WITH(NOLOCK)
WHERE Task.TASK_IS_SUMMARY = 0x0 -- Ignore summary tasks
AND Task.TASK_IS_MILESTONE = 0x0 -- Ignore milestones
AND Task.TASK_IS_SUBPROJ = 0x0 -- Ignore subprojects
GROUP BY Task.PROJ_UID) AS LeafTaskData
ON (Proj.PROJ_UID = LeafTaskData.PROJ_UID)
-- ************ All visible tasks in the project
LEFT OUTER JOIN (SELECT Task.PROJ_UID
,COUNT(*) AS [Task Count]
FROM dbo.MSP_TASKS AS Task WITH(NOLOCK)
WHERE Task.TASK_OPTINDX > 1
GROUP BY Task.PROJ_UID) AS AllTaskData
ON (Proj.PROJ_UID = AllTaskData.PROJ_UID)
-- ************ Task data changed on Last Project saved
LEFT OUTER JOIN (SELECT Task.PROJ_UID
,COUNT(*) AS [Recent Changes]
FROM dbo.MSP_TASKS AS Task WITH(NOLOCK)
INNER JOIN dbo.MSP_PROJECTS AS Proj WITH(NOLOCK)
ON (Task.PROJ_UID = Proj.PROJ_UID AND Task.MOD_REV_COUNTER = Proj.MOD_REV_COUNTER)
WHERE Task.TASK_OPTINDX > 1
GROUP BY Task.PROJ_UID) AS RecentTasks
ON (Proj.PROJ_UID = RecentTasks.PROJ_UID)
-- ************ Task Custom Field Data
LEFT OUTER JOIN (SELECT TaskCF.PROJ_UID
,MAX(TaskCF.[Task CF Count]) AS [Max Task Custom Fields]
,AVG(TaskCF.[Task CF Count]) AS [Average Task Custom Fields]
,SUM(TaskCF.[Task CF Count]) AS [Total Task Custom Fields]
,COUNT(*) AS [Number of Tasks with Custom Fields]
FROM (SELECT TCF.PROJ_UID
,TCF.TASK_UID
,COUNT(*) AS [Task CF Count]
FROM dbo.MSP_TASK_CUSTOM_FIELD_VALUES AS TCF WITH(NOLOCK)
GROUP BY TCF.PROJ_UID, TCF.TASK_UID) AS TaskCF
GROUP BY TaskCF.PROJ_UID) AS TaskCFAggregated
ON (Proj.PROJ_UID = TaskCFAggregated.PROJ_UID)
-- ************ Link Data
LEFT OUTER JOIN (SELECT Links.PROJ_UID
,COUNT(*) AS [Total Number of Links]
,SUM(CAST(LINK_IS_CROSS_PROJ AS INT)) AS [Cross Project Links]
FROM dbo.MSP_LINKS AS Links
GROUP BY Links.PROJ_UID) AS LinkData
ON (Proj.PROJ_UID = LinkData.PROJ_UID)
-- ************ Task Baseline Data
LEFT OUTER JOIN (SELECT TaskBaseData.PROJ_UID
,COUNT(*) AS [Number of Baselines]
,SUM(TaskbaseData.[Task Baseline Rows]) AS [Total Task Baseline Rows]
FROM (SELECT TaskBase.PROJ_UID
,TaskBase.TB_BASE_NUM AS [Baseline]
,COUNT(*) AS [Task Baseline Rows]
FROM dbo.MSP_TASK_BASELINES AS TaskBase WITH(NOLOCK)
GROUP BY TaskBase.PROJ_UID, TaskBase.TB_BASE_NUM) AS TaskBaseData
GROUP BY TaskBaseData.PROJ_UID) AS TaskBaseAggregated
ON (Proj.PROJ_UID = TaskBaseAggregated.PROJ_UID)
-- ************ Assignment Baseline Data
LEFT OUTER JOIN (SELECT AssnBaseData.PROJ_UID
,COUNT(*) AS [Total Assn Baseline Rows]
FROM dbo.MSP_ASSIGNMENT_BASELINES AS AssnBaseData WITH(NOLOCK)
GROUP BY AssnBaseData.PROJ_UID) AS AssnBaseAggregated
ON (Proj.PROJ_UID = AssnBaseAggregated.PROJ_UID)
-- ************ Resource Baseline Data
LEFT OUTER JOIN (SELECT ResBaseData.PROJ_UID
,COUNT(*) AS [Total Resource Baseline Rows]
FROM dbo.MSP_PROJECT_RESOURCE_BASELINES AS ResBaseData WITH(NOLOCK)
GROUP BY ResBaseData.PROJ_UID) AS ResBaseAggregated
ON (Proj.PROJ_UID = ResBaseAggregated.PROJ_UID)
-- ************ Assignment Data Summary
LEFT OUTER JOIN (SELECT AssnTaskData.PROJ_UID
,AVG(AssnTaskData.[Assignment Count]) AS [Average Resources on Assigned Tasks]
,SUM(AssnTaskData.[Assignment Count]) AS [Total Number of Assignments]
,COUNT(*) AS [Number of Tasks with Assignments]
FROM (SELECT Assn.PROJ_UID
,Assn.TASK_UID
,COUNT(*) AS [Assignment Count]
FROM dbo.MSP_ASSIGNMENTS AS Assn WITH(NOLOCK)
GROUP BY Assn.PROJ_UID, Assn.TASK_UID) AS AssnTaskData
GROUP BY AssnTaskData.PROJ_UID) AS AssnDataAggregated
ON (Proj.PROJ_UID = AssnDataAggregated.PROJ_UID)
-- ************ Assignment Custom Field Data
LEFT OUTER JOIN (SELECT AssnCF.PROJ_UID
,MAX(AssnCF.[Assn CF Count]) AS [Max Assn Custom Fields]
,AVG(AssnCF.[Assn CF Count]) AS [Average Assn Custom Fields]
,SUM(AssnCF.[Assn CF Count]) AS [Total Assn Custom Fields]
,COUNT(*) AS [Number of Assn with Custom Fields]
FROM (SELECT ACF.PROJ_UID
,ACF.ASSN_UID
,COUNT(*) AS [Assn CF Count]
FROM dbo.MSP_ASSN_CUSTOM_FIELD_VALUES AS ACF WITH(NOLOCK)
GROUP BY ACF.PROJ_UID, ACF.ASSN_UID) AS AssnCF
GROUP BY AssnCF.PROJ_UID) AS AssnCFAggregated
ON (Proj.PROJ_UID = AssnCFAggregated.PROJ_UID)
ORDER BY
[CF: TOTAL_ASN_CFs]
,[CF: TOTAL_TASK_CFs]
,[PRJ: DURATION (Mnth)]
'
EXEC (@SQL_SELECT + @SQL_JOINS)