How to force query to use certain execution plan?

Question

How to force query to use certain execution plan?

SQL Guy 1

SSCoach

Points: 15981
More actions
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

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

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

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

Phil Parkin SSC Guru Points: 247180 More actions · Answer 2

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

SQL Guy 1 SSCoach Points: 15981 More actions · Answer 3

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?

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

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

Phil Parkin SSC Guru Points: 247180 More actions · Answer 5

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.