Context :
Small table, stored procedure to update table, first two thousand hits no problem. After those first hits it starts to crawl to a snails pace. This stored procedure has no problems in our current environment of SQL Server 2008 R2 with the calling C# app. In our new testing environment for SQL Server 2016 we see this problem. The offending section of the query deals with a where clauses that has four comparisons of a column not equaling a param for an update of the small table:
WHERE A.Column1 <> @param1 OR A.Column2 <> @param2 etc..
My assumption was parameter sniffing but have ruled that out with various query hints.
I have also tried:
- Changing to select not update
- Playing with degrees of parallelism (MAXDOP)
- Updating stats and recompiling, even doing it mid process
- Various changes to the indexes
- Resetting connection pooling in calling C# code after X amount of iterations
- Removing any transactions controls
The only change that seems to remedy the issue is to remove the where clause and push the logic to the setter:
SET A.Column1 = CASE
WHEN A.Column1 <> @param1
THEN @param1
ELSE A.Column1
END
I don't like having to change the code, yet I have found no other resources online that could answer why SQL Server 2016 would break.
Any help would be appreciated.
Best Answer
In SQL Server 2014 & up, new cardinality estimation logic was introduced.
From BOL :
Recently, we upgrade from SQL server 2012 to SQL server 2014 and got hit by the new Cardinality estimator short coming - queries were timing out, cpu pegging close to 100%.
After much troubleshooting, updating stats, rebuilding indexes, doing query plan analysis, we figured out that changing compatibility level to sql 2012 works well.
Paul White explains - Cardinality Estimation for Multiple Predicates
So my advise would be
TF9481
as a start-up parameter, so it gets persisted during server restarts.Note: Enabling
TF 9481
, you dont need to set the compatibility level of the database to a lower level.From KB2801413 :
As a side note, along with proper testing - you also want to look into
TF4199
(Think of it as a master key to turn on every fix for the query optimizer). TF4199 behavior changes with sql server 2016.. TF4199 helped in my environment at lot and is by default ON for all new installations.In SQL Server 2016, you dont need to turn on the trace flag
9481
.For Trace Flag
4199
, you can just do