MSSQL Project and Idea list very slow on Compatibility 130 with partitions

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

STEPS TO REPRODUCE:

1. Use a large MSSQL 2016 dataset with multiple partitions set up
2. Connect to Clarity as admin/admin
3. Go to security.caches and select Flush All
4. Now go to Home - Projects or Home - Ideas

Expected Results: The lists to open in a reasonable time (5-10 sec)

Actual Results: Project list and Idea list take anytime from 50 sec - 2 min to load for the first time. Subsequent load is 1-5 sec.

When setting Compatibility Mode on the SQL server back to 110, the list loads significantly faster (5-10 for customer and 25 sec for us) . 120 is also slow as 130. Only Compatibility level 130 is supported on Clarity 15.7.1

Workaround: None

Environment

Release : 15.7.1

Component : CA PPM APPLICATION

Resolution

This has been logged with Engineering as DE53689, closed as fixed in MSSQL 2017 CU21. This MSSQL 2017 CU21 release performed very well in our test, considerably faster than MSSQL 2016. We recommend upgrading to Clarity 15.8.1 with MSSQL 2017 CU21

Additional Information

Note: If you have a situation in which the Load Data Warehouse job becomes slower on Compatibility 110, it is okay to keep Clarity on 110 and Data Warehouse on 130 for the time being before the upgrade to SQL 2017