Compatibility Mode causes query not to finish

Question

Compatibility Mode causes query not to finish

krypto69

SSChampion

Points: 13604
More actions
August 8, 2015 at 5:33 am

#319570

Hi,
We recently upgraded out SQL version from SQL2008R2 to SQL2014. As such, the compatibility mode changed to SQL2104 (120).
We have several queries that used to run fine that now take forever to bring back results. There are no errors (which surprised me). They just take way too long now. PLus they seem to be causing high I/O and CPU.
If I change the compt level back to SQL2008 - these queries run fine.
QUERY with SQL2008 compt level - finished in 2 minutes.
QUERY with SQL2014 compt level - finishes in 3 hours 22 minutes.
same exact query - same server - only thing changed was compatibility level.
WHat do I look for in the queries that could be causing this? (they look fine but obviously I'm missing something here)..

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

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

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 1

My first question would be, did you rebuild the indexes and the statistics? Also, have you installed the good version of service pack 1?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

This sounds like you may have a couple of queries that fit into the edge cases where they do not work well under the SQL 2014 Cardinality Estimator. For those queries, you can use a trace flag to use the old CE. Most queries under the new CE will perform better, but some do far worse.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

jude.pieries Default port Points: 1438 More actions · Answer 3

-Is there a meterialized view that sits under the query. Might pay to rebuild the index

(This might need to be done during low usage)

-Have you got variable tables in there? If yes, also investigate the possibility of adding the 2453 trace flag for better row estimation.

krypto69 SSChampion Points: 13604 More actions · Answer 4

krypto69

SSChampion

Points: 13604

August 8, 2015 at 11:25 pm

#1818383

Thanks Jeff forgot about SP1

krypto69 SSChampion Points: 13604 More actions · Answer 5

krypto69

SSChampion

Points: 13604

August 8, 2015 at 11:26 pm

#1818384

You were correct Jason. Good call. Thanks.

krypto69 SSChampion Points: 13604 More actions · Answer 6

Jeff Moden (8/8/2015)
My first question would be, did you rebuild the indexes and the statistics? Also, have you installed the good version of service pack 1?

Jeff - u may have nailed this one

https://support.microsoft.com/en-us/kb/3044519

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 7

NP. It IS one of the many reasons why I prefer to not be on the bleeding edge.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Jeff Moden (8/9/2015)
NP. It IS one of the many reasons why I prefer to not be on the bleeding edge.

Bleeding edge with major releases, SP, or even CU. Let it bake for a bit and then test it imho.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 9

Several things....

YES, rebuild indexes at bare minimum after moving from 2008 to 2014... any verison upgrade you should do that.

Also, get the query and see what indexes it is or isn't using. If you see table scans in there that is bad. You might need new indexes. The optimizer in 2014 is drastically different and like a previous poster said 95% of things will run better and a few will be terrible from what I have read.

Also, make sure stats are updated on at least a weekly basis if you don't have a scheduled task already doing this.

Let us know what you find as all of us not on 2014 are curious for future upgrades.

We have 4 new apps here on 2014 without any issues so far but I have not upgraded anything to 2014 yet.