Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

Fun With Flags - What’s the Optimizer Doing?
Published: Jul 03, 2022
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.

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.


© Copyright 2020 SQLTraining Ltd.