Why query plan is different

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.

AF.

avatar image By ByAlaN 3 asked Mar 30 at 01:25 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

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:

 SET ARITHABORT ON;
 GO

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

avatar image By Aaron Bertrand ♦ 1.7k answered Apr 11 at 09:25 PM
more ▼
(comments are locked)
avatar image ByAlaN Apr 12 at 12:49 PM

Great!..

Thanks a lot.

10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.