4

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.

  • 4
    Have you tried using the old CE option (querytraceon 9481) ? or database property-->Options --> Database scoped configurations --> Legacy Cardinality Estimation = ON. Also, can you post the actual xml execution plan (use pastebin and link it here) please ? A repro would be good to with relevant table structure. – Kin Shah Jun 23 '16 at 17:15
  • But that set it the same as set a.Column1 = @param1 – paparazzo Jun 23 '16 at 18:38
  • Have indexes fragmented? – paparazzo Jun 23 '16 at 18:40
  • Did you update statistics after migrating? – Erik Darling Jun 23 '16 at 18:53
  • @Paparazzi yes, an equivalent statement, its more about some auditing fields that have a few more cases in play to make sure one of the fields is changed before updating. To your other point, its not fragmentation. – acg Jun 23 '16 at 18:57
5

In SQL Server 2014 & up, new cardinality estimation logic was introduced.

From BOL :

The cardinality estimation logic, called the cardinality estimator, is re-designed in SQL Server 2014 to improve the quality of query plans, and therefore to improve query performance. The new cardinality estimator incorporates assumptions and algorithms that work well on modern OLTP and data warehousing workloads. It is based on in-depth cardinality estimation research on modern workloads, and our learnings over the past 15 years of improving the SQL Server cardinality estimator. Feedback from customers shows that while most queries will benefit from the change or remain unchanged, a small number might show regressions compared to the previous cardinality estimator.

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

Selectivity computation in SQL Server 2014 behaves the same as previous versions (and trace flag 4137 works as before) if the database compatibility level is set lower than 120, or if trace flag 9481 is active.

So my advise would be

  • For small number of queries revealing the problem, use QUERYTRACEON(9481) hint.
  • If you dont want to gamble, then just have the trace flag 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 :

9481 :Use when running SQL Server 2014 with the default database compatibility level 120. Trace flag 9481 forces the query optimizer to use version 70 (the SQL Server 2012 version) of the cardinality estimator when creating the query plan.

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 .

SELECT  name, value  
    FROM  sys.database_scoped_configurations  
    WHERE name = 'LEGACY_CARDINALITY_ESTIMATION'; 
-- if above is having value = 0, then set to ON
ALTER DATABASE
    SCOPED CONFIGURATION  
        SET LEGACY_CARDINALITY_ESTIMATION = ON;  
go  

For Trace Flag 4199, you can just do

ALTER DATABASE
    SCOPED CONFIGURATION  
        SET QUERY_OPTIMIZER_HOTFIXES = ON
| improve this answer | |
  • I still wonder why the new CE would go wonky after roughly 2000 iterations of this proc and be fine once we restart the process. When I have time, yeah right, I would like to turn on the new execution plan logging and see if anything shows up with this proc. Thanks again Kin! – acg Jun 24 '16 at 14:12

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.