Bienvenue à Blogs CodeS-SourceS Identification | Inscription | Aide

SQL Server: Scans and seeks

The most primitive operation in SQL Server is retrieving from a table a set of rows that satisfies a given search predicate. This can be achieved using two basic strategies: scans and seeks.

Scan

Scans can be performed on any structure (index or heap). Scanning a table means that SQL Server reads all the rows in the table. Only the rows satisfying the search predicate are returned. Scans imply sequential reads that are usually faster than reading each page individually, but the cost of this operation is proportional to the size of the table.

In indexes, SQL Server uses the pointers to the next page (or previous) for scanning. In a heap, it uses the IAM pages.
The number of logical reads during this operation is the number of pages read during the scan, plus one logical read at each non-leaf level, in case of an index, in order to locate the first row of the table. This operation counts as a scan in the IO statistics.

Seek

Seeks can only be performed on indexes (clustered or nonclustered). A seek is performed when SQL Server can leverage the ordering of the rows in the index to identify a range of rows in the index that contains all the rows satisfying the search predicate.

The search predicate is divided in two parts:

  • The seek predicate, such as all the rows within this range satisfy the seek predicate, and all the rows outside of that range don't. Seek predicates are usually predicates on the leftmost columns of the key of an index.
  • The residual predicate, such as the search predicate is the conjunction of the seek predicate and the residual predicate. The residual predicate is optional.

SQL Server will scan the range containing the rows satisfying the seek predicate, evaluate the residual predicate against each of them, and return only the rows that satisfy both.

In order to locate the first leaf-level row qualifying for the seek predicate, SQL Server reads one page at each non-leaf level of the index, from the root page, to the last non-leaf level.

There will be exactly one logical read at each non-leaf level of the index.

Then, at the leaf level of the index, all the rows satisfying the seek predicate have to be read:

  • If the index definition cannot guarantee that only a single row can satisfy the seek predicate, SQL Server scans all the pages from the page containing the first row qualifying, to the page containing the last row qualifying.
    The IO statistics will count this operation as a scan, and there will be as many logical reads at the leaf level as the number of pages scanned.
  • If the index definition guarantees that only a single row can satisfy the seek predicate, SQL Server just reads the page containing the qualifying row.
    The operation won't be counted as a scan, and there will be just a single logical read at the leaf level.

The logical reads performed at the leaf level are usually slower that logical reads at non-leaf levels because the non-leaf levels of indexes are often cached in memory.

Publié jeudi 18 décembre 2008 17:16 par RaptorXP
Classé sous : ,
Ce post vous a plu ? Ajoutez le dans vos favoris pour ne pas perdre de temps à le retrouver le jour où vous en aurez besoin :

Commentaires

Pas de commentaires
Les commentaires anonymes sont désactivés

Les 10 derniers blogs postés

- [TFS] Supprimer un projet de Visual Studio Online par Blog de Jérémy Jeanson le il y a 21 heures et 28 minutes

- Nouveau blog en anglais / New blog in english ! par Le blog de Patrick [MVP SharePoint] le 09-18-2014, 18:42

- [ #Yammer ] From Mailbox to Yammer and back / De votre messagerie vers Yammer et retour ! par Le blog de Patrick [MVP SharePoint] le 09-15-2014, 11:31

- [ #Office 365 ] New service settings panel / Nouveau panneau de paramétrage des services par Le blog de Patrick [MVP SharePoint] le 09-11-2014, 08:50

- Problème de déploiement pour une démo SharePoint/TFS? par Blog de Jérémy Jeanson le 09-10-2014, 21:52

- [ #Office365 ] Delve first impressions / Premières impressions sur Delve par Le blog de Patrick [MVP SharePoint] le 09-09-2014, 16:57

- [ #Office365 ] How to change Administration console language ? / Comment changer la langue de la console d’administration ? par Le blog de Patrick [MVP SharePoint] le 09-09-2014, 08:25

- [ #SharePoint 2013 ] Suppression de bases de données en état “Pas de Réponse” par Le blog de Patrick [MVP SharePoint] le 09-04-2014, 14:10

- Changer l’adresse d’une ferme Office Web Apps associée à SharePoint par Blog de Jérémy Jeanson le 09-01-2014, 22:21

- Une ferme #SharePoint 2013 dans @Azure en quelques clics (1ère partie) ! par Le blog de Patrick [MVP SharePoint] le 08-28-2014, 18:52