Execution plan help

Question

Execution plan help

marcelo salgado

SSC Veteran

Points: 287
More actions
August 24, 2022 at 11:43 pm

#4080306

Explain what are the differences between the following execution plans and which of them would you select as the best?

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

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

The Seek is probably a Seek followed by a range scan of the full table, which probably has little if any advantage over the scan. I only use the Execution plan to help me see what is going on. I never make a final choice based on the execution plan. The key would be to run the against your choice of tools... SET STATISTICS TIME,IO ON or SQL Profiler or Extended Events and see which one is the actual winner.

And I never go by % of batch. I can intentionally write a query where the 0% of two batches is actually a whole lot worse than the 100% batch, which can run almost instantaneously while the 0% toting batch takes a minute to run.

And the only way to truly understand the differences between those execution plans would be to dive into the properties of each block but we can't do that because you posted a graphic rather that attaching and actual viable execution plan. Sure, someone could explain a bit like I did above, but you never know for sure until you view the properties of the nodes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 2

Performance has a cost.

That cost starts with declaring the correct data type for columns !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

LEFT is also non-sargable so you will get a scan with that second query.

To make it sargable you would need to use LIKE instead. Thanks

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 4

Ant-Green wrote:

LEFT is also non-sargable so you will get a scan with that second query.
To make it sargable you would need to use LIKE instead. Thanks

"Like" does not work with numeric data typed columns.