InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 19:19:51 GMT20http://www.sqlservercentral.com/Forums/Topic981280-2669-1.aspxThanks for taking the time to discuss this, I feel strongly about it and love learning more on this topic. If possible, as you gain expertise, would you mind updating your blog with more information? It is extremely helpful for me<a href="http://www.yorkshiredentistry.co.uk/">dentist wakefield</a>Thu, 09 Sep 2010 23:15:22 GMTmilton.seo.linkhttp://www.sqlservercentral.com/Forums/Topic981280-2669-1.aspx[quote][b]Douglas Osborne-456728 (9/9/2010)[/b][hr]I knew you were in rarefied air when a Google search on 'NormalizeGbAgg SQL' returned 36 results and Bing returned 2 - both yours! 10 Star articleDoug[/quote]That's very kind of you, Doug, thanks!Thu, 09 Sep 2010 08:51:25 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic981280-2669-1.aspxNice article - good to see it published at SSC.Thu, 09 Sep 2010 08:42:00 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic981280-2669-1.aspxPaul,I knew you were in rarefied air when a Google search on 'NormalizeGbAgg SQL' returned 36 results and Bing returned 2 - both yours!10 Star articleDougThu, 09 Sep 2010 07:51:49 GMTDouglas Osborne-456728http://www.sqlservercentral.com/Forums/Topic981280-2669-1.aspx+Thu, 09 Sep 2010 04:25:04 GMTonkelfelahttp://www.sqlservercentral.com/Forums/Topic981280-2669-1.aspxMe likes :)Wed, 08 Sep 2010 07:24:53 GMTpeter-757102http://www.sqlservercentral.com/Forums/Topic981280-2669-1.aspx[quote][b]Paul White NZ (9/8/2010)[/b]What I am doing in this first part, is showing you what horrible plans can be produced if we prevent the optimizer from using all of its tricks.The point is to show the process the optimizer goes through when searching for a great query plan.[/quote]Okay, I get it now. BTW 'mention' can also be used as a noun, so Jeff's mention is grammatically correct. :D- arjunWed, 08 Sep 2010 07:03:43 GMTArjun Sivadasanhttp://www.sqlservercentral.com/Forums/Topic981280-2669-1.aspx[quote][b]Arjun Sivadasan (9/8/2010)[/b][hr]I saw Jeff mention that rules should be enabled only on a test server. Does it mean that the improvements, that you make to queries this way, cannot be used in a real-world scenario?[/quote]Nope, the optimizer normally produces a very good plan by using [i]all[/i] of the rules.What I am doing in this first part, is showing you what horrible plans can be produced if we prevent the optimizer from using all of its tricks.The point is to show the process the optimizer goes through when searching for a great query plan.Part 2 will be published in a few hours. In fact it's already an hour late in my time zone :-DWed, 08 Sep 2010 06:55:10 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic981280-2669-1.aspx[quote][b]Jeff Moden (9/8/2010)[/b][hr]I also recommend keeping track of the parts of the series... when you read part 4, you'll be itching to start from the beginning again. :hehe:[/quote]I'll try to remember to include direct links to the previous parts in the opening comment of the discussion thread. That said, Steve is normally pretty good about adding links to previous parts to the article text too, so I'll check that first.Wed, 08 Sep 2010 06:46:19 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic981280-2669-1.aspxIt's amazing how you 'uncomplicate' things. That's what I like the most about your posts. I saw Jeff's mention that rules should be enabled only on a test server. Does it mean that the improvements, that you make to queries this way, cannot be used in a real-world scenario? Thanks for the links :-)- arjunWed, 08 Sep 2010 06:45:12 GMTArjun Sivadasanhttp://www.sqlservercentral.com/Forums/Topic981280-2669-1.aspx[quote][b]hrc_public (9/8/2010)[/b][hr]Hope you're alright down there (:hehe:). I can see it has been a bit shaky in Christchurch some 350km away.[/quote]Yes we're fine - ChCh is going to need a bit of work though. All very sad.Wed, 08 Sep 2010 06:43:32 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic981280-2669-1.aspxThanks everyone. I'll look forward to the next articles. Hope you're alright down there (:hehe:). I can see it has been a bit shaky in Christchurch some 350km away.Wed, 08 Sep 2010 05:51:54 GMThrchttp://www.sqlservercentral.com/Forums/Topic981280-2669-1.aspx[quote][b]Anam Verma (9/7/2010)[/b][hr]Paul,In the part1, you have mentioned about SELonJN operation, how it can be enabled?It will be really handly if you'll also post the changes you made to the indexes or query to achieve the better performing execution plans.[/quote]I had the same problem when I first read Paul's series. The point to understand here is that Paul is not actually modifying the query in this first part... instead, he's actually enabling and disabling rules in SQL Server and running the same query. I'm thick... it took until part 4 for me to figure that out. ;-)As he said, he'll get to how to do that later. I recommend that if you ever do that, you do it only on a test server. :-)I also recommend keeping track of the parts of the series... when you read part 4, you'll be itching to start from the beginning again. :hehe:Wed, 08 Sep 2010 05:04:10 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic981280-2669-1.aspx[quote][b]hrc_public (9/8/2010)[/b][hr]Great article, great topic. I belong to the newcomer group and as such I would have liked to see how those rules you talk about, were executed. You move the "where" into the join, but in the end you just mention some rules that puts the Cherry on top of it - and I don't understand what you did.[/quote]I hope the later parts will make things clearer, though this is moderately advanced stuff so it might take a while to fully 'get it'.Wed, 08 Sep 2010 03:19:44 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic981280-2669-1.aspxThank you, Paul. This is one of the topics I always wanted to investigate deeper, but never did.I look forward to reading the next parts. I will try to avoid annoying you with my questions and post them once I got through the whole series, as I'm sure I will find the answers along the way.Great, as usual!Wed, 08 Sep 2010 01:34:50 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic981280-2669-1.aspxGreat article, great topic. I belong to the newcomer group and as such I would have liked to see how those rules you talk about, were executed. You move the "where" into the join, but in the end you just mention some rules that puts the Cherry on top of it - and I don't understand what you did.As a newcomer to the optimizer it is not all clear that the symbols in the execution plan mean table scans or Cartesian products. I can see they cost a lot but not what they in are (well, actually I can but ... I had to think first!). Maybe some kind of symbol explanation could be added? .. or maybe this is found elsewhere in some of the many links you added.(and I now find that most questions was found on page two in some of the newest posts)Wed, 08 Sep 2010 01:01:00 GMThrchttp://www.sqlservercentral.com/Forums/Topic981280-2669-1.aspx[quote][b]Anam Verma (9/7/2010)[/b][hr]In the part1, you have mentioned about SELonJN operation, how it can be enabled? It will be really handly if you'll also post the changes you made to the indexes or query to achieve the better performing execution plans.[/quote]All covered in the remaining parts of the series, Anam :-)Tue, 07 Sep 2010 21:02:22 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic981280-2669-1.aspxPaul,In the part1, you have mentioned about SELonJN operation, how it can be enabled?It will be really handly if you'll also post the changes you made to the indexes or query to achieve the better performing execution plans.Tue, 07 Sep 2010 20:28:35 GMTAnam Vermahttp://www.sqlservercentral.com/Forums/Topic981280-2669-1.aspxThanks for the excellent article! It was very clear and informative!I think a deeper understanding of how the optimizer works helps us to write better queries.Looking forward to the rest of the series.Tue, 07 Sep 2010 13:53:56 GMTGoldie Lesserhttp://www.sqlservercentral.com/Forums/Topic981280-2669-1.aspxExcellent job, Paul!I'm sure people will love the next 3. I know I learned a few things.Tue, 07 Sep 2010 13:29:10 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic981280-2669-1.aspxGreat stuff. Looking forward to part 2 on Thursday.Tue, 07 Sep 2010 13:23:07 GMTNickBalaamhttp://www.sqlservercentral.com/Forums/Topic981280-2669-1.aspx[quote][b]Paul White NZ (9/7/2010)[/b][hr][quote][b]WayneS (9/7/2010)[/b][hr]Have you got some references you can pass on about how the optimizer works?[/quote]For sure:Craig Freedman: [url]http://blogs.msdn.com/b/craigfr/[/url]SQL Server Storage Engine: [url]http://blogs.msdn.com/b/sqlserverstorageengine/[/url]CSS: [url]http://blogs.msdn.com/b/psssql/[/url]Query Processing: [url]http://blogs.msdn.com/b/sqlqueryprocessing/[/url]White Papers: [url]http://technet.microsoft.com/en-us/sqlserver/bb671430.aspx[/url]SQLCAT: [url]http://blogs.msdn.com/b/sqlcat/[/url]Conor vs. SQL: [url]http://blogs.msdn.com/b/conor_cunningham_msft/[/url]Programmability & API: [url]http://blogs.msdn.com/b/sqlprogrammability/[/url]Bart Duncan: [url]http://blogs.msdn.com/b/bartd/[/url]Conor @ SQLskills: [url]http://www.sqlskills.com/blogs/conor/[/url]QO Team: [url]http://blogs.msdn.com/b/queryoptteam/[/url]Joe Chang: [url]http://www.qdpma.com/CBO/SQLServerCostBasedOptimizer.html[/url]The SQL Server Internals books edited by Kalen Delaney are also excellent sources. Optimizer stuff is covered particularly well in 2008 Internals (Conor Cunningham) and 2005 Query Tuning & Optimization (Craig Freedman).[/quote]BWAA-HAAA!!!! Been at this for a while, huh? :-PI haven't changed my mind since the first time I saw this series and agree that 5 stars isn't enough. Glad you brought it to SSC. Thanks, Paul.Tue, 07 Sep 2010 11:16:17 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic981280-2669-1.aspx[quote][b]Chris Morris-439714 (9/7/2010)[/b][hr]Feature-packed and very readable article as always Paul.It's a little like watching your favourite tv program - just as you're really getting into it, you reach the end and the credits roll. Can't wait for the next installment :-D[/quote]Thank you, Chris. Steve did an excellent job with the scheduling on this four-part series, so you'll get Part 2 on Thursday, and Parts 3 & 4 on Tuesday & Thursday next week. The graphics are best in Part 1, but the content gets better in the later parts :-)Tue, 07 Sep 2010 11:12:06 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic981280-2669-1.aspx[quote][b]WayneS (9/7/2010)[/b][hr]Have you got some references you can pass on about how the optimizer works?[/quote]For sure:Craig Freedman: [url]http://blogs.msdn.com/b/craigfr/[/url]SQL Server Storage Engine: [url]http://blogs.msdn.com/b/sqlserverstorageengine/[/url]CSS: [url]http://blogs.msdn.com/b/psssql/[/url]Query Processing: [url]http://blogs.msdn.com/b/sqlqueryprocessing/[/url]White Papers: [url]http://technet.microsoft.com/en-us/sqlserver/bb671430.aspx[/url]SQLCAT: [url]http://blogs.msdn.com/b/sqlcat/[/url]Conor vs. SQL: [url]http://blogs.msdn.com/b/conor_cunningham_msft/[/url]Programmability & API: [url]http://blogs.msdn.com/b/sqlprogrammability/[/url]Bart Duncan: [url]http://blogs.msdn.com/b/bartd/[/url]Conor @ SQLskills: [url]http://www.sqlskills.com/blogs/conor/[/url]QO Team: [url]http://blogs.msdn.com/b/queryoptteam/[/url]Joe Chang: [url]http://www.qdpma.com/CBO/SQLServerCostBasedOptimizer.html[/url]The SQL Server Internals books edited by Kalen Delaney are also excellent sources. Optimizer stuff is covered particularly well in 2008 Internals (Conor Cunningham) and 2005 Query Tuning & Optimization (Craig Freedman).Tue, 07 Sep 2010 10:58:54 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic981280-2669-1.aspxIt's too bad we're limited to just 5 stars...Great article Paul.Have you got some references you can pass on about how the optimizer works?Tue, 07 Sep 2010 10:24:55 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic981280-2669-1.aspx[quote][b]jim.jaggers1 (9/7/2010)[/b][hr]Can you help me understand the difference in the Nested Loops operator between the first and second plan? Other than the '!' point warning symbol in the first plan they look the same to me. How can you tell one is doing a Cartesion Join while the other is doing an Inner join?[/quote]The exclamation point is shown where compiler warnings occur. In this case the warning is: "No Join Predicate". A cartesian product is a join with no join predicate. The other visual clue is that the size of the arrow on the output of the join is *huge* - indicating a very large number of rows.Tue, 07 Sep 2010 09:13:38 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic981280-2669-1.aspx[quote][b]george sibbald (9/7/2010)[/b][hr]How do you turn off rules in the optimiser?[/quote]Everything (in detail) will be revealed in subsequent parts...stay tuned :-)Tue, 07 Sep 2010 09:08:47 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic981280-2669-1.aspxI agree with the previous comments - great article![quote][b]jim.jaggers1 (9/7/2010)[/b][hr]Can you help me understand the difference in the Nested Loops operator between the first and second plan? Other than the '!' point warning symbol in the first plan they look the same to me. How can you tell one is doing a Cartesion Join while the other is doing an Inner join?[/quote]The exclamation point symbol is not related. This simply indicates an warning from the optimzier - usually an indication of missing statistics.In the exectution plan, you can find the difference by checking the properties of the operators (you can see them by hovering your mouse over them, or by right-clicking, selecting "properties", then clicking the operators you want to check).If the filtering is done before the join, then you will see a "predicate" property on the scan before (to the right of) the join operator. (Or a "seek predicate" property if it's a seek). In this case, the join itsself is technically still a cartesian join, but on pre-filtered inputs (as if you write ... FROM (SELECT ... WHERE ...) AS a JOIN (SELECT ... WHERE ...) AS b ON ...)If the filtering is done during the join, then you will see a "predicate" property and/or "outer references" property on the join operator. This is a true non-cartesian join (inner join, unless the operator is an outer join operator).If the filtering is done after the join, you'll see a "predicate" or similar property on one of the operators after (to the left of) the join operator. Usually a filter operator. In these cases, the join was a "true" cartesian product.Tue, 07 Sep 2010 08:01:32 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic981280-2669-1.aspxshould make for an interesting series.How do you turn off rules in the optimiser? Or is that something its best not to know? (or put in print) :-)Tue, 07 Sep 2010 07:39:41 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic981280-2669-1.aspxGreat article. I look forward to the series.Can you help me understand the difference in the Nested Loops operator between the first and second plan? Other than the '!' point warning symbol in the first plan they look the same to me. How can you tell one is doing a Cartesion Join while the other is doing an Inner join?Tue, 07 Sep 2010 07:35:45 GMTSQL Curioushttp://www.sqlservercentral.com/Forums/Topic981280-2669-1.aspxFeature-packed and very readable article as always Paul.It's a little like watching your favourite tv program - just as you're really getting into it, you reach the end and the credits roll. Can't wait for the next installment :-DTue, 07 Sep 2010 02:56:13 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic981280-2669-1.aspxComments posted to this topic are about the item [b][url=http://www.sqlservercentral.com/articles/SQL+Server+2008/71019/]Inside the Optimizer: Constructing a Plan - Part 1[/url][/b]Link to [url=http://www.sqlservercentral.com/articles/SQL+Server+2005/71020/]Part 2[/url]Link to [url=http://www.sqlservercentral.com/articles/SQL+Server+2005/71021/]Part 3[/url]Link to [url=http://www.sqlservercentral.com/articles/SQL+Server+2005/71022/]Part 4[/url]Mon, 06 Sep 2010 20:11:40 GMTPaul White