JohnB381 Member since 2016 9 posts
Posted: 4 years ago
Last activity: 4 years 4 months ago

MS SQL Clustered Index

I've been having issues with deadlocks on the pr_sys_locks table in 7.1.9.  running on MS SQL Server.    Unlike Oracle, SQL Server will pick a victim for the deadlock and kill the query which leads to application problems unless you have logic to retry.    After troubleshooting the issue we discovered that the pr_sys_locks table did not have a clustered index which lead the query optimizer to choose a poor query plan.    After adding a clustered index on pzInsKey the deadlocks stopped.  SQL Server best practice states that in most cases you should have one clustered index on each table.     Most if not all of the OOTB shipped Pega tables do not have a clustered index.   I'm about to add some clustered indexes on a few important tables to try fixing some query alerts.  I'm inclined to add them to more tables but I don't want to blindly make changes that could have a negative impact.

    Why is PRPC not following best practice?    What's the benefit of shipping without clustered indexes?

    Is this a known issue?

    What are other customers experiencing/doing?  



Message was edited by: Marissa Rogers - added category

Data Integration
Moderation Team has archived post
Share this page LinkedIn