Thanks for the article.
I went ahead and ran the code in Listing 9-3 on SQL Server 2012 instance, and checked the execution plan with and without the optimizer, but did not see anything like Figure 9-6.
Figure 9-6 seems completely off because it shows Physical operation : Hash Match, yet Logical operation: Inner Join. I don't see such operator in the execution plan of any of the code in the List 9-3. Any "Hash Match" I have seen so far has the logical operation of "Aggregation", not "Inner Join", so I am confused here.
Thanks, again, for your kind words! I am glad that you like this stairway series.
There ios often more that I want to include in a level than I have room for, so I sometimes have to cut my descriptions a bit short. The part where I show the batch mode bailout due to a hash spill is one part where clarity has suffered because of this. I probably should have cut the listing into three parts, and I should have included a screenshot of the full execution plan I got, with an arrow to indicate where you can see the bailout happening.
So for your understanding, the code in listing 9-3 consists of three parts.
The first part runs up to and including the line that reads "GO 10", and this is setup only. Run it once, wait until it finishes, then your system is ready for the actual repro.
The second part starts at the comment line "-- Force the bailout (SQL Server 2012 only)", and ends at "OPTION (OPTIMIZE FOR (@MinManufacturer = 'Y'));" (or at the "GO" just after that). This is the part that will show the bailout. You should run this *after* the setup part is done; and with the "Include Actual Execution Plan" option enabled. You need to run the code with an actual plan; an estimated plan will never show the bailout. You can repeat this part multiple times if you wish, until you do the cleanup.
The third and last part is just the "DROP TABLE dbo.TmpProduct;" line, which removes the temporary table created in the setup part.
On my system, running the second part (after the setup has completed) takes a few minutes, and produces the actual execution plan I attached to this post. There are three operators where you can see the bailout: the Columnstore Index Scan and the two Hash Match operators all show actual execution mode row vs estimated batch (marked with arrows in the attachment). The picture in figure 9-6 is from one of the two Hash Match operators (the one with the circle in the attached picture).
If you still do not see this on your system, then can you please in the actual execution plan right-click somewhere in the white space, select "Save execution plan as", save it somewhere with the default .sqlplan extension, then reply to this post and attach the saved execution plan to that post? That will allow me to open up the plan on my system, look into all the properties and see if I can find out why you got a different execution plan from what I have.