Improved diagnostics for query execution plans that involve residual predicate pushdown

I love stuff like this!

Even though it’s not on my list of dream features, it’s pretty neat. Getting new views into what SQL is doing when queries execute is pretty cool. You can read the short and gory details at the KB here: Improved diagnostics for query execution plans that involve residual predicate pushdown in SQL Server 2012

The wording might seem a little odd at first. The phrase “predicate pushdown” might make your face scrunch like you just stepped in something wet when the only thing you have on your feet are your lucky socks, and “residual” might just remind you of the time you left Tupperware in your desk drawer before a long weekend.

I would imagine this is coming to SQL Server 2014 in an upcoming CU/SP, but it hasn’t yet. As of this writing, the newest CTP for 2016 is 3.1, and it is working there as well.

You’re rambling, man. Get on with it.

Let’s take a look at what this ol’ dog does, and how we can use it to troubleshoot query problems. We’ll start with a small-ish table and some indexes.

A million rows and nothing to do

Before the addition of this element, the only way to get any information like this right in SSMS was to SET STATISTICS IO ON, but that only gave us a partial story about the pages read by our query. Let’s look at a couple examples.

Running the above query with the actual execution plan turned on, the tool tip that pops up over the index scan looks like this:

We read all million rows! Bummerino duder.
We read all million rows! Bummerino duder.

This is something Brent wrote about years ago. Great post, Brent! It’s further evidenced here, by the new Number of Rows Read line. We had to scan the index, all million rows of it, to search out the double wildcard LIKE predicate.

If we alter the query slightly, we can cut down dramatically on the number of rows we’re looking at. I get that this changes the logic, but really, you need to take care when allowing people to search full strings like above.


Note that we're no longer scanning the index, either.
Note that we’re no longer scanning the index, either.

Granted, getting away from double wildcard searches and towards more sane search methods is a big leap. What if we just tighten our last query’s predicates up a bit? Say that we only needed POs that start with ’00’, and we only needed results since June. We’re filtering on [ProcessDate] to make sure that the order was actually fulfilled, or something. It’s a dream!

That's way less, boss.
That’s way less, boss.

Now we’re down to reading just a few thousand rows to find what we need.

So what?

If you’re on SQL 2012, or, for some reason on CTP 3.1 of SQL 2016, you should take a new look at troublesome queries. Perhaps you can track down similar predicate inefficiency using this new tool. You may be reading way more data than you think. Anything you can do to cut down on data movement will very likely speed things up. The queries, in order, ran for an average of 110ms, 47ms, and 3ms respectively. Small changes can make differences.

To get this to work, I had to be on the SSMS November preview. It wasn’t showing up in other versions of SSMS for me.

I’d also like to thank everyone who voted for my Connect Item. It’s nice to know that 162 of you are at least as weird as I am about tempdb.

Previous Post
How to Make SQL Server Backups Go Faster
Next Post
We Are Ready For Risk-Taking Presenters

6 Comments. Leave new

  • Nice article. Unfortunately getting on current anything here at work is near impossible :-). But the information is very good nonetheless.

    • I know how that goes. Usually if you send change management an ominous enough email with a large enough Excel file attached, you get what you want.


    It’s great that you’re self-editing to avoid verbosity but the little nuggets of wisdom at the head of the article are worth reading.

    Nice encapsulation of the topic. I will be bookmarking this one.

    Also, props for the link to the “classic” Brent piece. I will be sharing this with my colleagues, as well.


  • Jay Robinson
    June 17, 2016 7:46 am

    Running June 2016 SSMS against 2014 database and I’m not seeing Number of Rows Read for some reason. I swear it was there the other day! Anyway, thought I’d throw it here in case anyone else had a similar experience or had a clue why this isn’t working.

    • Erik Darling
      June 17, 2016 9:15 am

      It’s, sadly, not in 2014 yet. It made it to 2012 in SP3, and 2016.

      I’d keep an eye out for this in SP2, which Microsoft sort of bizarrely did announced over here.


Leave a Reply

Your email address will not be published.

Fill out this field
Fill out this field
Please enter a valid email address.