Stairway to SQL Server Indexes: Step 9, Reading Query Plans

  • Comments posted to this topic are about the item Stairway to SQL Server Indexes: Step 9, Reading Query Plans

  • In the 'Viewing Parallel Streams' section you claim that both 'streams' are read in parallel. A hash join is a partially-blocking iterator: it consumes all rows from the build input to create the hash table, and then probes for matching rows one at a time from the probe input. This is clearly documented in Books Online - see Understanding Hash Joins.

  • Greetings,

    As I have just read this part of articles. A question arise in my mind 😀 about figure 5. As my I understanding after reading untill part 9 is:

    with query below

    SELECT C.LastName, C.FirstName, C.MiddleName, C.Title, H.SalesOrderID, H.OrderDate

    FROM Person.Contact C

    JOIN Sales.SalesOrderHeader H ON H.ContactID = C.ContactID

    WHERE Suffix is null

    ORDER BY Title

    I think the query plan should be still like figure 4 (not changing). This is because index IX_ContactID which added later does not give more advantage (lack field SalesOrderID to show for final output). How come with new index IX_ContactID and Clustered Index of table Contact, we can produce field SalesOrderID for final result?

    Please give me enlightenment here :-).

  • I think the index has all it needs, including SalesOrderID column, because this column is in the bookmark of the index.

    Bookmarks of non-clustered indexes are values of clustered index if the table is clustered index.

    You can check that easy - just replace it in SELECT statement with any other column of SalesOrderHeader table. It will be again parallized.

  • Can you please tell me how did you find looking at execution plan 'Contact' rows are problem?

    "The new plan also shows us that the increased number of Contact rows has caused the Match and Sort operations to become the critical path for this query."

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply