Setting Cardinality Estimator in SQL Server 2016 for performance & using old CE of SQL Server 2012

Question

Post upgrade to SQL Server 2016, we saw a lot of I/O issues followed by slowness which affected the whole system.

Upon investigation, we found that top queries are delete statements.

Execution plans for delete statements:

• https://www.brentozar.com/pastetheplan/?id=BJVUG3cFN
• https://www.brentozar.com/pastetheplan/?id=B1trH3cYE

To make the system work at an acceptable rate, the legacy cardinality was turned on, which improved overall performance and brought back the situation to normal.

LEGACY_CARDINALITY_ESTIMATION=ON

Could you help me to optimize the query and reduce the cost?

Answer 1

Kiran, It is not possible for others to do query tuning for you.

What was the previous execution plan and is the plan which you sent is the current plan?

Also, have you have table spools, which indicate that your query can use some indexes (they may be already there, in that case, change query) or create a new one to support it. (Overindexing is quite bad as well).

Additionally, there can be so many different factors from statistics to how the new server is configured. Very difficult to answer with the limited information.