Post reply

SSIS - runaway queries after converting

  • May 6, 2019 at 2:55 pm

    #3636456

    (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

    #3636503

    Could be the 'new' cardinality estimator (introduced in 2014). Lots of links all over, but you could start with this.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • May 8, 2019 at 12:17 pm

    #3638257

    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