question

We have upgraded from SQL SERVER 2012 to 2014. After Upgrade we are facing a performance issue in our code. Till 2012 the below mentioned query was performing very well executed in 3 sec but in 2014 server the same query is taking more than 30 mins on the same Data. SQL Query: DECLARE @SDEK TABLE (COUNTERID INT IDENTITY(1,1), SDEK varchar(100), SourceColumnName varchar(50)) INSERT INTO @SDEK SELECT s.SDEK,CASE WHEN SourceFieldMappingid =1 THEN 'TxnCount' WHEN SourceFieldMappingid =2 THEN 'Amount' ELSE 'Categorical' END Sourcecolumnname --INTO tmp_SDEK FROM SDEKXRef s INNER JOIN SDEKDetails s1 on s1.SDEK=s.SDEK and s1.Archive=1 ; WITH TOP1_SDEK as ( SELECT TransactionDateID, SMPID, s.SDEK, SourceRecordID, CASE WHEN t.Sourcecolumnname ='TxnCount' THEN cast(s.TxnCount AS varchar(50)) WHEN Sourcecolumnname ='Amount' THEN cast (Amount AS varchar(50)) ELSE cast (Categorical as varchar(50)) END Sourcecolumnname, ROW_NUMBER() OVER (PARTITION BY SMPID, s.SDEK ORDER BY TransactionDateID DESC) as RowNum FROM dbo.SOURCEDATA_SDEK_HIST S INNER JOIN @SDEK t ON s.SDEK=t.sdek WHERE S.SMPID IN (SELECT DISTINCT SMPID FROM TellTaleResults) ) SELECT SMPID as tmp_SMPID,SDEK,Sourcecolumnname FROM TOP1_SDEK WHERE RowNum = 1 I changes compatibility mode to 2012(110) and after that same query perform well. And after that i revert back the compatibility setting to 120, and instead off table variable is used Table and after that That query work very well with 2014 server as well. So my question is if somebody know whether this is known issue of table variable in server 2014. and what could be the other solution to fix the issue.