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 :

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.