March 28, 2011 at 3:53 pm
Comments posted to this topic are about the item Stairway to SQL Server Indexes: Step 9, Reading Query Plans
October 5, 2011 at 4:52 am
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.
November 4, 2011 at 12:38 am
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 :-).
April 23, 2015 at 4:14 am
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.
July 1, 2015 at 7:35 am
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