High I/O for part of query I have a large query that I'm trying to tune. I write lots of queries but haven't done a lot of tuning. I've included a screenshot from SQL Sentry Plan Explorer Free (SSPEF):



In the above part of the plan the table pb_WorkRquestLog contains 229,001 rows. However, the query plan is showing approx. 348 million rows (229,001 x 1,520 iterations):



There is no where clause so the query is using a Clustered Index Scan. I've rebuild all indexes with FULLSCAN and updated all statistics.

The code that this part of the plan is executing is:

select distinct 
            wrs.ServiceKey 
            , owner.DepartmentName AS GroupName
            , owner.UserName AS UserId
            , owner.WRLCreateDateTime
            , owner.WRLNotes
            , wrx.CreatedDate as WRCreateDateTime
            , wrx.Id
            , wrx.Description 
            , cast(wrx.Notes as varchar(2500)) as Notes
        from    
            prism72ext.dbo.pb_WorkRequestService wrs 
            Join prism72ext.dbo.pb_WorkRequest wrx on (wrs.WorkRequestId = wrx.Id and wrx.Status = 'Incomplete')
            left join (
                select
                    wl.WorkRequestId
                    ,   d.Name AS DepartmentName
                    ,   u.UserName
                    , wl.CreatedDate as WRLCreateDateTime
                    , cast(wl.Notes as varchar(2500)) as WRLNotes
                from
                    (
                        SELECT     
                            MAX( Id ) AS Id
                        FROM          
                                prism72ext.dbo.pb_WorkRequestLog WITH(INDEX(0))
                        GROUP BY 
                            WorkRequestId
                    ) mwl
                    join prism72ext.dbo.pb_WorkRequestLog wl on mwl.Id = wl.Id
                    join prism72Ext.dbo.pb_Department d ON wl.DepartmentId = d.DepartmentId
                    left join prism72Ext.dbo.pb_User u on wl.UserId = u.UserId
                ) owner on wrs.WorkRequestId = owner.WorkRequestId


SSPEF is reporting the Actual Data Size is nearly 5GB, with 3 million Logical Reads! SSMS reports the table to be only 16 MB in size. 

I've tuned the query down from 4m28s to 1m35s, but now I'm stuck. I'd be grateful if anyone could point me in the right direction to deal with this.

Edit: Someone suggested trying "OPTION (HASH JOIN, MERGE JOIN)". This made a dramatic difference. I/O was massively reduced and the query ran in 13 seconds. 

Does anyone see any issues with this solution?
If you don't already have one, create a composite nonclustered index on prism72ext.dbo.pb_WorkRequestLog(WorkRequestId, Id DESC) and run the query without the index hint. 

Also, have your inner select for the owner derived table return mwl.WorkRequestId instead of ml.WorkRequestId.