This is quite a fun episode of Fun With Flags because I like poking about inside the Optimizer to see what’s really going on. Doesn’t mean I can influence it… but it can be quite enlightening to have a poke about inside and see what bubbles up to the surface.
For example, I mentioned in my last post (about ANY / SOME) that the Optimizer sees all EXISTS / IN / ANY / SOME as a SOME operation internally… so where did I get that from?
Let’s use the same query as I did in the previous blog BUT with one key addition… a couple of trace flags:
select *
from Production.product p
where exists
(
select productID
from Production.TransactionHistoryArchive t
where p.ProductID = t.ProductID
)
option (querytraceon 3604, querytraceon 8606, querytraceon 8621)
If you look closely you’ll see that the EXISTS is indeed seen as a SOME operation to the Optimizer (ScaOp_SomeComp). You’ll also see that there’s a 2 next to the operation… this is simply “EQUAL”… there are others which you can easily find out by playing with your query… you’ll see they’re 1 – 6 and cover the obvious (=, <=, < etc).
Anyway… the main point of the blog was simply to highlight the flags which can be used to see what’s happening inside the Optimizer. These are undocumented (that I’ve been able to find anyway)… but TF8606 can be used to see the Tree itself which the Optimizer is using, including all join types and operators. TF8621 shows you what the Optimizer is doing in terms of simplifying and re-writing your joins. For example, there are rules in which the Optimizer chooses to re-write and remove Subqueries from your T-SQL in order to align them and simplify where possible.
It’s hard to explain exactly what you’ll see because this will be so varied in all circumstances… but with some practice you’ll soon start to see patterns and be able to read it yourself with relative ease. As with everything… just play with the Trace Flags and see what you can decipher. Half the fun is poking about on your own… I’m just happy to point you in the right direction.