29Sep 2008 by Grant Fritchey No Comments

Dissecting SQL Server Execution Plans at PDC

PASS, SQL Server, T-SQL
I just got word that Red Gate has printed more copies of the book that they'll be distributing at the Microsoft Professional Developers Conference that's taking place in LA. I just wish we had a 2008 version of the book now because, while most of it is still applicable, there's more that can be done with execution plans now. Have I mentioned I think the missing index information that's displayed with the statement text in the graphical execution plan in SQL Server 2008 is pretty slick? Well it is. I hope they're going to distribute it at the PASS Summit this year too. I think they are, but I don't know that for a fact.
Read More
29Sep 2008 by Grant Fritchey 3 Comments

Loop Joins, More rows on top or bottom?

T-SQL
I've seen this question come by in the web searches multiple times. The nested loop join is also called an iterative join. This is because it takes the rows from the inner part of the join and compares them through an iterative process (one-by-one) to the rows in the outer part of the join. So, if the optimizer has correctly chosen this operation for your query, you should see FEWER rows in the top, or outer, part of the join and MORE rows in the bottom, or inner, part of the join. Take this query as an example (run against AdventureWorks2008): SELECT * FROM [Sales].[SalesOrderHeader] soh JOIN [Sales].[SalesOrderDetail] sod ON soh.[SalesOrderID] = sod.[SalesOrderID] WHERE soh.[SalesOrderID] = 47716 Here we have a single row from the SalesOrderHeader table and 55 rows…
Read More