Même si cela n’est malheureusement pas suffisamment mis en place dans la pratique, il est rappelé qu’il est très important d’avoir des plans de maintenance des bases de données SQL Server, avec notamment une étape liées à la défragmentation des index.

Mais il ne faut pas trop en faire et faire chauffer le serveur pendant toutes les plages “hors production” pour simplement refaire des index qui n’ont presque pas évolué depuis la dernière fois.

Une bonne pratique consiste à s’appuyer sur le taux de fragmentation effectif des index afin de décider quelle type d’opération appliquer : rien du tout s’il n’est pas nécessaire d’intervenir, une petite réorganisation des données au sein des pages ou bien une reconstruction totale de l’index. Voici un script permettant de réaliser cette défragmentation conditionnelle.

Plusieurs solutions sont possibles pour s’occuper de toutes les bases de données ou bien se concentrer sur une seule base. J’ai fait ici le choix d’une procédure stockée qui peut être stockée dans un endroit centralisé (la base master par exemple) et qui est appelée une fois par base concernée, directement depuis la base.

use master
go
if not exists (select * from sys.procedures where object_id=OBJECT_ID('dbo.sp_IndexDefrag'))
	exec sp_executesql N'CREATE PROCEDURE dbo.sp_IndexDefrag AS RETURN;'
GO
ALTER PROCEDURE dbo.sp_IndexDefrag
	@Seuil_Reorganize tinyint = 10,
	@Seuil_Rebuild tinyint = 30
AS
BEGIN
	declare @Schema nvarchar(128),
			@Table nvarchar(128),
			@TableId int,
			@Index nvarchar(128),
			@IndexId int,
			@Partition int,
			@Frag float,
			@Online bit,
			@AvecPartitions bit

	declare @Requete nvarchar(max)

	declare @RebuildOnlineFeature bit,
			@RebuildTableKatmai bit,
			@RebuildOnlineDenali bit

	create table #Liste (ObjectId int,
						ObjectName nvarchar(128),
						SchemaName nvarchar(128),
						IndexId int,
						IndexName nvarchar(128),
						PartitionNumber int,
						Fragmentation float,
						OnlineRebuild bit,
						AvecPartitions bit DEFAULT 0)

	SET NOCOUNT ON

	-- Petite sécurité contre les valeurs d'entrée incorrectes
	if @Seuil_Reorganize>@Seuil_Rebuild
		SET @Seuil_Reorganize=@Seuil_Rebuild

	-- Détermination si le moteur est en édition Enterprise (seule cette édition supporte le rebuild Online)
	if SERVERPROPERTY('EngineEdition')=3 -- Enterprise, Evaluation ou Dev
		set @RebuildOnlineFeature=1
	else
		set @RebuildOnlineFeature=0

	-- Les heaps ne peuvent être reconstruits que depuis SQL Server 2008
	if convert(tinyint,left(convert(varchar(max),SERVERPROPERTY('ProductVersion')),patindex('%.%',convert(varchar(max),SERVERPROPERTY('ProductVersion')))-1))>=10
		set @RebuildTableKatmai=1
	else
		set @RebuildTableKatmai=0

	-- Les types de données BLOB (varchar(max), ...) ne supporte le REBUILD ONLINE qu'à partir de SQL Server 2012
	if convert(tinyint,left(convert(varchar(max),SERVERPROPERTY('ProductVersion')),patindex('%.%',convert(varchar(max),SERVERPROPERTY('ProductVersion')))-1))>=11
		set @RebuildOnlineDenali=1
	else
		set @RebuildOnlineDenali=0

	-- On récupère les taux de fragmentation des objets
	set @Requete = N'insert into #Liste (ObjectId, ObjectName, SchemaName, IndexId, IndexName, PartitionNumber, Fragmentation)
	select o.object_id,o.name,s.name,i.index_id,i.name,ps.partition_number,ps.avg_fragmentation_in_percent
		from sys.dm_db_index_physical_stats(DB_ID(),DEFAULT,DEFAULT,DEFAULT,Null) ps
			join sys.objects o on o.object_id=ps.object_id
								and o.type in (''U'', -- Table
												''V'') -- View
			join sys.schemas s on s.schema_id=o.schema_id
			join sys.indexes i on i.object_id=ps.object_id
								and i.index_id=ps.index_id
								and i.type<=2 -- Uniquement les heaps, les index clustered et non clustered
	WHERE ps.avg_fragmentation_in_percent>=' + convert(nvarchar,@Seuil_Reorganize) + N'
	--AND ps.page_count>8 -- Inutile de chercher à défragmenter si on ne dépasse pas un extend
	'
	exec sp_executesql @Requete

	-- Identification des index qui sont liés à des partitions (notamment parce que le Rebuid Online n'est pas possible)
	set @Requete = N'
	update l
	set AvecPartitions=1
	from #Liste l
	join (select i.object_id,i.index_id from sys.indexes i
		join sys.partition_schemes sch on sch.data_space_id=i.data_space_id
		join sys.partition_range_values rv on rv.function_id=sch.function_id
		group by i.object_id,i.index_id
		having count(*)>1) p on p.object_id=l.ObjectId
							and p.index_id=l.IndexId
	'
	exec sp_executesql @Requete
	-- On pour les reconstructions, on regarde si elles peuvent être faites en ligne
	update l
		set OnlineRebuild=@RebuildOnlineFeature
	FROM #Liste l
	where l.Fragmentation>=@Seuil_Rebuild

	if @RebuildOnlineFeature=1
	BEGIN
		set @Requete = N'
		update l
			set OnlineRebuild=0
		FROM #Liste l
			join sys.index_columns ic on ic.object_id=l.ObjectId
										and ic.index_id=l.IndexId
			join sys.columns c on c.object_id=ic.object_id
								and (c.column_id=ic.column_id or l.IndexId=1) -- L_index Clustered "contient" toutes les colonnes
								and (c.system_type_id in (34,35,99,241) -- Type image, text et ntext non supportés
									or (' + convert(varchar,@RebuildOnlineDenali) + N'=0 and c.max_length=-1) -- varchar(MAX), XML, ... avant SQL Server 2012
									or c.is_filestream=1) -- Filestream'
		exec sp_executesql @Requete
 	END

	-- Traitement des heaps (tables sans index clustered)
	-- On ne traite que les reconstructions
	if @RebuildTableKatmai=1
	BEGIN
		DECLARE Curseur_Tables INSENSITIVE CURSOR FOR
		select l.SchemaName,l.ObjectName,l.PartitionNumber,l.OnlineRebuild,l.AvecPartitions
		from #Liste l
		WHERE l.Fragmentation>=@Seuil_Rebuild
			and l.IndexId=0

		OPEN Curseur_Tables
		FETCH NEXT FROM Curseur_Tables INTO @Schema,@Table,@Partition,@Online,@AvecPartitions

		WHILE @@FETCH_STATUS = 0
		BEGIN
			set @Requete=N'ALTER TABLE ' + QUOTENAME(@Schema) + N'.' + QUOTENAME(@Table) + N' REBUILD '
						+ case when @AvecPartitions=1
								then N'PARTITION = ' + convert(nvarchar(max),@Partition)
								else N''
								END
			exec sp_executesql @Requete

			FETCH NEXT FROM Curseur_Tables INTO @Schema,@Table,@Partition,@Online,@AvecPartitions
		END

		CLOSE Curseur_Tables
		DEALLOCATE Curseur_Tables
	END

	-- Traitement des index : d'abord les clustered puis les non clustered
	DECLARE Curseur_Index INSENSITIVE CURSOR FOR
	select l.SchemaName,l.ObjectName,l.IndexName,l.partitionnumber,l.Fragmentation,l.OnlineRebuild,l.AvecPartitions
	from #Liste l
	where IndexId>=1
	order by l.IndexId -- Pour traiter d'abord les index clustered

 	OPEN Curseur_Index
	FETCH NEXT FROM Curseur_Index INTO @Schema,@Table,@Index,@Partition,@Frag,@Online,@AvecPartitions
	WHILE @@FETCH_STATUS = 0
	BEGIN
		set @Requete=N'ALTER INDEX ' + QUOTENAME(@Index) 
					+ N' ON ' + QUOTENAME(@Schema) + N'.' + QUOTENAME(@Table) 
					+ CASE WHEN @Frag>=@Seuil_Rebuild THEN N' REBUILD ' ELSE N' REORGANIZE ' END
					+ case when @AvecPartitions=1
							then N'PARTITION = ' + convert(nvarchar(max),@Partition)
							else case when @Frag>=@Seuil_Rebuild
									then N' WITH (ONLINE=' + case when @Online=1 then N'ON' else N'OFF' end + N')'
									else N''
									end
							end		exec sp_executesql @Requete

		FETCH NEXT FROM Curseur_Index INTO @Schema,@Table,@Index,@Partition,@Frag,@Online,@AvecPartitions
	END

	CLOSE Curseur_Index
	DEALLOCATE Curseur_Index

END
GO

Après, il n’y a plus qu’à vous positionner sur la base à traiter, et à lancer la procédure stockée, éventuellement avec les paramètres adéquats si vous souhaitez d’autres valeurs de seuils que celles par défaut.