Hi guys. Assuming SQL Server can choose the appropriate execution plan for a Query/SP — How can I see how many execution plans are exists for a specific Query/SP ?

@RoyiNamir do you mean 'how many were considered'? A non-trivial query will have a very large number of possible plans...

@JackDouglas Yes that's what I meant. Does a simple select with a few joins can have large amount of possible plans ? ( I thought there're a few plans , not large amount of plans....)

@RoyiNamir even just considering join order, permutations go up with n! which is faster than exponential

and of course, join order is usually not the only variable

Oh.... It's much more complicated than I thought.....Thank you.

optimizers invariably have a strategy for narrowing down the list of plans they will cost because the cost of costing all possible plans is otherwise going to be higher than the cost of executing them, if you see what I mean

what's your reason for wanting to see the plans other than the chosen one?

The purpose is just for learning. I've learned that if SP do @declare @v=something and later I do in the SP : select where myIndexedColumn=@v then , the index will be used as scan and not via seek so it must chose a different execution plan. I just wanted to see what other possibilities it has to chose from.

(Again, just for learning purpose).

@RoyiNamir that's not how I understand Paul's comment:

I don't think a 'seek' is impossible, it's just that cardinality estimates may be 'wrong' enough that it chooses a scan when a seek would really be faster

Good memory :-). , Still , can't i see the possibilities of execution plans chosing ?

Or at least the number of them ...

Not sure - on Oracle I often use hints to compare two execution plans

Same in SQL Server. In that instance you'd add a FORCESEEK hint.

There's no direct way to see alternative execution plans or the number of alternatives considered though.

aha, there you go @RoyiNamir

thanks @Paul

No worries.

Thank you all

@PaulWhite Thanks, I think disabling page locks is now on record as being opposed by everyone except the author of that SQLCAT post.

It is a pretty crazy idea.

As was whatever they did to break all the original SQLCAT links.

@PaulWhite Yes, it looks like they've been replaced by a 230 page Word document

Much more convenient :)