Release Notes › Known Issues › Tracking ID 571-43: Performance Problems on SQL Server CMSs

Tracking ID 571-43: Performance Problems on SQL Server CMSs

Under certain circumstances on SQL Server CMSs we have observed performance problems when running iConsole searches and reports. For example, users assigned to a Self-Exclude security model are more likely to suffer from this issue.

This issue is caused by a known problem in SQL Server that can occur when:

• A database query contains at least one JOIN clause.
• There are multiple columns in the WHERE clause.
• The SQL Server optimizer uses the ANTI SEMI JOIN operator to join data (this is a NOT EXISTS sub query)

DBAs can verify whether a CMS is suffering from this problem by analyzing a SHOW STATISTICS PROFILE query plan for the slow running query. Indicative symptoms are:

• Elements of the query plan show a large divergence between the Actual Row Count (showing the correct value) and the Estimated Row Count (highly underestimated; possibly estimated at one).
• Divergent row count estimates originate from an ANTI SEMI JOIN operator row.

In this situation, we recommend that you apply the fix described in Microsoft Knowledge Base article 2222998.