SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col25... FROM Tabl1 WHERE ( Col30 IS NOT NULL AND Col28 <> @P0 AND DATEDIFF( dd, Col31, CAST(SUBSTRING(@P1, 1, 4) + '-' + SUBSTRING(@P2, 5, 2) + '-' + SUBSTRING(@P3, 7, 2) + 'T00:00:00.000' AS DATETIME)) <= @P4 AND DATEDIFF( dd, Col31, CAST(SUBSTRING(@P5, 1, 4) + '-' + SUBSTRING(@P6, 5, 2) + '-' + SUBSTRING(@P7, 7, 2) + 'T00:00:00.000' AS DATETIME)) >= @P8 ) AND Col27 = @P9 ORDER BY 18 ASC, 31 DESC, 30 ASC;
The above example query is from third party application. I have no control over the application. The query is more like user query.
The query uses an index seek but it has a key lookup for 20 columns.
When I add 20 included columns in the non-clustered index, the query does not seek the index anymore - it uses a non-clustered index scan. The non-clustered index scan resolves the deadlock, but CPU usage is 15% higher than using index seek + key lookup.
Index keys are Col30,Col28,Col27,Col31,ClusteredIndexKey and 20 columns are included columns.
I am using SQL Server 2016 SP2.
Now, I enabled query store in prod. I see 3 plan_id, One of them failed. Triangle sign, The other two has the same query execution plan but they have different plan_id. One takes longer than the other one. Any ideas?