Seeks seeks seeks… The number of the beast. Seeks are cool though. I like seeks. Sometimes, at least. Perhaps some seeks are cooler than others. I just said seeks six times, now we’re ready to go.
Let’s begin by creating a temporary table that holds values from 1 to 10 000.
CREATE TABLE #T(id integer PRIMARY KEY CLUSTERED); WITH n(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM n WHERE n < 10000) INSERT INTO #T(id) SELECT n FROM n ORDER BY n OPTION (MAXRECURSION 10000);
Now, let’s assume that we need to retrieve a specific range of values – e.g. between 500 and 563. There are a couple of ways to do that, that’s not exactly rocket science, but let’s consider the following two approaches:
Using the BETWEEN operator:
SELECT Id FROM #X WHERE Id BETWEEN 500 AND 563
Using the IN operator:
SELECT Id FROM #X WHERE Id in ( 500,501,502,503,504,505,506,507,508,509,510,511,512, 513,514,515,516,517,518,519,520,521,522,523,524,525, 526,527,528,529,530,531,532,533,534,535,536,537,538, 539,540,541,542,543,544,545,546,547,548,549,550,551, 552,553,554,555,556,557,558,559,560,561,562,563 )
Uh, truly fascinating.
But, behind these boring and functional equivalent queries, there is a dirty seekret – Wow, I’m on fire today. Both result in a Clustered Index Seek, but one is sus. Discuss!
That’s quite a difference. The second query results in 64 times more logical reads. But why?
One Seek To Rule Them All
In the execution plan of the query that uses the BETWEEN operator, it’s possible to notice that there’s only one seek operation, which range starts at 500 and then traverses the leaf level of the index, until it finds a row beyond the range limit – 563 in this case.
The Meeseeks Box
The seek #2, in the execution plan of the query that uses the IN operator is rather different:
The list continues down to a total of 64 individual seeks.
The underlying reason for the 128 logical reads is related to the size of our table and consequently, the size of the index. Since it is large enough to need a separate root page, each one of the 64 seeks will imply 2 logical reads.
But I digress, the bottom line here is that Seek #2 is the impostor. It is not actually a seek, but a whole bunch of them.