Bertwagner.com Website Review

Everyone has their own method of reading an execution plan when performance tuning a slow SQL query.  One of the first things I like These three little icons may not seem like the most obvious place to begin troubleshooting a slow query, but with larger plans especially I like starting with a quick glance at the join operators because they allow you to infer a lot about what SQL Server thinks about your data. This will be a three part series where we’ll learn how each join algorithm works and what they can reveal about our upstream execution Nested loops joins work like this: SQL Server takes the first value from our first table (our “outer” table – by default SQL Server and compares it to every value in our second “inner” table to see if they match.  Once every inner value has been checked, SQL Server moves to the next value in the outer table and the process repeats until every value from our outer table has been compared to every value in our inner table. This description is a worst case example of the performance of a nested loop join.  Several optimizations exist that can make the join more efficient.  For example, if the inner table join values are sorted (because of an index you created or a spool that SQL Server In the above animation, SQL Server has the inner input data sorted, allowing it to seek directly to the rows it needs, reducing the total For more in-depth explanations of the internals and optimizations of nested loops joins, I recommend reading this post by Craig Freedman Knowing the internals of how a nested loops join works allows us to infer what the optimizer thinks about our data and the join’s upstream operators, helping us focus our performance tuning efforts.  Here are a few scenarios to consider the next time you see a nested loops join being used in your execution plan: have SQL Server provide better estimates (and maybe a more appropriate join). animation above), and SQL Server might choose a more efficient operator if the inputs are both sorted.