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.
Could you help me to optimize the query and reduce the cost?