Lorsque l’on consulte les différentes vues système d’une instance SQL, et plus particulièrement la vue sys.dm_db_missing_index_details, il peut arriver de trouver des index qui sont très utiles, mais seulement pour des actions « rares ».
Dans ce cas, le fait de créer un index permanent n’est peut-être pas la meilleure méthode, et il est à envisager de créer un index juste pour l’occasion.
Prenons un exemple fictif. Tout d’abord, créons une table.
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Test]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[Test]( [Id] [int] IDENTITY(1,1) NOT NULL, [NewId] [uniqueidentifier] NOT NULL, [Aleatoire] [float] NOT NULL, [Chaine] [char](10) NOT NULL, CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DF_Test_NewId]') AND type = 'D') BEGIN ALTER TABLE [dbo].[Test] ADD CONSTRAINT [DF_Test_NewId] DEFAULT (newid()) FOR [NewId] END IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DF_Test_Aleatoire]') AND type = 'D') BEGIN ALTER TABLE [dbo].[Test] ADD CONSTRAINT [DF_Test_Aleatoire] DEFAULT (rand()) FOR [Aleatoire] END
Ensuite, remplissons-la avec quelques enregistrements. Pour l’exemple, ce remplissage correspondra à une charge mensuelle de travail.
truncate table Test GO SET NOCOUNT ON GO CREATE TABLE #Lot(Id int,Test bit) INSERT INTO #Lot (Test) values (0),(1) --2 INSERT INTO #Lot (Test) SELECT Test FROM #Lot --4 INSERT INTO #Lot (Test) SELECT Test FROM #Lot --8 INSERT INTO #Lot (Test) SELECT Test FROM #Lot --16 INSERT INTO #Lot (Test) SELECT Test FROM #Lot --32 INSERT INTO #Lot (Test) SELECT Test FROM #Lot --64 INSERT INTO #Lot (Test) SELECT Test FROM #Lot --128 INSERT INTO #Lot (Test) SELECT Test FROM #Lot --256 INSERT INTO #Lot (Test) SELECT Test FROM #Lot --512 INSERT INTO #Lot (Test) SELECT Test FROM #Lot --1024 GO insert into Test (Chaine) SELECT 'Test' FROM #Lot GO 10000 DROP TABLE #Lot GO
Dans l’état, sur une machine de test, ce script s’exécute en 4 minutes 45. Ce sera notre référence pour la charge mensuelle liée à l’alimentation de cette table.
Maintenant, supposons un certain script qui tournerait une fois par mois.
declare @Central float declare @Liste table (Central float, NbVoisins int) DECLARE curseur CURSOR FOR SELECT TOP 10000 Aleatoire FROM Test Order by [NewId] OPEN curseur FETCH NEXT FROM curseur INTO @Central WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO @Liste(Central,NbVoisins) SELECT @Central,COUNT(*) FROM Test WHERE Aleatoire BETWEEN @Central-0.00001 AND @Central+0.00001 FETCH NEXT FROM curseur INTO @Central END CLOSE curseur DEALLOCATE curseur select * from @Liste
En l’état actuel des choses, ce script s’exécute en 2 heures 30. C’est un peu long, et même si ce script est mensuel, il peut être intéressant de cherchre à réduire son temps d’exécution.
En cherchant à l’optimiser, on peut observer la vue système des index manquants, et constater que le système recommande un index sur la colonne [Aleatoire] de notre table.
Créons donc cet index, pour voir s’il est efficace.
create index IX_Aleatoire ON Test(Aleatoire)
La création de l’index prend environ 40 secondes.
Maintenant, en relançant le script mensuel, le temps d’exécution tombe à 1 minute 30 secondes.
L’index s’avère donc (très) efficace, et on serait tenté de le conserver et d’en rester là.
Mais il s’agit là en fait d’une grave erreur. En effet, le système devra maintenir cet index en permanence, tout au long du processus mensuel de remplissage de la table de données. Et cela a un coût non négligeable. En effet, en relançant le script simulant l’alimentation mensuelle, on obtient désormais une durée d’exécution de 7 minutes 15 là où précédemment nous n’avions besoin que de moins de 5 minutes en temps cumulé.
Cette augmentation est due au surcoût du maintien de l’index, rempli avec des valeurs non séquentielles.
Au final, nous avons le décompte de temps suivant, pour la charge de remplissage plus le script mensuel :
Cas | Durée charge mensuelle | Création index | Traitement ponctuel | Total |
Sans index | 4 min 45 | 150 min | 155 min | |
Index permanent | 7 min 15 | 1 min 30 | 8 min 45 | |
Index pour l’occasion | 4 min 45 | 40 s | 1 min 30 | 6 min 35 |
Cet exemple montre donc que des index, aussi efficaces soient-ils, on un surcoût pour être maintenus, et qu’il peut parfois être préférable de les créer juste le temps de les utiliser s’ils ne sont bénéfiques que lors de certains traitements peu fréquents.