Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stairway to SQL Server Indexes: Step 9, Reading Query Plans Expand / Collapse
Author
Message
Posted Monday, March 28, 2011 3:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, June 23, 2012 10:54 AM
Points: 8, Visits: 23
Comments posted to this topic are about the item Stairway to SQL Server Indexes: Step 9, Reading Query Plans
Post #1085228
Posted Wednesday, October 5, 2011 4:52 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Monday, July 27, 2015 4:12 AM
Points: 9,932, Visits: 11,285
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
SQLblog.com
@SQL_Kiwi
Post #1185771
Posted Friday, November 4, 2011 12:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 5, 2015 7:35 PM
Points: 18, Visits: 127
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 .

Post #1200394
Posted Thursday, April 23, 2015 4:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 5:20 AM
Points: 1, Visits: 181
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.
Post #1679546
Posted Wednesday, July 1, 2015 7:35 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:51 AM
Points: 220, Visits: 860
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."
Post #1699342
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse