Revealing Predicates in Execution Plans (TF 9130)
Published: 16th September 2014
In this episode of Fun with Flags I'm going to discuss trace flag 9130. I found this flag when watching a video on performance tuning by Joe Sack… it’s something that I never knew existed, but that I’ve actually now used a few times because it turned out to be surprisingly handy.

SQL Server has a habit of hiding certain things in your execution plan and you might find that sometimes it’s not clear to see what’s going on, or why it’s chosen the plan it has. Well this can actually help with that.

I’ll show you what I mean with a demo…

Let’s imagine we have a VERY basic query which we’re running in the AdventureWorks2012 database…

select SalesOrderID, ShipDate
from sales.SalesOrderHeader
where ShipDate = '2005-07-08'


Now, if we run this and look at the Execution Plan then we will see the following:

Nothing special or unexpected there. However, we might wonder why we’re using a Scan there… what is the scan actually doing?

Obviously I wouldn’t expect anyone reading this to think that in this case, but in a much more complex plan, sometimes it’s hard to discern what’s going on and where predicates are being dealt with.

If we hover over the Clustered Index Scan we would see the following:

As you can see, the predicate was pushed directly into the scan and therefore was hidden from our view.

So what happens when we use TF 9130?

select SalesOrderID, ShipDate
from sales.SalesOrderHeader
where ShipDate = '2005-07-08'
option (querytraceon 9130)


As you can see, the predicate is now removed from the Clustered Index Scan and you can clearly see what’s happening in the Execution Plan as a whole.

Hovering over the Filter will tell you the same as we saw earlier:

Personally I find this a little clearer. If I was glancing at an execution plan alone (ie. simply looking for obvious issues in a plan rather than trawling through lines of code first) then I would immediately know that a scan is taking place, a lot of rows are being considered (the thicker line coming from the index scan) and then they are being filtered. Therefore a covering index would be ideal here.

The actual execution plan doesn’t highlight this at all and therefore just glancing at it isn’t so clear.

In fact, have a look at this...
Okay, obviously I've just made a silly query to generate this plan (included below if anyone's interested), I'm just using this as an example as to what you could see when looking at an execution plan.

It clearly shows two clustered index scans... they look pretty much identical... but are they? At a glance yes... but let's use our new trace flag...

Actually no... not the same at all... you can clearly see that one of the scans was the entire table, which seemingly we wanted... however the other was actually a filtered clustered index scan on a much larger table and therefore an index would likely benefit.

Looking carefully you could have seen this in the original plan (2% cost versus 0.2%) but it really wasn't obvious.

Now, I’m definitely not suggesting that you use this in production… that’s just unnecessary. But what I am saying is that you might want to consider this when performing tuning or execution plan analysis if things are a little unclear as it might just help.

Code:

if object_id('salesOrderHeaderTemp') is not null drop table salesOrderHeaderTemp

select top (3025) *
into salesOrderHeaderTemp
from sales.salesOrderHeader

create unique clustered index pk_salesOrderHeaderTemp_SalesOrderID on salesOrderHeaderTemp(salesOrderID)

select a.*
from
(
  
select salesOrderID, DueDate
  
from sales.SalesOrderHeader
  
where orderDate <= '2006-07-01'
) a
join
(
  
select salesOrderID, DueDate
  
from salesOrderHeaderTemp
) b
on a.SalesOrderID = b.SalesOrderID
cross join person.person
--option (querytraceon 9130)
Comments:
NB: Comments will only appear once they have been moderated.