Not All Seeks Are Created Equal #1

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.



Categorias:#sqlserverperformance, executionplans

Etiquetas:, ,

2 replies

Trackbacks

  1. Not All Seeks Are Created Equal #2 – Francisco do Ó
  2. Performance Woes & Wows #5: IN vs UNION – Francisco do Ó

Deixe uma Resposta

Preencha os seus detalhes abaixo ou clique num ícone para iniciar sessão:

Logótipo da WordPress.com

Está a comentar usando a sua conta WordPress.com Terminar Sessão /  Alterar )

Google photo

Está a comentar usando a sua conta Google Terminar Sessão /  Alterar )

Imagem do Twitter

Está a comentar usando a sua conta Twitter Terminar Sessão /  Alterar )

Facebook photo

Está a comentar usando a sua conta Facebook Terminar Sessão /  Alterar )

Connecting to %s

<span>%d</span> bloggers like this: