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 ?