Query runs for days when upgrading to 2017

Question

Query runs for days when upgrading to 2017

askcoffman

Mr or Mrs. 500

Points: 580
More actions
December 5, 2019 at 7:46 pm
Go to Answer
#3705132

We upgraded our datawarehouse sql server from 110 to 140, RTM - CU17. In the new version, the query in the link below was allowed to run for 24+ hours before killing it.
https://www.brentozar.com/pastetheplan/?id=B1O4D08ar
If I add option(use hint('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110')), the query runs in 5 minutes. This query is from our cube processing job so I can't embed the hint in the cube partition without getting syntax errors.
A quick work around was to change the compatibility mode to 110 on the DB, process the cube, then revert back to 140. Not ideal....
I was able to spot on the execution plan that the period_dim is retrieving over 5 billion rows, but there are only 80K in the table. Assuming this is the issue, how do I help sql get a better estimate? All stats have been rebuilt using 100% sample.
Jeff Moden

SSC Guru

Points: 1004329
More actions
December 6, 2019 at 3:29 pm
Answer
#3705437

Just to add to what John has stated, I've found that a lot of the code that fails really needs a rewrite anyway. If you can't fix a piece of code in an hour or so, consider scrapping it and starting anew. Heh... and you know you're gonna add meaningful comments this time, as well, right? 😀
--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)

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

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

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

I don't recall the syntax off of the top of my head but there's some code to tell SQL Server to use the old cardinality estimator, which is what this problem seems to be all about. I don't know for sure but I'd imagine they'll stop supporting that someday. With that, if you know what code is causing you grief, I'd eventually fix it to work with the new cardinality estimator (CE).

I didn't use such code when we ran into the problem because it affected so much of our code. See Trace Flag 9481 at the following link for how to revert the whole box to the old CE, which is nasty overkill be we had to do it. I recommend using the code I mentioned above for a much less broad stroke on this problem if you can isolate the problem quickly and to just a few things.

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-ver15

Links to the finer scoped code is available when you lookup Trace Flag 9481 above.

--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)

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Have you looked at the definitions of those two views? You may be able to tweak the code so it works better with the "new" query optimizer.

John

askcoffman Mr or Mrs. 500 Points: 580 More actions · Answer 3

The trace flag and the hint that I included do the same thing for the cardinality estimator. I agree the real solution is to adjust the query to fit the 2017 CE. For this purpose I included the execution plan hoping that someone who is more of an expert than me could read this and suggest a way to alter the query/view. Would the the scripted view be helpful? I assumed including the execution plan would cover that.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

I think if anyone tried to reverse engineer the query from the execution plan, they'd lose the will to live! Don't get me wrong - the execution plan is indeed useful, but we need to see the whole picture. From my point of view, I'm just interested in the view definitions in case there's something really obvious that could easily be changed.

John

askcoffman Mr or Mrs. 500 Points: 580 More actions · Answer 5

Thanks Jeff and John for the help. I ended up rewriting the vw_pos_period_dim as a materialized view. Still not sure why that worked since the view itself performed well, but when joined ran terribly.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 6

askcoffman wrote:

Thanks Jeff and John for the help. I ended up rewriting the vw_pos_period_dim as a materialized view. Still not sure why that worked since the view itself performed well, but when joined ran terribly.

A materialized view is basically a table behind the scenes. It doesn't have to do the same calculations as your non-materialized view.

--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)