Erik Darling leaves no man behind and is seeking Sarge. On day one, Erik briefs the crew:
SARGability is the in-club way of saying that a search predicate(s) can be used to seek through the key(s) of an index.
On day two, the crew use the power of math to get past a blockade:
Let’s say we’re doing this to audit short questions and answers for quality.
Since SQL Server doesn’t retain any precise data about string column lengths, we don’t have an effective way to implement this search.
Worse, since the Body column is a max datatype, no expression (SARGable or not) can be pushed to the index scan.
On day three, they enter the fetid jungles of tempdb:
In all, the query runs for about 50 seconds. This can be avoided by hinting a hash join, of course, for reasons explained here.
But good luck figuring out why this thing runs for 50 seconds looking at a cached, or estimated execution plan, which doesn’t show you spills or operator times.
Stay tuned for the thrilling conclusion to Seeking SARG.