Cost plan data is slow on MSSQL

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

MSSQL environment. Performance degradation is observed when using the cost plan populate functionality on SQL Server (MSSQL). This may be on MUX Financials pages, Roadmaps or Hierachy

 

Expected Results: Reasonable execution time. 

 

Actual Results: In such case the cost plan population is taking approximately around 2.6 to 3 minutes, to refresh / re-populate this Cost Plan. If the Totals columns are removed from the cost plan, it takes around 45 seconds to refresh / populate. 

Environment

Release : 16.1.1, 16.1.2, 16.1.3

Resolution

This is DE71070, fixed in 16.2 and 16.1.3.2

• Recommended solution is to upgrade to 16.2 and 16.1.3.2

Workaround: 

• Cardinality change suggested on SQL Server (required testing on lower environment to ensure everything else works):
• LEGACY_CARDINALITY_ESTIMATION = ON;
• We recommend to switch this back once you upgrade to 16.2 and 16.1.3.2