tag:blogger.com,1999:blog-3000721125799986885.post5317702214432572923..comments2024-03-28T04:05:20.028-07:00Comments on SELECT Blog FROM Brad.Schulz CROSS APPLY SQL.Server(): A Second in the Life of a Query OperatorBrad Schulzhttp://www.blogger.com/profile/01852762873611487368noreply@blogger.comBlogger31125tag:blogger.com,1999:blog-3000721125799986885.post-91538867900509801322017-04-18T20:57:01.913-07:002017-04-18T20:57:01.913-07:00LOL... No problem about my name... I'm used to...LOL... No problem about my name... I'm used to it, with people misspelling my last name all the time... You may notice that it was misspelled in Brent's newsletter. :)Brad Schulzhttps://www.blogger.com/profile/01852762873611487368noreply@blogger.comtag:blogger.com,1999:blog-3000721125799986885.post-75831577539831459512017-04-18T16:57:31.080-07:002017-04-18T16:57:31.080-07:00Will do. And sorry about mangling your name.Will do. And sorry about mangling your name.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-3000721125799986885.post-51873156026028736482017-04-17T11:28:52.659-07:002017-04-17T11:28:52.659-07:00I get Brent's weekly newsletter myself, but I ...I get Brent's weekly newsletter myself, but I hadn't read it yet this morning... but all of a sudden I saw 3 new comments on this post! Thanks to Brent&Company directing people here.<br /><br />@Ted and @Martin: Thanks for the comments... so glad you enjoyed it.<br /><br />@Anonymous: I haven't had time in years to write posts, but if you poke around this blog, you'll find plenty of interesting quirky stuff. Make sure you also check out blogs from Rob Farley, Paul White, and Michael Swart.<br />Brad Schulzhttps://www.blogger.com/profile/01852762873611487368noreply@blogger.comtag:blogger.com,1999:blog-3000721125799986885.post-70143178900292060352017-04-17T10:33:35.184-07:002017-04-17T10:33:35.184-07:00Hello Bard. I was directed to your fairly old post...Hello Bard. I was directed to your fairly old post by Brent Ozar's email newsletter. Hopefully you will get some new traffic because of it. I'm a SQL developer and I'm always interested to learn about query plans. Your post was entertaining and informative. I am sad that even one person found something to criticize about it. Please give us more!!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-3000721125799986885.post-57851690935979000362017-04-17T09:44:02.921-07:002017-04-17T09:44:02.921-07:00love it... it's hard to forget these concepts ...love it... it's hard to forget these concepts when you learn them in such a fun way!Anonymoushttps://www.blogger.com/profile/01728749070467781786noreply@blogger.comtag:blogger.com,1999:blog-3000721125799986885.post-10315170260092811882017-04-17T09:38:41.567-07:002017-04-17T09:38:41.567-07:00Great post, Brad. Thanks for putting effort into ...Great post, Brad. Thanks for putting effort into making a difficult subject enlightening and entertaining--it was a good read.Ted Glickhttps://www.blogger.com/profile/03533941825433656758noreply@blogger.comtag:blogger.com,1999:blog-3000721125799986885.post-61255298869578439692015-10-02T16:30:56.239-07:002015-10-02T16:30:56.239-07:00Thanks for your reply... I'm glad you found it...Thanks for your reply... I'm glad you found it helpful!Brad Schulzhttps://www.blogger.com/profile/01852762873611487368noreply@blogger.comtag:blogger.com,1999:blog-3000721125799986885.post-26400051886170646552015-10-02T14:40:15.044-07:002015-10-02T14:40:15.044-07:00Thank you for this wonderful post - I've alway...Thank you for this wonderful post - I've always had an interest in query plans and yes, like many, I assumed that they started in the back and threw the data forward. This is good information, and a whole new model to consider.<br />LongHairedWeirdohttps://www.blogger.com/profile/00719702522755422229noreply@blogger.comtag:blogger.com,1999:blog-3000721125799986885.post-5429150521422315682011-10-31T13:02:59.231-07:002011-10-31T13:02:59.231-07:00Hello,
Thank you for an excellent read! Just wha...Hello, <br /><br />Thank you for an excellent read! Just what I needed as I am in the process of trying to fully understand query plans.<br /><br />I do have a few minor questions, I hope it is not too late to ask.<br /><br />Quote "NORA: Wake up, Stanley, dear! I need a row for SalesOrderID 47660."<br /><br />- This implies that the GetNext() method called on the Seek (from Nora/Sort) is called with SalesOrderID as an argument. Is this correctly understood?<br /><br />- This also implies that the Seek has to wait and can not start about its job before at least one row is returned from the Scan, that is, the Seek is not independent of the scan.<br /><br />This last point is very confusing as these things are always explained differently in every place I have read, and I would love a <br />clarification (the recommendation in the comments to get Delaneys book is noted).<br /><br /><br />Best Regards,<br />GeirAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-3000721125799986885.post-15879147287457306542010-12-20T12:18:58.416-08:002010-12-20T12:18:58.416-08:00I found the article entertaining but also enjoyed ...I found the article entertaining but also enjoyed the bit of insight - I'll read the query plans in both directions.<br /><br />After all, there is that expression for somebody who really knows their stuff: 'He/she knows it backwards and forwards'.<br /><br />Peace and ignore the hatas,<br />SteveSDChttps://www.blogger.com/profile/06074492906736058141noreply@blogger.comtag:blogger.com,1999:blog-3000721125799986885.post-78288748568848130092010-12-17T12:04:49.496-08:002010-12-17T12:04:49.496-08:00Wow, the comments on this post are starting to get...Wow, the comments on this post are starting to get longer than the original post itself!<br /><br />Jason, I wasn't sure at first how to respond to your original post. I appreciate the criticism, but I didn't appreciate the condescending tone... "Mr. MVP"? Gimme a break.<br /><br />Rob and Paul's comments have already communicated what I want to say on the subject.<br /><br />Sure, I suppose I could have worded the third paragraph differently... not make it so abrupt... but I wanted to get readers' attention. <br /><br />As Paul and I had mentioned in these comments, we do generally look at a plan from right to left to get a feel for where the data is going and in what order, but when you get down into detail of record counts processed by the operators and get into plans that involve spools and such, the whole "right-to-left" paradigm only takes you so far.<br /><br />I wanted to explain, in a hopefully entertaining way, how a query actually works under the hood. I've explained this at SQL user groups, and I can see the revelations in people's eyes as I talk about it, and I've had people express thanks for explaining it.<br /><br />The first several pages of Craig Freedman's 100-page chapter in Kalen Delaney's "Query Tuning and Optimization" book goes into great detail about how the operators work in the way I described, just as Paul had in his blog. <br /><br />In short, this is a concept worth knowing, and that's why I wrote about it. We can agree to disagree about its importance, but I'm certainly not doing anything to destroy the SQL Server community... It's not like I'm advocating murder or anything here... I'm surprised and sorry that you felt a need to respond so vehemently.<br /><br />--BradBrad Schulzhttps://www.blogger.com/profile/01852762873611487368noreply@blogger.comtag:blogger.com,1999:blog-3000721125799986885.post-82196033964877993342010-12-16T19:58:36.693-08:002010-12-16T19:58:36.693-08:00Jason,
He didn't say it was wrong, he said we...Jason,<br /><br />He didn't say it was wrong, he said we said we *shouldn't* read plans from right-to-left. He even says that he reads plans from right-to-left to get a feel for the flow of data.<br /><br />I don't agree with you that it's the wrong path. In fact, Brad is providing an explanation here which is far more complete than many other explanations of how execution plans work.<br /><br />I'm pleased you've said it's 'just your two cents', but I also think you need to be wary of criticising such an excellent post anonymously, and in such a personal way.<br /><br />If you'd like to email me on this directly, you can find my email address in the right-hand panel of my own blog. You'll find my name (above) is a link there.<br /><br />RobRob Farleyhttp://sqlblog.com/blogs/rob_farleynoreply@blogger.comtag:blogger.com,1999:blog-3000721125799986885.post-14096816828500378342010-12-16T11:17:13.412-08:002010-12-16T11:17:13.412-08:00Rob,
Off the top of my head I can only think of t...Rob,<br /><br />Off the top of my head I can only think of two operators that affect operators to their right: TOP and filters where the Startup Expression Predicate is specified. There may be others that I forgot or am unaware of, but the point is they are few and far between. <br /><br />To your point that "Different people realise the need to consider plans from both directions at different times", I agree despite it being very infrequently useful. However, the third line of Brad's post emphatically states that reading from right to left is wrong.<br /><br />Personally, I think an MVP should be doing things to help the SQL Server community and this post is only going to steer aspiring SQL Server query plan readers down the wrong path. <br /><br />That's just my two cents though.<br /><br />JasonAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-3000721125799986885.post-16802656086662224562010-12-15T14:27:18.876-08:002010-12-15T14:27:18.876-08:00Jason,
Many people don't need to understand e...Jason,<br /><br />Many people don't need to understand execution plans at all. For them, getting correct results, and being able to create useful-enough indexes to help performance may get them as far as they need to go.<br /><br />When people start to read execution plans, they see many resources explain that the data moves from right-to-left, and get stuck there. <br /><br />Unfortunately if your understanding stops at this point, there are many aspects of plans that remain completely unexplained, particularly around the way that operators know what to do based on operators to their left.<br /><br />Different people realise the need to consider plans from both directions at different times. I know many people who have found this post from Brad incredibly helpful and enlightening.<br /><br />RobRob Farleyhttp://sqlblog.com/blogs/rob_farleynoreply@blogger.comtag:blogger.com,1999:blog-3000721125799986885.post-8841922607911792442010-12-15T12:30:59.349-08:002010-12-15T12:30:59.349-08:00Brad,
I have been trying to decide if this post ...Brad, <br /><br />I have been trying to decide if this post is more misleading than it is pointless. I can't really decide. On one hand you clearly have some things messed up in your head about query plans (thanks for passing those on to the SQL Server community Mr. MVP!). On the other hand though, we are only talking about ways to abstract away what SQL Server is doing for the purpose of better being able to solve problems. <br /><br />Anyway, your profound and deep paradigm of reading left to right is just awful. I would save this explanation only for the most junior of people that I couldn't get through to any other way. Regardless, it begs the question, if they can't handle the more correct and difficult way of reading query plans then why are you explaining it in the first place?<br /><br />Sorry for the scathing post but someone had to write it.<br /><br />Thanks, JasonAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-3000721125799986885.post-68503349434526175782010-12-15T11:47:55.646-08:002010-12-15T11:47:55.646-08:00@Paul:
By "initialization", do you mean...@Paul:<br /><br />By "initialization", do you mean more about the Open() method of the individual iterators?<br /><br />True, I didn't want to muddy stuff up too much... I wanted to get across the general idea that (as Rob Farley so eloquently states) query plans "suck"... i.e. the iterators request or "pull" data from their children to the right... they don't "push" data from right to left.<br /><br />A little over a year ago, at least 3 MVP's were puzzling over a query plan at the MSDN forum and couldn't figure out why the counts were the way they were, until the light bulb finally went off over my head as to how they actually worked, and then it all became clear.<br /><br />I, too, also read plans from right to left to get a feel for the flow of data... but you have to understand the control flow when you get down into more complicated plans like recursive queries or windowed aggregates, for example.<br /><br />Thanks again, as always for the comment. Your blog post you mentioned promised future posts on the topic... I'm looking forward to it!<br /><br />--BradBrad Schulzhttps://www.blogger.com/profile/01852762873611487368noreply@blogger.comtag:blogger.com,1999:blog-3000721125799986885.post-21202812575796391682010-12-15T10:48:11.133-08:002010-12-15T10:48:11.133-08:00Brad,
To add to my previous comment:
You did mis...Brad,<br /><br />To add to my previous comment:<br /><br />You did miss a bit from the script concerning the initialization step (no doubt for space reasons). For these details and more, please see http://sqlblog.com/blogs/paul_white/archive/2010/08/05/iterators-query-plans-and-why-they-run-backwards.aspx<br /><br />I do still routinely read plans from the top-right and working left, because I am usually interested in the flow of data, rather than the flow of control.<br /><br />Cheers!<br /><br />PaulPaul Whitehttps://www.blogger.com/profile/04690243284528295117noreply@blogger.comtag:blogger.com,1999:blog-3000721125799986885.post-75741390977458094382010-12-11T15:47:36.772-08:002010-12-11T15:47:36.772-08:00I love this blog post. I made an homage to it call...I love this blog post. I made an homage to it called "Silverlight View Model (MVVM) - A Play In One Act" http://openlightgroup.net/Blog/tabid/58/EntryId/141/Silverlight-View-Model-MVVM-A-Play-In-One-Act.aspxMichael Washingtonhttp://openlightgroup.net/Blog/tabid/58/EntryId/141/Silverlight-View-Model-MVVM-A-Play-In-One-Act.aspxnoreply@blogger.comtag:blogger.com,1999:blog-3000721125799986885.post-43635048926651636412010-12-10T17:25:07.875-08:002010-12-10T17:25:07.875-08:00@Sabyasachi:
LOL... poor Stanley!
I have to admi...@Sabyasachi:<br /><br />LOL... poor Stanley!<br /><br />I have to admit that ever since I wrote this post, I look at query plans in a whole new light, thinking of Nora and Simon and all the rest.<br /><br />Thank you for your comment!<br /><br />--BradBrad Schulzhttps://www.blogger.com/profile/01852762873611487368noreply@blogger.comtag:blogger.com,1999:blog-3000721125799986885.post-78186563123155297062010-12-09T10:26:06.819-08:002010-12-09T10:26:06.819-08:00Awesome! This is one of the best sqlserver article...Awesome! This is one of the best sqlserver article I ever read. I can literally visualise how the query is being executed .. only downside - I do hesitate to fire a query immediately after another - hoping somewhere Stanley gets some sleep.Sabyasachi Mukherjeehttps://www.blogger.com/profile/04459662520963253374noreply@blogger.comtag:blogger.com,1999:blog-3000721125799986885.post-11436578073511873302010-12-08T10:10:28.538-08:002010-12-08T10:10:28.538-08:00This is just great.Wonderfully written. Very infor...This is just great.Wonderfully written. Very informative. I am sure it took a bit of work on your part to craft a nice tight story and your hard work was not wasted.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-3000721125799986885.post-70362649024599404342010-12-07T20:00:39.399-08:002010-12-07T20:00:39.399-08:00@Aashish and @Anonymous:
Thanks for the great fee...@Aashish and @Anonymous:<br /><br />Thanks for the great feedback! I'm so glad you enjoyed it.<br /><br />--BradBrad Schulzhttps://www.blogger.com/profile/01852762873611487368noreply@blogger.comtag:blogger.com,1999:blog-3000721125799986885.post-41368851639679662492010-12-07T16:47:30.888-08:002010-12-07T16:47:30.888-08:00That is simply the BEST way anyone has ever descri...That is simply the BEST way anyone has ever described the workings of a query!<br /><br />Thanks Brad!<br /><br />-AashishAashishhttps://www.blogger.com/profile/00632169663220329519noreply@blogger.comtag:blogger.com,1999:blog-3000721125799986885.post-62859993167692953192010-12-05T22:04:06.778-08:002010-12-05T22:04:06.778-08:00Awsome...Thats a very nice and simple way to expla...Awsome...Thats a very nice and simple way to explain what goes on inside the sql server engine when a query is firedAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-3000721125799986885.post-1610446237915515872010-11-22T08:54:25.695-08:002010-11-22T08:54:25.695-08:00@Wilfred: Parallel processing and twins: LOL. V...@Wilfred: Parallel processing and twins: LOL. Very good! Either that, or Nora's copying machine could be used to create clones.Brad Schulzhttps://www.blogger.com/profile/01852762873611487368noreply@blogger.com