Sql-server – Changing database compatibility from legacy CE to 120

cardinality-estimatesoptimizationquery-performancesql serversql server 2014

Just seeking an expert/practical advise from DBA point of view where one of our application DB running on SQL 2014 after migration had old DB compatibility level i.e 100.(SQL2008)

From DEV point of view all the testing has been done and they dont see much diff and want to move to prod based on their testing.

In our testing ,For certain process where we see slowness like in SP's we found the part of statement that was slow and added query traceon hint , something like below keeping compat to 120, which helps keeping performance stable

SELECT  [AddressID],
    [AddressLine1],
    [AddressLine2]
FROM Person.[Address]
WHERE [StateProvinceID] = 9 AND
    [City] = 'Burbank'
OPTION (QUERYTRACEON 9481);
GO

UPDATE- Editing question based on more findings-

Actually we found things getting worst for a table which calls scalar function within a computed column-

below is how that column looks

CATCH_WAY AS ([dbo].[fn_functionf1]([Col1])) PERSISTED NOT NULL

and part of query where it goes weird is somewhat looking like below

DELETE t2
   OUTPUT del.col1
          del.col2
          del.col3
   INTo #temp1
FROM #temp2 t2
INNER JOIN dbo.table1 tb1 on tb1.CATCH_WAY = ([dbo].[fn_functionf1](t2.[Col1])
AND t2.[col2] = tb1.[col2]
AND t3.[col3] = tb1.[col3]
AND ISNULL (t2.[col4],'') = ISNULL (tb1.[col4],'')

I know function is being called and is slow but the problem is with current compat i.e. 100 runs OK'ish slow but when changed to 120 it gets X100 times slow and if kept at 100 its X100 faster.
What is happening ?

Best Answer

This regression in performance usually happens because you already had a bad query that was considered "fast enough" under the old compat, but is super slow under the new compat, due to the weird/bad way it was written.

I'd get rid of the 2 functions in your DELETE statement's ON clause, by pre-computing them and storing them into your temp tables before the DELETE happens.

You could also try to rewrite the ISNULL parts, since ISNULL is also a function.

--new temp table to store table1
SELECT col2
    ,col3
    ,col4
    ,CATCH_WAY --this scalar function gets calculated here and saved into the temp table
INTO #tempTable1
FROM dbo.table1;

DELETE t2
   OUTPUT del.col1
          del.col2
          del.col3
   INTO #temp1
FROM #temp2 t2
JOIN #tempTable1 tb1
ON tb1.CATCH_WAY = ([dbo].[fn_functionf1](t2.[Col1]) --not a good idea
AND t2.[col2] = tb1.[col2]
AND t3.[col3] = tb1.[col3]
AND ISNULL (t2.[col4],'') = ISNULL (tb1.[col4],'') --not a good idea, the ISNULL "hides" the columns from the SQL Optimiser
Related Question