3 Added a warning about the join hint
source | link

Try forcing a hash joinjoin*

SELECT TOP 1 
       dc.DOCUMENT_ID,
       dc.COPIES,
       dc.REQUESTOR,
       dc.D_ID,
       cj.FILE_NUMBER
FROM DOCUMENT_QUEUE dc
INNER HASH JOIN CORRESPONDENCE_JOURNAL cj
        ON dc.DOCUMENT_ID = cj.DOCUMENT_ID
       AND dc.QUEUE_DATE <= GETDATE()
       AND dc.PRINT_LOCATION = 2
ORDER BY cj.FILE_NUMBER

The optimizer probably thought a loop was going to be better with top 1 and that kind of makes sense but in reality it did not work here. Just a guess here but maybe the estimated cost of that spool was off - it uses TEMPDB - you may have a poorly performing TEMPDB.


* Be careful with join hints, because they force plan table access order to match the written order of the tables in the query (just as if OPTION (FORCE ORDER) had been specified). From the documentation link:

BOL extract

This may not produce any undesirable effects in the example, but in general, it very well might. FORCE ORDER (implied or explicit) is a very powerful hint that goes beyond enforcing order; it prevents a broad range of optimizer techniques being applied, including partial aggregations and reordering.

An OPTION (HASH JOIN) query hint may be less intrusive in suitable cases, since this does not imply FORCE ORDER. It does, however, apply to all joins in the query. Other solutions are available.

Try forcing a hash join

SELECT TOP 1 
       dc.DOCUMENT_ID,
       dc.COPIES,
       dc.REQUESTOR,
       dc.D_ID,
       cj.FILE_NUMBER
FROM DOCUMENT_QUEUE dc
INNER HASH JOIN CORRESPONDENCE_JOURNAL cj
        ON dc.DOCUMENT_ID = cj.DOCUMENT_ID
       AND dc.QUEUE_DATE <= GETDATE()
       AND dc.PRINT_LOCATION = 2
ORDER BY cj.FILE_NUMBER

The optimizer probably thought a loop was going to be better with top 1 and that kind of makes sense but in reality it did not work here. Just a guess here but maybe the estimated cost of that spool was off - it uses TEMPDB - you may have a poorly performing TEMPDB.

Try forcing a hash join*

SELECT TOP 1 
       dc.DOCUMENT_ID,
       dc.COPIES,
       dc.REQUESTOR,
       dc.D_ID,
       cj.FILE_NUMBER
FROM DOCUMENT_QUEUE dc
INNER HASH JOIN CORRESPONDENCE_JOURNAL cj
        ON dc.DOCUMENT_ID = cj.DOCUMENT_ID
       AND dc.QUEUE_DATE <= GETDATE()
       AND dc.PRINT_LOCATION = 2
ORDER BY cj.FILE_NUMBER

The optimizer probably thought a loop was going to be better with top 1 and that kind of makes sense but in reality it did not work here. Just a guess here but maybe the estimated cost of that spool was off - it uses TEMPDB - you may have a poorly performing TEMPDB.


* Be careful with join hints, because they force plan table access order to match the written order of the tables in the query (just as if OPTION (FORCE ORDER) had been specified). From the documentation link:

BOL extract

This may not produce any undesirable effects in the example, but in general, it very well might. FORCE ORDER (implied or explicit) is a very powerful hint that goes beyond enforcing order; it prevents a broad range of optimizer techniques being applied, including partial aggregations and reordering.

An OPTION (HASH JOIN) query hint may be less intrusive in suitable cases, since this does not imply FORCE ORDER. It does, however, apply to all joins in the query. Other solutions are available.

2 added 58 characters in body
source | link

Try forcing a hash join

SELECT TOP 1 
       dc.DOCUMENT_ID,
       dc.COPIES,
       dc.REQUESTOR,
       dc.D_ID,
       cj.FILE_NUMBER
FROM DOCUMENT_QUEUE dc
INNER HASH JOIN CORRESPONDENCE_JOURNAL cj
        ON dc.DOCUMENT_ID = cj.DOCUMENT_ID
       AND dc.QUEUE_DATE <= GETDATE()
       AND dc.PRINT_LOCATION = 2
ORDER BY cj.FILE_NUMBER

The optimizer probably thought a loop was going to be better with top 1 and that kind of makes sense but in reality it did not work here. Just a guess here but maybe the estimated cost of that spool was off. - it uses TEMPDB - you may have a poorly performing TEMPDB.

Try forcing a hash join

SELECT TOP 1 
       dc.DOCUMENT_ID,
       dc.COPIES,
       dc.REQUESTOR,
       dc.D_ID,
       cj.FILE_NUMBER
FROM DOCUMENT_QUEUE dc
INNER HASH JOIN CORRESPONDENCE_JOURNAL cj
        ON dc.DOCUMENT_ID = cj.DOCUMENT_ID
       AND dc.QUEUE_DATE <= GETDATE()
       AND dc.PRINT_LOCATION = 2
ORDER BY cj.FILE_NUMBER

The optimizer probably thought a loop was going to be better with top 1 and that kind of makes sense but in reality it did not work here. Just a guess here but maybe the estimated cost of that spool was off.

Try forcing a hash join

SELECT TOP 1 
       dc.DOCUMENT_ID,
       dc.COPIES,
       dc.REQUESTOR,
       dc.D_ID,
       cj.FILE_NUMBER
FROM DOCUMENT_QUEUE dc
INNER HASH JOIN CORRESPONDENCE_JOURNAL cj
        ON dc.DOCUMENT_ID = cj.DOCUMENT_ID
       AND dc.QUEUE_DATE <= GETDATE()
       AND dc.PRINT_LOCATION = 2
ORDER BY cj.FILE_NUMBER

The optimizer probably thought a loop was going to be better with top 1 and that kind of makes sense but in reality it did not work here. Just a guess here but maybe the estimated cost of that spool was off - it uses TEMPDB - you may have a poorly performing TEMPDB.

1
source | link

Try forcing a hash join

SELECT TOP 1 
       dc.DOCUMENT_ID,
       dc.COPIES,
       dc.REQUESTOR,
       dc.D_ID,
       cj.FILE_NUMBER
FROM DOCUMENT_QUEUE dc
INNER HASH JOIN CORRESPONDENCE_JOURNAL cj
        ON dc.DOCUMENT_ID = cj.DOCUMENT_ID
       AND dc.QUEUE_DATE <= GETDATE()
       AND dc.PRINT_LOCATION = 2
ORDER BY cj.FILE_NUMBER

The optimizer probably thought a loop was going to be better with top 1 and that kind of makes sense but in reality it did not work here. Just a guess here but maybe the estimated cost of that spool was off.