Question

6
Replies
566
Views
John Bushey (JohnB381)
Pega

Pega
US
JohnB381 Member since 2016 9 posts
Pega
Posted: June 14, 2016
Last activity: June 23, 2016
Posted: 14 Jun 2016 11:57 EDT
Last activity: 23 Jun 2016 14:55 EDT
Closed

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?  

Thanks

John

Message was edited by: Marissa Rogers - added category

Data Integration
Moderation Team has archived post, This thread is closed to future replies. Content and links will no longer be updated. If you have the same/similar Question, please write a new Question.