Summary

Development systems running Microsoft SQL Server 2008 R2 experience blocked threads as a result of a call to RACacheAppCentricImpl.invalidateEntries. The problem occurs when users log in and when rules of any type are created. Deletes take as long as 30 minutes to execute. The system slows and eventually stalls.

HFix-7125 improves the performance of the invalidation query in the class RACacheAppCentricImpl.java by adding a SQL Server query optimizer hint, changing Join order, and using a non-clustered primary key for the tables pr_sys_appcache_entry and pr_sys_appcache_shortcut.

Details, HFix-7125

HFix-7125 is an optimized version of HFix-7038 for Microsoft SQL Server only that improves the performance of the invalidation queries delivered by HFix-7038.

HFix-7125 has two parts:

• HFix-7125.jar, downloadable from Hotfix Self-Service and installable by Update Manager
• HFix-7125.sql, obtainable from Pegasystems Global Customer Support (GCS)

When you download HFix-7125 from Hotfix Self-Service, the DL-####.zip that you receive contains the HFix-7125.jar file and the JAR files of its dependent hotfixes (Additional Related Hotfixes). You install the DL-####.zip file using Update Manager.

The HFix-7125.sql file that you receive from GCS contains the optimized script for SQL Server, which alters constraints for two tables: [dbname].pr_sys_appcache_entry and [dbname].pr_sys_appcache_shortcut

Installing HFix-7125

To install the HFix-7125.jar and the HFix-7125 SQL script:

1. Back up the system, as advised before installing any hotfix.
2. Go to Hotfix Self-Service, select HFix-7125, and download the DL file.
3. Install the DL file using the Update Manager, the menu choice Install Hotfix.
4. Do NOT select Perform Auto Commit. You will have a chance to commit the selected hotfixes after you have tested them.
5. Stop all server nodes.
6. Run the HFix-7125.sql script to effect these changes:
   ​Alter the two table constraints by dropping the existing table constraints and creating non-clustered constraints. Replace dbname with the actual database name.

   alter table [dbname].pr_sys_appcache_entry drop constraint pr_sys_appcache_entry_PK ;

   alter table [dbname].pr_sys_appcache_shortcut drop constraint pr_sys_appcache_shortcut_PK ;

   --Non-clustered constraint

   alter table [dbname].pr_sys_appcache_entry  add constraintpr_sys_appcache_entry_PK  primary key nonclustered (pzinskey);

   alter table [dbname].pr_sys_appcache_shortcut  add constraint pr_sys_appcache_shortcut_PK  primary key  nonclustered (pzinskey);

7. Restart all server nodes to apply the hotfix changes and the new table changes.

Be sure to test your system thoroughly. Contact GCS if you have any questions or concerns.

Additional Information

Atlas — Initial Database Tables (Data-Admin-DB-Table)