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

High I/O

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

enter image description here

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 
            , 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
            prism72ext.dbo.pb_WorkRequestService wrs 
            Join prism72ext.dbo.pb_WorkRequest wrx on (wrs.WorkRequestId = wrx.Id and wrx.Status = 'Incomplete')
            left join (
                    ,   d.Name AS DepartmentName
                    ,   u.UserName
                    , wl.CreatedDate as WRLCreateDateTime
                    , cast(wl.Notes as varchar(2500)) as WRLNotes
                            MAX( Id ) AS Id
                                prism72ext.dbo.pb_WorkRequestLog WITH(INDEX(0))
                        GROUP BY 
                    ) 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?

  • Comments are not for extended discussion; this conversation has been moved to chat.
    – Paul White
    Aug 29, 2017 at 11:03

2 Answers 2


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.

  • Hi shriop, I have that index, but without the DESC. I made the changes you suggest, but it didn't make a lot of difference. I've updated the question with a solution. Could you take a look at the solution and indicate if you think it is appropriate.
    – Alan T
    May 22, 2014 at 8:20
  • It depends on how core to your system this query is. It's probably fairly appropriate as a solution to a problem you're trying to solve with limited context over the internet. Generally, hints are always to be avoided and tend to "hint" at an underlying problem, but it's probably something that I would have to sit at your server for an hour or so running queries to solve better. Every blue moon, there tends to be something that seems like it almost requires a hint, but I still feel that it probably even shows my own gaps when that happens, not that it's technically needed. May 22, 2014 at 15:51
  • If it's really core to your system, I'd suggest moving with that "fix" to production, but continuing to research and understand why, depending on how much time you have to invest. May 22, 2014 at 15:52
  • It seems you are looking for the "latest" (i.e. having the highest ID) record in pb_WorkRequestLog for a particular WorkRequestId. May be in the presence of that index above a window function (ROW_NUMBER() OVER (PARTITION BY pb_WorkRequestLog ORDER BY ID DESC) will perform better.
    – mustaccio
    Aug 5, 2015 at 19:46

using INDEX(0) hint forces the clustered index scan. If you want to avoid it, you have to remove the hint. TABLE HINTS:

If a clustered index exists, INDEX(0) forces a clustered index scan and INDEX(1) forces a clustered index scan or seek. If no clustered index exists, INDEX(0) forces a table scan and INDEX(1) is interpreted as an error.

You could try also to create a nonclustered index on WorkRequestId and include the CreatedDate and Notes columns using INCLUDE clause - bear in mind this can have negative effect on the insert performance and the size of the table.

For what is worth, hardcoding database name in queries makes the code extremely hard to move to testing environments, unless you can afford single database per server instance.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.