question

yasubmj avatar image
yasubmj asked

A list of Blocking/Non Blocking Iterators

Hey There! Went through a really nice post by Craig Freedman on "Blocking/ Non Blocking Iterators", find below the link to it: http://blogs.msdn.com/b/craigfr/archive/2006/06/19/637048.aspx After reading the above post, i was curious to get a comprehensive list of all Blocking/Non Blocking iterators, but could'nt find anything anywhere! Can someone help me to put this list together? Just Curious! :) -Yasub
sql-server-2008performanceexecution-plan
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SQL Kiwi avatar image
SQL Kiwi answered
The fully-blocking iterators that consume their entire input in `Open()` are: - Sort (including TopN Sort and Sort Distinct) - Eager Hash Distinct (*not* Flow Distinct) - Hash Join (build input only, probe is pipelined) - Eager Table Spool - Eager Index Spool - Eager Row Count Spool - UDX (check, compile, and serialize) - Scalar (no group by clause) aggregate - Batch Hash Table Build (columnstore) - Remote Range - Remote Scan - Remote Query - Remote Fetch - Remote Modify Other iterators are either fully pipelined or semi-blocking (a *group* of rows is consumed before producing output).
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

So says the guy with the debugger. So say we all.
2 Likes 2 ·
Grant Fritchey avatar image
Grant Fritchey answered
There is not a definitive list available from Microsoft. You can mostly derive which of the operators within a plan are blocking or not by reading their descriptions, but it's largely a guessing game. You can read more about it in my [book on execution plans][1]. [1]: http://www.amazon.com/Server-Execution-Plans-Grant-Fritchey/dp/190643493X/ref=sr_1_11?ie=UTF8&qid=1378119376&sr=8-11&keywords=fritchey
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Grant! It would be so awesome, if there was such a list, or if someone super experienced like you, could put this list together and share it with us amateurs. :)
0 Likes 0 ·
Problem is, it's not clear in all cases, and Microsoft isn't supplying the information. I've asked.
0 Likes 0 ·
Argh!!!!!! :( .... Anyways, thanks for clarifying.. :) ... Hoping against hope that someone from MS, sees this and replies with the list. :D
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.