Plan de maintenance et tables sans index clustered

Même si certains oublient de les mettre en place (et s’étonnent ensuite de voir les performances de leur application se dégrader au fil du temps …), des plans de maintenance sont généralement implémentés sur les serveurs SQL Server, et intègrent notamment une défragmentation des index. Mais dans certains cas, ce type de tâches n’est pas suffisant …

Dans les exemples de tables que nous prendrons ici, nous considérerons que l’on a un certain nombre (10 millions par exemple) d’enregistrements dans nos tables, et que nous en supprimons les trois quarts. Ensuite, nous progresserons dans notre plan de maintenance pour en observer l’efficacité. L’étude sera faite sur 2 tables : une avec un index clustered, et l’autre sans.

Voici le script de création des tables :

CREATE TABLE AvecClustered (Id int IDENTITY PRIMARY KEY, Valeur varchar(100))
CREATE TABLE SansClustered (Id int IDENTITY PRIMARY KEY NONCLUSTERED, Valeur varchar(100))

Et voici le script de remplissage :

SET NOCOUNT ON
GO
INSERT INTO AvecClustered (Valeur) SELECT NEWID()
INSERT INTO SansClustered (Valeur) SELECT NEWID()
GO 10000000

On peut d’ores et déjà observer la taille occupée sur disque par ces deux tables :

TablesPleines

Ensuite, on efface 3 enregistrements sur 4.

DELETE FROM AvecClustered where Id%4<>0
DELETE FROM SansClustered where Id%4<>0

En regardant l’espace occupé, on s’aperçoit qu’il n’a pas changé alors que le nombre d’enregistrements a été divisé par 4 !

Volume_Gruyere

Utilisons ensuite l’assistant de plan de maintenance pour générer une tâche basique de reconstruction d’index.

PlanMaintenance

Et après exécution de ce plan, voici les volumes occupés par les deux tables :

Moitie

On voit donc que le plan de maintenance, destiné à reconstruire tous les index, a bien considérablement réduit le volume occupé par la table AvecClustered. Mais il n’a pas touché à la table SansClustered, qui est un segment mémoire sans aucun index clustered.

Plusieurs solutions se présentent pour pouvoir réduire tout de même l’espace disque occupé :

  • respecter la bonne pratique de modélisation consistant à définir un index clustered pour l’ensemble des tables. Remarque : ce n’est pas parce qu’on défini un index clustered que l’on doit définir une clé primaire; l’index clustered n’est pas nécessairement porteur d’une contrainte d’unicité
  • l’autre solution consiste a compléter le plan de maintenance avec un petit script permettant de forcer la reconstruction des segments mémoire.

La syntaxe pour reconstruire un segment mémoire ne s’appuie pas sur la modification d’un index (ALTER INDEX) mais se traduit directement par une opération sur la table (ALTER TABLE xxx REBUILD), comme détaillé ici.

Voici une proposition d’opération complémentaire ajoutée au plan de maintenance, pour reconstruire tous les segments mémoire de toutes les bases de données actives.

 

sp_MSforeachdb '
if exists (select * from sys.databases where name=''?'' and state=0 and database_id>4)
BEGIN
USE [?];
declare @requete nvarchar(max)=N'''';
select @requete+=''ALTER TABLE '' + QUOTENAME(s.name) + ''.'' + QUOTENAME(t.name) + '' REBUILD;''
	FROM sys.indexes i
		join sys.tables t on t.object_id=i.object_id
		join sys.schemas s on s.schema_id=t.schema_id
	where i.index_id=0;
exec (@requete)
END
'

Il suffit donc de positionner ce script en complément de la tâche standard de reconstruction des index, pour obtenir un résultat complètement satisfaisant.

Propre

Voici donc, sous forme d’une petite progression simple, la démonstration que la tâche par défaut de reconstruction des index dans les plans de maintenance ne couvre pas nécessairement l’ensemble des cas possibles …

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Contrôle de sécurité * Time limit is exhausted. Please reload CAPTCHA.