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

Question

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

David Durant

Default port

Points: 1422
More actions
March 28, 2011 at 3:53 pm

#248794

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

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

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

Paul White SSC Guru Points: 150467 More actions · Answer 1

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.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

permono.sam Old Hand Points: 336 More actions · Answer 2

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 :-).

kagi.jagi SSC Enthusiast Points: 182 More actions · Answer 3

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.

Tac11 SSCertifiable Points: 6980 More actions · Answer 4

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."