Not All Seeks Are Created Equal #2

As we saw in the previous post, Index Seek operations can look for single rows (Singleton Seek) or specific subsets of rows (Range Seek).

Singleton Seek

A Singleton Seek occurs when just one single row, at most, will satisfy the search requirements that are related to the Seek Key.

The IO statistics for the same execution presented above, will show a scan count of zero, also confirming the Singleton Seek occurrence.

The following image depicts a similar seek operation, as exemplified by the red arrows:

In this case, the B-tree is traversed from the root to the leaf node and the specific data page is fetched.

Range Seek

Conversely, a Range Seek will occur when SQL Server assumes that more than one row can be retrieved by the Seek Key.

The following image depicts an equivalent seek operation, as exemplified by the red arrows:

Similarly to a Singleton Seek, the B-tree is also traversed to a specific leaf node, but in this case, this leaf node will be either the first or the last key within range (depending on the scan direction) and then the remaining leaf nodes within range limit will be scanned.

And that’s all he wrote.



Categorias:executionplans

Etiquetas:

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: