Common Query Plan Patterns For Joins: Prefetching Lookups

Counterpoint


Shockingly, there hasn’t been a lot written about Prefetching. Hard to imagine why.

Women want it, men want to be it, James Bond goes to see movies about it, and so forth.

The few reliable sources of information out there are these articles:

One sort of interesting point about prefetching is that it’s sensitive to parameter sniffing. Perhaps someday we’ll get Adaptive Prefetching.

Until then, let’s marvel at at this underappreciated feature.

Neckline


The difference between getting ordered and unordered prefetch typically comes down to the data needing to be ordered for one reason or another.

It might be an order by, or some other operator that expects or preserves ordered input.

The easiest way to see that is just to use order by, like so:

/*Unordered prefetch*/
SELECT TOP (1000)
    p.*
FROM dbo.Posts AS p
WHERE p.CreationDate < '20100801';
GO

/*Ordered prefetch*/
SELECT TOP (1000)
    p.*
FROM dbo.Posts AS p
WHERE p.CreationDate < '20100801'
ORDER BY p.CreationDate;
GO

Here’s what we get from the nested loops properties:

meating

You may notice that the top query that uses unordered prefetch read 1399 rows from the seek, and takes 43ms vs the ordered prefetch query’s 1000 rows and 5ms runtime.

That tends to happen with a cold cache (DBCC DROPCLEANBUFFERS;) , and it exacerbated in parallel plans:

worsened

In this case, both queries read additional rows from the index seek.

Shopkeep


But what about all that parameter sniffing stuff? Check this out.

CREATE OR ALTER PROCEDURE dbo.Dated
(
    @date datetime
)
AS
SET NOCOUNT, XACT_ABORT ON;
BEGIN

    DBCC DROPCLEANBUFFERS;

    SELECT TOP (1000)
    	p.*
    FROM dbo.Posts AS p WITH(INDEX = x)
    WHERE p.CreationDate < @date
    ORDER BY p.Score DESC
    OPTION(MAXDOP 1);

END;

I have an index and MAXDOP hint in there so we do an equal comparison across executions.

EXEC dbo.Dated
    @date = '20080731';
GO 

EXEC dbo.Dated
    @date = '20190801';
GO

If we run the “small” version first, we won’t get the benefit of readaheads. This query runs for a very long time — nearly 2 hours — but most of the problem there is the spill at the end. If we run the “big” version first, we get unordered prefetch, and the time is significantly improved.

editorial

Thanks, parameter sniffing.

Thanks for reading!



Leave a Reply

Your email address will not be published. Required fields are marked *