question

navneethbti avatar image
navneethbti asked

SQL SERVER 2014 Performance issue with table variable.

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.
performancesql server 2014
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Have you compared the execution plans between the two compatibility modes?
2 Likes 2 ·
You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
0 Likes 0 ·
srutzky avatar image
srutzky answered
You should probably read through the following resources * [Temporary Tables in Stored Procedures][1] (Paul White blog) * [Temporary Table Caching Explained][2] (Paul White blog; continuation of blog noted above) * [Cardinality Estimation][3] (MSDN documentation) You should also probably test switching the Table Variable into a Local Temporary Table (i.e. `#TableName`) as those are _generally_ better for JOINing anyway. [1]: http://sqlblog.com/blogs/paul_white/archive/2012/08/15/temporary-tables-in-stored-procedures.aspx [2]: http://sqlblog.com/blogs/paul_white/archive/2012/08/17/temporary-object-caching-explained.aspx [3]: https://msdn.microsoft.com/en-us/library/dn600374(v=sql.120).aspx
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

In addition to the advice above. Have you tried applying the latest SP/CU. A few fixes have been resolved in and around TV's https://support.microsoft.com/en-us/kb/2952444
2 Likes 2 ·
+1 to @sp_lock, particularly given MS's recent re-iteration of their advice to now apply CUs as you would normally apply SPs... https://blogs.msdn.microsoft.com/sqlreleaseservices/announcing-updates-to-the-sql-server-incremental-servicing-model-ism/
1 Like 1 ·
@ThomasRushton Not seen that article.. Let us all hope it doesn't bite us in the a$$ like index rebuilds or AG sync issue :-)
1 Like 1 ·
Thanks for the information shared. I am going to ask my DBA to install CU4 on 2014 server. Hope this will fix this issue.
0 Likes 0 ·
please do not post comments as answers. Please delete these two answers and simply update your question with the new info if you are not able to post a comment on someone else's answer yet.
0 Likes 0 ·
@sp_lock : if applying those patches works, then you should post that as an answer.
0 Likes 0 ·
Wilfred van Dijk avatar image
Wilfred van Dijk answered
Install at least SP1 for MSSQL 2014 and apply traceflag 4199 to enable all the cardinality estimator fixes. see [this link][1] [1]: https://blogs.msdn.microsoft.com/psssql/2015/06/16/identifying-sql-server-2014-new-cardinality-estimator-issues-and-service-pack-1-improvement/
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.