Secrets of SQL Server Execution plan (4/5)

Haja Batcha

Hi guys, thanks for traveling with me along the series of post to explore the SQL server Execution Plan Secrets. For the people those who missed out some basics, no worries here you go. limitations of execution plan, some alarming signs and complex execution plans

Really we are going to see some interesting things on this post. Most of the time, while we examine the execution plan of a query which join two or more tables, we used to saw the join operators –nested loop join, hash match and merge join operator . We often not concentrate, why it is using hast match join etc. Which was prime subject of our post today, also we will see how resolve the warnings on our execution plan.

Okay, let us see some basics about this join operators before step into details.  For the people those who saw the below image and puzzled, no more such situation.

	Nested Loops:-  If you want to understand the nested loop join in a simplest way, imagine you have two streams of data rows, uses one join input as the outer input table (shown as the top input in the graphical execution plan) and one as the inner (bottom) input table. The outer loop consumes the outer input table row by row. The inner loop, executed for each outer row, searches for matching rows in the inner input table.
	Hash Match:-  Hast match has two phase: the build phase and probe phase. Better to be explained with a example. Consider you are retrieving the rows from table A & B, during first phase the values needed to be extracted will be collected which is has build phase. The data collected on the hash buckets. On the second the phase was the probe phase, on which the collected data will be matched with the hash buckets.
	Merge join:-  Merge join will be used by the execution plan when there is two set of ordered results exists from table A & B.  which are defined by the equality (ON) clauses of the join predicate. The query optimizer typically scans an index, if one exists on the proper set of columns, or it places a sort operator below the merge join.

Let us move on to see some real demos on the three join types one by one. I use Adventureworks2012 database for our demo

SELECT

P1.FirstName,

P1.LastName,

P2.PhoneNumber

FROM      Person.Person P1

INNER JOIN Person.PersonPhone P2        ON P1.BusinessEntityID = P2.BusinessEntityID

The Query will generate a execution plan like below. Check the index used by the personphone table, it has phone number on the key column,  as i explained before it was the build phase, which builds the data using this index on the hash buckets. And the second phase was the probe phase, which probes the matching records using other index on the  Person table. You can mouse over the Hast Match operator to see more details on it..

Now we will see, when will get a warning symbol and how to resolve it. Hash bucket, which was used on the hash match will reside on memory, if the statistics are not update the optimizer will grant 1MB memory instead of 8MB of memory. Just mouse over on the Select icon which was on the beginning of the execution plan. You will see a Memory grant as displayed on image beside here. Once the memory granted was not enough, because of the statics not updated, the SQL Server has to spill the hash table into the temp table means it make additional IO to the query which degrades the performance of the query.  To resolve this kind warning symbols over the hash match join we need update the statistics of the participant tables, which enables the optimizer to grant the correct memory to accommodate the hash tables on memory.

Thanks for your time, and the game is not over yet. On next post we will see how we can make the optimizer to use different execution plan. Meaning you will see the same query i demonstrated above, will use nested loop and merge joins. By the end of next post, you will be able to change the execution plan by supplying some index, by which the join operator used by the optimizer will differ accordingly..

Would like to see your comments and feed backs as well