Multiple execution plan for 1 query.

Question

Multiple execution plan for 1 query.

LearningDBA

SSCertifiable

Points: 6609
More actions
February 6, 2015 at 10:27 am

#315072

I have this SQL statement with 2 different execution plan. I am also attaching both execution plan. No one has complained about the performance yet. Just curios to find out if it's normal, or something needs to be done.
(@0 varchar(8000),@1 varchar(8000),@2 varchar(8000),@3 varchar(8000))
SELECT top 1001 T680 . C1 ,
C1000005709 ,
C1000005706
FROM T680
WHERE ( ( T680 . C813000002 = @0 )
AND ( T680 . C1000005705 = @1 )
AND ( ( T680 . C900000333 ! = @2 )
OR ( T680 . C900000333 ! = @3 ) ) )
ORDER BY 1 asc
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

Viewing 4 posts - 1 through 3 (of 3 total)

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

lmarkum SSCertifiable Points: 7989 More actions · Answer 1

I can't speak to how normal or common it is to have multiple execution plans for the same piece of code. I can recommend that you review the indexes on T307. It seems that in both plans there are some Key Lookups happening on just a few columns and those lookups, when combined, account for a fair percentage of the query cost. There are some index seeks happening on T307 and I would see if you could create one or maybe to covering indexes that would eliminate the key lookups. You could probably do the same with T287.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

The SET options are different between the two plans, hence two different plans in cache as they're technically different queries.

One has

ANSI_NULLS: True, ANSI_PADDING: True, ANSI_WARNINGS: True, ARITHABORT: True, CONCAT_NULL_YIELDS_NULL: True, NUMERIC_ROUNDABORT: False, QUOTED_IDENTIFIER: True

The other has

ANSI_NULLS: True, ANSI_PADDING: True, ANSI_WARNINGS: True, ARITHABORT: False, CONCAT_NULL_YIELDS_NULL: False, NUMERIC_ROUNDABORT: False, QUOTED_IDENTIFIER: True

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

New Born DBA (2/6/2015)
I have this SQL statement with 2 different execution plan. I am also attaching both execution plan. No one has complained about the performance yet. Just curios to find out if it's normal, or something needs to be done.
(@0 varchar(8000),@1 varchar(8000),@2 varchar(8000),@3 varchar(8000))
SELECT top 1001 T680 . C1 ,
C1000005709 ,
C1000005706
FROM T680
WHERE ( ( T680 . C813000002 = @0 )
AND ( T680 . C1000005705 = @1 )
AND ( ( T680 . C900000333 ! = @2 )
OR ( T680 . C900000333 ! = @3 ) ) )
ORDER BY 1 asc

Your view is too complex, you're getting an optimiser timeout: "Reason for Early Termination of Statement Optimisation = Timeout". SQL Server is warning you that the statement is too complex to guarantee picking a good enough plan within a reasonable period of time. The plan you have will generate correct results but won't necessarily perform well, moreover, each time there's a plan recompile, a different plan is likely to be generated. Some will perform better than others.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden