SQL Server 2008 – Index Filtrés

Il y a quelques mois de cela, j’avais publié un post concernant les index Include, présents dans SQL Server depuis la version 2005. Depuis bien des années, j’ai fait des IO disques mon cheval de bataille. L’index Include, les index couvrants, l’optimisation des types de données dans les champs d’une table. Ce n’est pas tant le coût du stockage qui me préoccupe, mais bien la performance. Tous les moyens sont bons pour économiser des IO disque.

Dans sa livrée 2008, SQL Server nous propose de filtrer les index. Tout cela dans le but d’optimiser les performances. Regardons de plus près.

Pour tester cette fonctionnalité, je me suis basé sur la table [Sales].[SalesOrderDetail] dont le nombre de ligne peu important (121.317) devrait somme toute donner des résultats probants.
image

J’ai créé une nouvelle base de données, sans attention particulière : Test_IndexFiltre.
J’ai recopié les données de la table SalesOrderDetail de la base de test AdventureWorks2008 en deux exemplaires afin de mesurer l’impact de l’index filtré.

SELECT *
INTO Test_IndexFiltre.dbo.[SalesOrderDetail]
FROM [AdventureWorks2008].[Sales].[SalesOrderDetail]
GO
SELECT *
INTO Test_IndexFiltre.dbo.[SalesOrderDetail_IDXFiltre]
FROM [AdventureWorks2008].[Sales].[SalesOrderDetail]
GO

ALTER TABLE [SalesOrderDetail] ADD  CONSTRAINT [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]
PRIMARY KEY CLUSTERED (
    [SalesOrderID] ASC,
    [SalesOrderDetailID] ASC
)
ON [PRIMARY]
GO
ALTER TABLE [SalesOrderDetail_IDXFiltre] ADD  CONSTRAINT [PK_SalesOrderDetailIDXFiltre_SalesOrderID_SalesOrderDetailID]
PRIMARY KEY CLUSTERED (
    [SalesOrderID] ASC,
    [SalesOrderDetailID] ASC
)
ON [PRIMARY]
GO

La colonne CarrierTrackingNumber autorise les valeurs NULL (60.398 enregistrements ont une valeur NULL). C’est un bon candidat pour notre test.

Si nous voulons rechercher toutes les lignes de cette table dont le tracking number est ‘E075-4B48-BE’ (arbitrairement pris au hasard …) nous exécutons la requête :

SELECT [SalesOrderID]
      ,[SalesOrderDetailID]
      ,[CarrierTrackingNumber]
      ,[OrderQty]
      ,[ProductID]
      ,[SpecialOfferID]
      ,[UnitPrice]
      ,[UnitPriceDiscount]
      ,[LineTotal]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [SalesOrderDetail]
    WHERE [CarrierTrackingNumber] = ‘E075-4B48-BE’

N’ayant pas d’index sur cette colonne les résultats du plan d’exécution sont tout a fait attendus : un scan sur l’index cluster, avec un nombre relativement élevé de lectures disque :
image 
(17 row(s) affected)
Table ‘SalesOrderDetail’. Scan count 1, logical reads 1502, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Il est évident que l’utilisation d’une table non indexée n’est pas vraiment  la solution optimale …

Créons maintenant un index filtré sur la colonne CarrierTrackingNumber :

CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_IDXFiltre]
       ON [SalesOrderDetail_IDXFiltre] (CarrierTrackingNumber)
WHERE CarrierTrackingNumber IS NOT NULL
ON [PRIMARY]

 

et exécutons la même requête sur la table  SalesOrderDetail_IDXFiltre :

image

(17 row(s) affected)
Table ‘SalesOrderDetail_IDXFiltre’. Scan count 1, logical reads 54, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Le nombre de lectures disque a drastiquement chuté de 1502 à 54. L’index filtré apparait comme une solution très performante pour réduire les IO disque sur des requêtes faisant appel à des colonnes présentant de nombreuses valeurs NULL (près de 50% dans notre cas). 

Que se passe t’il si on crée un index ‘classique’ sur la colonne ?

CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail]
       ON [SalesOrderDetail] (CarrierTrackingNumber)
ON [PRIMARY]

image 
(17 row(s) affected)
Table ‘SalesOrderDetail’. Scan count 1, logical reads 54, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Et là les sceptiques me diront : bah, c’est le même nombre d’IO que l’index filtré, alors à quoi bon ???

Ce résultat “identique” est du à la faible volumétrie de la table sur laquelle est posé l’index. Pour preuve, lorsque l’on regarde les informations de l’index filtré :

image

et les informations de l’index “normal”

 image

Le nombre de lignes au niveau feuille est quasiment moitié moindre sur l’index filtré. De même il y a 318 pages dans l’index filtré contre 424 dans l’index “normal”. On peut naturellement en conclure que l’index filtré sera d’autant plus performant que le volume de données dans la table sera élevé.

 

128 pages. 128 pages, c’est le nombre de pages de données (ou d’index) contenues dans 1Mo.
1502 lectures disque dans la requête n’utilisant pas d’index représentent plus de 11 Mo lus sur disque (le poids de la table … normal on a fait un index scan sur la clé cluster).
54 lectures disque dans la requête utilisant un index filtré, soit 0.421 Mo …

Les chiffres parlent d’eux mêmes. L’indexation est un des piliers de la performance d’une base de données.

Et les index filtrés sont un moyen supplémentaire d’optimiser les requêtes en réduisant le poids de l’index. Les IO, les IO, c’est le nerf de la guerre …

About these ads

À propos de Christophe

Consultant SQL Server Formateur certifié Microsoft MVP SQL Server MCM SQL Server 2008
Ce contenu a été publié dans SQL Server, avec comme mot(s)-clef(s) , . Vous pouvez le mettre en favoris avec ce permalien.

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s