-3

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:

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?

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.