Why query plan is different

ByAlaN 2017-03-30 13:25:02

link textHello.

I want know why execution query plan is different in Sentry SQL Plan Explorer?, its a bug or this is normal behavior?

Thanks a lot.

Edit: SQL Server 2008 R2 Enterprise.


Aaron Bertrand 2017-04-11 21:25:26
The difference is in the SET settings (specifically, your SSMS session had ARITHABORT = true, while Plan Explorer had ARITHABORT = false. (Easiest way to see this is in the Plan XML tab.)

This is the same type of behavior described in Erland Sommarskog's Slow in the Application, Fast in SSMS article, as well as my post, Multiple Plans for an "Identical" Query. The reason is that, even though the query text may be identical, SQL Server must generate independent plans due to the differences in set options. Sometimes this just leads to wasted space in the plan cache, but other times it can lead to completely different plans due to changes that have happened to the data in the meantime (especially if a long time has passed since the first plan was generated).

You can work around this by adding this to Plan Explorer's command text prior to the query text:


Or by doing the opposite in SSMS (change ON to OFF).

ByAlaN 2017-04-12 12:49:13

Thanks a lot.