Tsql runs fine under sql2008R2, but not under sql2014

Question

Tsql runs fine under sql2008R2, but not under sql2014

Ian_McCann

SSCertifiable

Points: 5163
More actions
February 11, 2016 at 2:51 am

#324173

I have a relativly simple querry that runs in about 15 seconds on SQLServer 2008R2 (SP2)
But when the same database is upgraded to SQLServer 2014 (SP1), it runs forever.
I have followed the steps outlined by Thomas LaRock here
http://thomaslarock.com/2014/06/upgrading-to-sql-server-2014-a-dozen-things-to-check/
I have also enabled trace flag 4199.
Is there anything else I can do while I wait for the querry to complete and provide me with an execution plan. It's currently been running for 17 1/2 hours

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Can you pull the estimated plan from SQL 2014 and the actual plan from 2008 R2 and post them both?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ian_McCann SSCertifiable Points: 5163 More actions · Answer 2

Hallo Gail.

Unfortunitly I can't post them.

But in comparing them I see that the 2008R2 Actuel plan is using a loop join and 2014 a Hash Match.

I've used a hint to force a loop.

It is not my final solution, but it does buy me some time and put me back on familiar ground.

Many thanks for your help

Ian

krypto69 SSChampion Points: 13604 More actions · Answer 3

did you try changing the compatibility level to check for cardinality issues?

We have had many and some times the quick way to tell for sure is to switch it, then test.

Ian_McCann SSCertifiable Points: 5163 More actions · Answer 4

krypto69 (2/11/2016)
did you try changing the compatibility level to check for cardinality issues?
We have had many and some times the quick way to tell for sure is to switch it, then test.

I do not see any difference in performance between sqlserver 2014 in compatibility mode and a sql2008R2 installation.

However running in a compatibility mode is not an acceptable solution for us and we will rewrite the SP's to use a valid execution plan.

(at least, I hope we are able to do that :-))

Ken McKelvey SSCoach Points: 18843 More actions · Answer 5

You may find the Word document referenced here to be useful:

https://msdn.microsoft.com/en-us/library/dn673537.aspx

Please note, I have played with this a bit but not used it in anger as we do not have any production servers with SQL2014.

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 6

Ian_McCann (2/11/2016)
krypto69 (2/11/2016)
did you try changing the compatibility level to check for cardinality issues?
We have had many and some times the quick way to tell for sure is to switch it, then test.
I do not see any difference in performance between sqlserver 2014 in compatibility mode and a sql2008R2 installation.
However running in a compatibility mode is not an acceptable solution for us and we will rewrite the SP's to use a valid execution plan.
(at least, I hope we are able to do that :-))

You can set cardinality compatability for a single query/proc with an option

https://www.brentozar.com/archive/2015/06/careful-testing-the-2014-cardinality-estimator-with-trace-flags/

Ian_McCann SSCertifiable Points: 5163 More actions · Answer 7

Ken McKelvey (2/11/2016)
You may find the Word document referenced here to be useful:
https://msdn.microsoft.com/en-us/library/dn673537.aspx
Please note, I have played with this a bit but not used it in anger as we do not have any production servers with SQL2014.

Thank you Ken

The examples shown on page 27 under the title "Join Estimate Algorithm Changes" is exactly what we are getting here 🙂

Figure 21 and figure 22 could be our execution plans :-):-)

Ian_McCann SSCertifiable Points: 5163 More actions · Answer 8

Steve Jones - SSC Editor (2/11/2016)
Ian_McCann (2/11/2016)
krypto69 (2/11/2016)
did you try changing the compatibility level to check for cardinality issues?
We have had many and some times the quick way to tell for sure is to switch it, then test.
I do not see any difference in performance between sqlserver 2014 in compatibility mode and a sql2008R2 installation.
However running in a compatibility mode is not an acceptable solution for us and we will rewrite the SP's to use a valid execution plan.
(at least, I hope we are able to do that :-))
You can set cardinality compatability for a single query/proc with an option
https://www.brentozar.com/archive/2015/06/careful-testing-the-2014-cardinality-estimator-with-trace-flags/

Thank you Steve.

It's a political thing.

After the upgrade to SQL2014 (hardware, operating system and sql server) nobody want to see anything running in a comptability mode.