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.
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:
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:
In this case, both queries read additional rows from the index seek.
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.
Thanks, parameter sniffing.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on 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.