THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

TechEd North America 2014 (Houston) - Demos - DBI-B489 and DBI-B333

Just a quick note to thank everyone who attended both of my sessions at this year's TechEd show in Houston!

Attached are demos for both the query tuning and window functions talks.

Enjoy, and let me know if you have any questions.


Published Thursday, May 15, 2014 12:05 PM by Adam Machanic


Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



RalphHerron said:

The session yesterday was great.  Due to the standing room only I missing one of the "undocumented" trace flags.  What was the one to turn off the spooling?  --Thanks for sharing the knowledge!!

May 15, 2014 1:51 PM

Adam Machanic said:

Hi Ralph,

8690. Glad you enjoyed it!


May 15, 2014 4:10 PM

Jeff said:

Attended the DBI-B333 T-SQL Power session.


Exactly the info I need to convince our CORP IT to upgrade to at least SQL 2012.

We run a lot of month over month and year over year reports against a ton of  data. With these new commands I can show upper management that they won't need to take a coffee break or go clean out their Inbox while they wait for the reports to finish. Convince management and CORP will fall in line!

Thanks Adam!  

May 15, 2014 6:55 PM

Patryk Nowak said:

These sessions were fantastic! No amount of Google searching helped me find the solution for our problem queries but these sessions answered all my questions and I didn't even have to ask. Superb. Thanks Adam!

May 16, 2014 3:39 PM

Gustavo said:

Hi there Adam. Good sessions as always.

Now a question about DBI-B489, you said that developers use to use views nested in other views and other views and the QO isn't abe to push predicates down.

And you suggest to use inline TVF instead.

We all know inline TVF rock, but I thought that view were kinda like "macros" and were inlined and expanded by the QO.

I don't quite undestand in which cases the views can prevent the QO to push predicates down.

I suppose very nested and complicates views maybe, but don't know if that's what you meant.

May 20, 2014 6:28 PM

Janaka said:

Hi Adam,

This was one of the best sessions I attended at MS TechEd 2014!!

I learnt a lot!!  I am now a follower!! Thanks!!

May 21, 2014 12:21 PM

Adam Machanic said:


Thank you for the kind words and I'm so glad you enjoyed it!


I'm not sure I can come up with a specific list of cases -- maybe Paul White could chime in? I'll ask him. In any case here's a simple example against AdventureWorks, in which the predicate pushdown is inhibited by a TOP operator:





SELECT TOP(1000000000)


FROM Production.TransactionHistory AS th

) AS p


p.ProductID = 123

May 21, 2014 2:38 PM

Steve Grimes said:

Hi Adam -- loved your stuff at TechEd -- thank you!

I wondering if the slides 6 through 13 of your DBI-B333.ptx presentation (Windows that open Doors), should say

ORDER BY TransactionDate, ProductID

instead of ORDER BY ProductID, TransactionDate.

At least, that's what the data in the example looks like to me.


May 23, 2014 7:02 AM

Adam Machanic said:

Hi Steve,

Great catch. Could have used your keen eyes last week!


May 23, 2014 9:52 AM

Brian said:

Hey Adam,

I watched your youtube video in regards to this topic.  Would you mind sharing your slides?  Assuming you still have them.  There are a couple on there that I wanted to review with my colleagues!

January 12, 2016 10:31 AM

Adam Machanic said:


They're available if you look here:

January 12, 2016 3:11 PM

Mark said:

Hi Adam,

Thank you very much for your presentation, these materials, and all that you do (and have done) to give back to the community.

I'm trying to learn more about your comment in the presentation about moving away from nested Views towards ITVF because it allows us to "push the predicate down".  

Are there any other posts, presentations, or books that you could recommend that cover that subject in more detail?  I've searched online, but can only find general "nesting view" performance posts; they are not specific to using ITVF for performance improvement.  

I started looking at the example given to @Gustavo back in 2014 above, but it doesn't leave me with a complete picture of what the solution should be, and how to visually see the difference when reviewing the Execution Plan.

Thank you again!

July 21, 2016 5:35 PM

Adam Machanic said:


Sorry, but I don't have anything more in depth. If you look at the Gustavo example, where I put the TOP in the derived table (which is effectively the same as a view), and compare that to the plan without the TOP, or with the predicate under the TOP, you'll see the difference in the plans.

Generally TOP, GROUP BY, DISTINCT, and similar constructions are the biggest issues with regard to predicate pushdown. But I've even seen it limited without these in place. It's mainly a problem in cases where I expect the query optimizer to choose a nested loop and it uses a different operator instead, thereby processing way more data than necessary.


July 27, 2016 10:19 AM

Mark said:


Thank you for your reply, I think I see it now.  I executed this:

--Exploring Predicate Push-down

SELECT TOP(1000000000) *    



SELECT th.ProductId

FROM Production.TransactionHistory AS th

) AS p

WHERE p.ProductID = 123

Looking at it from right-to-left or a "data" point of view, The Gustavo example begins with an Index Scan on the TransactionHistory table, and shows "thick lines" running thru both the TOP and FILTER operators.

In the above example I just created, we now have an Index Seek (instead of Scan) on the TransactionHistory Table, we have no "think lines" and we only have a TOP operator.

Looking at Table I/O I can also see a difference:

Gustavo:     203 Logical Reads

New Example:   2 Logical Reads

This means the predicate was "pushed-down" and made it all the way back to the table, positively impacting the Scan Vs. Seek decision made by the Query Optimizer.

Does the above sound/look correct?  (Thank you in advance for diving into the weeds on this!)


July 27, 2016 10:58 AM

Adam Machanic said:


Pretty much spot on, with the exception of the scan vs seek. A predicate could be pushed into a residual. Whether or not that's ideal is a totally different topic :-)

What it boils down to is the question of whether the QP is working with more rows than it has to. Thick lines bad, thin lines good!


July 27, 2016 11:57 AM

Mark said:


Thank you very much for your time and help!

July 27, 2016 4:16 PM

Leave a Comment


About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog


Privacy Statement