Please see: https://community.pega.com/node/2326326

At least in MS SQL world, this the fact that Pega ships all tables with NONCLUSTERED Indexes is a REALLY BAD idea specifically because of HEAP Fragmentation due to Forwarded Records. This is specifically noticed in pr_sys_locks table as this table gets fragmented by the hour in our environment and the table can grow up to 100 MB in size just in one day and scans became VERY expensive. This is when the table only contained less than 50 rows of data.

There are numerous articles which describe why this is a bad idea:

• https://www.brentozar.com/archive/2016/07/fix-forwarded-records/
• https://www.sqlskills.com/blogs/paul/forwarding-and-forwarded-records-and-the-back-pointer-size/
• https://www.red-gate.com/simple-talk/databases/sql-server/database-administration-sql-server/sql-server-heaps-and-their-fragmentation/

As for solution suggested here (MS SQL Clustered Index | Support Center (pega.com) to add non-clusterd indexes on this table with INCLUDE covering indexes: SQL Server execution plans will not consider non-clustered index because the size of the pr_sys_locks table is very small, the plan will keep choosing a scan on the table because its more efficient to do a scan than a seek on the NCI.

We can't keep rebuilding the pr_sys_locks table every hour on a production environment, this is really bad design and needs to be corrected by Pega. We spent weeks troubleshooting this issue and finally added a cluster index which resolved all issues on pr_sys_locks.