Summary

Deleting old messages from large SQL tables may generate a long running query and have a negative impact on the performance of the SQL server.

Long running queries show high CPU utilization on the SQL server and, when SQL is profiled, the expensive query is "delete from search_message where message_id not in (select message_id from search_activity_messages union select message_id from search_activity_messages_log)"

Note: We recommend adding the indexes below even if you have not yet experienced performance issues.

Resolution

Add two indexes with the syntax below to speed up the query.

For Microsoft SQL Servers

• create index search_activity_msg_mid_ix on search_activity_messages
  (
  message_id asc
  )
  GO
• create index search_activity_msg_log_mid_ix on search_activity_messages_log
  (
  message_id asc
  )
  GO

For Oracle Servers

• create index search_activity_msg_mid_ix on search_activity_messages
  (
  message_id asc
  )
  /
• create index search_activity_msg_log_mid_ix on search_activity_messages_log
  (
  message_id asc
  )
  /