ROOT CAUSE: On March 6, teams were alerted regarding a message age alert in the ClaimDirector Service. A cartesian join was being used on a “dead” table, meaning a query joined a table where none of the data from the table was being used. The table execution plan changed based on volume in the dead table. As the database grows, the optimizer can make poor decisions on the execution path. Once a particular threshold is breached, the database optimizer can select an inefficient execution plan. This query took 37 seconds for 1 record. Once the cartesian join was removed in the code, the execution time was reduced to 10 to 20 ms.

CORRECTIVE ACTION: 

1. DBA team attempted to add 2 indexes. This did not improve performance.
2. Development removed a cartesian join from the code. This was tested and deployed, and messages began to be consumed.

PREVENTIVE ACTION ITEMS BY POINT OF FAILURE: