{"id":726,"date":"2013-04-20T23:27:33","date_gmt":"2013-04-20T21:27:33","guid":{"rendered":"http:\/\/www.sqlserver.fr\/blog\/?p=726"},"modified":"2026-05-02T14:30:53","modified_gmt":"2026-05-02T12:30:53","slug":"index-couvrants","status":"publish","type":"post","link":"https:\/\/www.sqlserver.fr\/blog\/index-couvrants\/","title":{"rendered":"Index couvrants"},"content":{"rendered":"

Tr\u00e8s souvent, lorsqu’une requ\u00eate donn\u00e9e tra\u00eene un peu, une solution pour l’optimiser consiste \u00e0 s’attacher \u00e0 positionner des index ad\u00e9quats. Un \u00e9l\u00e9ment important dans le design des index r\u00e9side dans la notion d’index couvrants.<\/p>\n

Dans cet article, nous nous appuierons sur la base d’exemple de SQL Server 2012, AdventureWorks2012, disponible ici<\/a>.<\/p>\n

Dans un premier temps, \u00a0les colonnes candidates pour un index sont les colonnes utilis\u00e9es pour filtrer les r\u00e9sultats de la requ\u00eates. Ainsi, prenons la requ\u00eate ci-dessous :<\/p>\n

select PersonType,LastName,FirstName\r\nfrom Person.Person\r\nwhere PersonType='SP'<\/pre>\n
Lorsqu’on l’ex\u00e9cute, on obtient le plan d’ex\u00e9cution suivant :<\/p>\n
\"FullScan\"<\/a><\/p>\n
On remarque un parcours complet de la table (Index Scan), ce qui au final donne un nombre de lectures assez \u00e9lev\u00e9.<\/p>\n
Table ‘Person’. Scan count 1, logical reads 3820, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.<\/p>\n
Si l’on positionne d\u00e9sormais un index sur la colonne PersonType, on s’aper\u00e7oit qu’il est utilis\u00e9 et que le nombre de pages lues chute de mani\u00e8re drastique. Jusqu’ici, tout va bien.<\/p>\n
create index IX_Person_PersonType ON Person.Person(PersonType)<\/pre>\n
\"IndexSeek\"<\/a><\/p>\n
Table ‘Person’. Scan count 1, logical reads 53, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.<\/p>\n
Mais lorsque le filtre ne restreint plus uniquement sur quelques enregistrements, le surco\u00fbt des op\u00e9rations Key Lookup devient trop important.<\/p>\n
Pour rappel, une recherche de cl\u00e9 (Key Lookup) correspond au fait d’aller chercher dans un autre index les colonnes manquantes (dans notre cas les colonnes LastName et FirstName) alors que l’on n’a \u00e0 notre disposition que les colonnes pr\u00e9sentes dans les feuilles de notre index non clustered (dans notre cas PersonType et\u00a0BusinessEntityID).<\/p>\n
Lorsque le nombre de recherches de cl\u00e9s (Key Lookup) devient trop important, le moteur SQL Server peut m\u00eame d\u00e9cider d’aller chercher les donn\u00e9es manquantes autrement que via un Key Lookup.<\/p>\n
select PersonType,LastName,FirstName\r\nfrom Person.Person\r\nwhere PersonType='VC'<\/pre>\n
\"Intermediaire\"<\/a><\/p>\n
Le syst\u00e8me pr\u00e9f\u00e8re ici effectuer un scan complet d’un autre index pour obtenir les colonnes demand\u00e9es. Le moteur s’appuie ici sur l’index non clustered dont le parcours est le moins co\u00fbteux, c’est-\u00e0-dire l’index d\u00e9di\u00e9 aux colonnes FirstName et LastName.<\/p>\n
Si notre requ\u00eate concerne d’autres colonnes, par exemple la colonne Demographics, le fonctionnement est diff\u00e9rent. En effet, il n’y a pas d’index s’appuyant sur cette colonne. Au final, si la solution la moins co\u00fbteuse est celle passant par un Key Lookup (en comparaison avec un scan complet de l’index Clustered), m\u00eame si cela nous oblige tout de m\u00eame \u00e0 avoir un nombre de lectures qui croit assez rapidement.<\/p>\n
select PersonType,Demographics\r\nfrom Person.Person\r\nwhere PersonType='SC'<\/pre>\n
\"Intermediaire2\"<\/a><\/p>\n
Table ‘Person’. Scan count 1, logical reads 2320, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.<\/p>\n
Afin de r\u00e9duire le co\u00fbt d’une telle requ\u00eate, une technique habituelle consiste \u00e0 ajouter des colonnes aux index utilis\u00e9s. Ainsi, dans le cas des colonnes LastName et FirstName, on pourrait ajouter les colonnes \u00e0 l’index d\u00e9fini sur PersonType.<\/p>\n
Mais lorsque la colonne requise est la colonne Demographics, il n’est pas possible d’ajouter cette colonne directement \u00e0 l’index car son type de donn\u00e9es, XML, n’est pas triable.<\/p>\n
N\u00e9anmoins, SQL Server, depuis sa version 2005, nous permet d’ajouter des colonnes, m\u00eame d’un type de donn\u00e9es non triable, au niveau terminal des index (niveau auquel les pages sont appel\u00e9es feuilles).<\/p>\n
create index IX_Person_PersonType_Demographics ON Person.Person(PersonType) INCLUDE (Demographics)<\/pre>\n
\"SeekFinal\"<\/a><\/p>\n
Table ‘Person’. Scan count 1, logical reads 51, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.<\/p>\n
On constate effectivement qui le nombre de lectures est redevenue tr\u00e8s faible, ce qui correspond donc \u00e0 de bonnes performances pour la requ\u00eate.<\/p>\n
Ce type d’index, d\u00e9di\u00e9 \u00e0 une ou plusieurs requ\u00eates type et apportant toutes les colonnes requises par ces requ\u00eates, est appel\u00e9 index couvrant. Comme toujours avec les index, on a une am\u00e9lioration des performances de lecture mais au d\u00e9triment des performances lors des \u00e9critures.<\/p>\n
Le mot cl\u00e9 INCLUDE permet de lister des colonnes qui ne seront positionn\u00e9es que dans les niveaux terminaux de l’index. M\u00eame si ces colonnes incluses sont souvent indiqu\u00e9es au niveau des conseils li\u00e9s aux index manquants, il faut toutefois faire attention \u00e0 ne pas trop en abuser, car l’espace consomm\u00e9 peut rapidement augmenter (en dupliquant des donn\u00e9es \u00e0 outrance) et cela peut d\u00e9grader les performances li\u00e9es aux modifications.<\/p>\n","protected":false},"excerpt":{"rendered":"
Tr\u00e8s souvent, lorsqu’une requ\u00eate donn\u00e9e tra\u00eene un peu, une solution pour l’optimiser consiste \u00e0 s’attacher \u00e0 positionner des index ad\u00e9quats. Un \u00e9l\u00e9ment important dans le design des index r\u00e9side dans la notion d’index couvrants.<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-726","post","type-post","status-publish","format-standard","hentry","category-article_sql"],"_links":{"self":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/726","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/comments?post=726"}],"version-history":[{"count":22,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/726\/revisions"}],"predecessor-version":[{"id":1933,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/726\/revisions\/1933"}],"wp:attachment":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/media?parent=726"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/categories?post=726"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/tags?post=726"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}