Common Query Plan Patterns For Joins: Prefetching Lookups

Posted on by Erik Darling

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:

SQL Server Query Plan
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:

SQL Server Query Plan
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.

SQL Server Query Plan
editorial

Thanks, parameter sniffing.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Related Posts