May 6, 2019 at 2:55 pm
(Not sure if this is the right forum, if not: mods please move).
I'm converting our data warehouse from SQL 2012 to 2017. Once I increased the database compatibility to 2017, a number of my queries are now runaways -- queries that take a few minutes when running through a query window will take hours when run through the ETL in our overnight job. Worse yet, it's not always consistent -- a query will take five minutes one night, then three hours the next.
I was able to improve things slightly by adding some relevant UPDATE STATISTICS commands to the ETL, which suggests that my problem has something to do with statistics. But even that doesn't work every time.
I turned on the Query Store and forced plans for some of the more problematic queries, but in most cases the forced plan is not selected. To my eyes the selected plans look the same.
If I drop the database compatibility to SQL Server 2012, everything runs fine.
Running out of ideas, can anyone point me in the right direction?
May 6, 2019 at 3:43 pm
Could be the 'new' cardinality estimator (introduced in 2014). Lots of links all over, but you could start with this.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 8, 2019 at 12:17 pm
Thanks, Phil. That definitely sounds like it could be the issue.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply