Post reply

How to force query to use certain execution plan?

  • August 23, 2016 at 9:30 am

    #329581

    We are in a process of migration from 2008R2 to 2014. We have a very complex query (it is a part of S.P.) that executes much slower in 2014. Execution plans are different. What I want to test is to create exec plan on 1st server, save it in XML, and then to use it for the same query on a 2nd server. Is it possible? If yes, how?

    Thanks

  • August 23, 2016 at 9:57 am

    #1896958

    The USE PLAN hint, but I would recommend you try every other way to get a desired plan shape first. The hint's not one I like using in production.

    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

  • August 23, 2016 at 10:10 am

    #1896961

    SQL Guy 1 (8/23/2016)

    We are in a process of migration from 2008R2 to 2014. We have a very complex query (it is a part of S.P.) that executes much slower in 2014. Execution plans are different. What I want to test is to create exec plan on 1st server, save it in XML, and then to use it for the same query on a 2nd server. Is it possible? If yes, how?

    Thanks

    Why not just use the relevant trace flag to force the query to use the pre-2014 cardinality estimator in your query? (I'm assuming that the new CE is the root of your issue.)

    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

  • August 23, 2016 at 2:35 pm

    #1897047

    You're assuming right. This is precisely the warning that I received in 2014. But can I ask you more questions related to this. What is trace # for 2008R2 and how can I use it? How can it impact other queries in 2014 in production?

    And a more general question: if with pre-2014 trace flag it will improve performance, what the point of migration at all? I will be running 2014 with important performance features of 2008?

  • August 23, 2016 at 2:58 pm

    #1897049

    I suspect Phil meant to apply the traceflag just to the problematic query, not enable it system-wide.

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

    https://blogs.msdn.microsoft.com/psssql/2015/06/16/identifying-sql-server-2014-new-cardinality-estimator-issues-and-service-pack-1-improvement/

    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

  • August 23, 2016 at 3:17 pm

    #1897056

    GilaMonster (8/23/2016)

    I suspect Phil meant to apply the traceflag just to the problematic query, not enable it system-wide.

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

    https://blogs.msdn.microsoft.com/psssql/2015/06/16/identifying-sql-server-2014-new-cardinality-estimator-issues-and-service-pack-1-improvement/

    Correct suspicion.

    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

Viewing 6 posts - 1 through 5 (of 5 total)

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