M\u00eame si cela n’est malheureusement pas suffisamment mis en place dans la pratique, il est rappel\u00e9 qu’il est tr\u00e8s important d’avoir des plans de maintenance des bases de donn\u00e9es SQL Server, avec notamment une \u00e9tape li\u00e9es \u00e0 la d\u00e9fragmentation des index.<\/p>\n
Mais il ne faut pas trop en faire et faire chauffer le serveur pendant toutes les plages \u00ab\u00a0hors production\u00a0\u00bb pour simplement refaire des index qui n’ont presque pas \u00e9volu\u00e9 depuis la derni\u00e8re fois.<\/p>\n
Une bonne pratique consiste \u00e0 s’appuyer sur le taux de fragmentation effectif des index afin de d\u00e9cider quelle type d’op\u00e9ration appliquer : rien du tout s’il n’est pas n\u00e9cessaire d’intervenir, une petite r\u00e9organisation des donn\u00e9es au sein des pages ou bien une reconstruction totale de l’index. Voici un script permettant de r\u00e9aliser cette d\u00e9fragmentation conditionnelle.<\/p>\n
Plusieurs solutions sont possibles pour s’occuper de toutes les bases de donn\u00e9es ou bien se concentrer sur une seule base. J’ai fait ici le choix d’une proc\u00e9dure stock\u00e9e qui peut \u00eatre stock\u00e9e dans un endroit centralis\u00e9 (la base master par exemple) et qui est appel\u00e9e une fois par base concern\u00e9e, directement depuis la base.<\/p>\n
use master\r\ngo\r\nif not exists (select * from sys.procedures where object_id=OBJECT_ID('dbo.sp_IndexDefrag'))\r\n\texec sp_executesql N'CREATE PROCEDURE dbo.sp_IndexDefrag AS RETURN;'\r\nGO\r\nALTER PROCEDURE dbo.sp_IndexDefrag\r\n\t@Seuil_Reorganize tinyint = 10,\r\n\t@Seuil_Rebuild tinyint = 30\r\nAS\r\nBEGIN\r\n\tdeclare @Schema nvarchar(128),\r\n\t\t\t@Table nvarchar(128),\r\n\t\t\t@TableId int,\r\n\t\t\t@Index nvarchar(128),\r\n\t\t\t@IndexId int,\r\n\t\t\t@Partition int,\r\n\t\t\t@Frag float,\r\n\t\t\t@Online bit,\r\n\t\t\t@AvecPartitions bit\r\n\r\n\tdeclare @Requete nvarchar(max)\r\n\r\n\tdeclare @RebuildOnlineFeature bit,\r\n\t\t\t@RebuildTableKatmai bit,\r\n\t\t\t@RebuildOnlineDenali bit\r\n\r\n\tcreate table #Liste (ObjectId int,\r\n\t\t\t\t\t\tObjectName nvarchar(128),\r\n\t\t\t\t\t\tSchemaName nvarchar(128),\r\n\t\t\t\t\t\tIndexId int,\r\n\t\t\t\t\t\tIndexName nvarchar(128),\r\n\t\t\t\t\t\tPartitionNumber int,\r\n\t\t\t\t\t\tFragmentation float,\r\n\t\t\t\t\t\tOnlineRebuild bit,\r\n\t\t\t\t\t\tAvecPartitions bit DEFAULT 0)\r\n\r\n\tSET NOCOUNT ON\r\n\r\n\t-- Petite s\u00e9curit\u00e9 contre les valeurs d'entr\u00e9e incorrectes\r\n\tif @Seuil_Reorganize>@Seuil_Rebuild\r\n\t\tSET @Seuil_Reorganize=@Seuil_Rebuild\r\n\r\n\t-- D\u00e9termination si le moteur est en \u00e9dition Enterprise (seule cette \u00e9dition supporte le rebuild Online)\r\n\tif SERVERPROPERTY('EngineEdition')=3 -- Enterprise, Evaluation ou Dev\r\n\t\tset @RebuildOnlineFeature=1\r\n\telse\r\n\t\tset @RebuildOnlineFeature=0\r\n\r\n\t-- Les heaps ne peuvent \u00eatre reconstruits que depuis SQL Server 2008\r\n\tif convert(tinyint,left(convert(varchar(max),SERVERPROPERTY('ProductVersion')),patindex('%.%',convert(varchar(max),SERVERPROPERTY('ProductVersion')))-1))>=10\r\n\t\tset @RebuildTableKatmai=1\r\n\telse\r\n\t\tset @RebuildTableKatmai=0\r\n\r\n\t-- Les types de donn\u00e9es BLOB (varchar(max), ...) ne supporte le REBUILD ONLINE qu'\u00e0 partir de SQL Server 2012\r\n\tif convert(tinyint,left(convert(varchar(max),SERVERPROPERTY('ProductVersion')),patindex('%.%',convert(varchar(max),SERVERPROPERTY('ProductVersion')))-1))>=11\r\n\t\tset @RebuildOnlineDenali=1\r\n\telse\r\n\t\tset @RebuildOnlineDenali=0\r\n\r\n\t-- On r\u00e9cup\u00e8re les taux de fragmentation des objets\r\n\tset @Requete = N'insert into #Liste (ObjectId, ObjectName, SchemaName, IndexId, IndexName, PartitionNumber, Fragmentation)\r\n\tselect o.object_id,o.name,s.name,i.index_id,i.name,ps.partition_number,ps.avg_fragmentation_in_percent\r\n\t\tfrom sys.dm_db_index_physical_stats(DB_ID(),DEFAULT,DEFAULT,DEFAULT,Null) ps\r\n\t\t\tjoin sys.objects o on o.object_id=ps.object_id\r\n\t\t\t\t\t\t\t\tand o.type in (''U'', -- Table\r\n\t\t\t\t\t\t\t\t\t\t\t\t''V'') -- View\r\n\t\t\tjoin sys.schemas s on s.schema_id=o.schema_id\r\n\t\t\tjoin sys.indexes i on i.object_id=ps.object_id\r\n\t\t\t\t\t\t\t\tand i.index_id=ps.index_id\r\n\t\t\t\t\t\t\t\tand i.type<=2 -- Uniquement les heaps, les index clustered et non clustered\r\n\tWHERE ps.avg_fragmentation_in_percent>=' + convert(nvarchar,@Seuil_Reorganize) + N'\r\n\t--AND ps.page_count>8 -- Inutile de chercher \u00e0 d\u00e9fragmenter si on ne d\u00e9passe pas un extend\r\n\t'\r\n\texec sp_executesql @Requete\r\n\r\n\t-- Identification des index qui sont li\u00e9s \u00e0 des partitions (notamment parce que le Rebuid Online n'est pas possible)\r\n\tset @Requete = N'\r\n\tupdate l\r\n\tset AvecPartitions=1\r\n\tfrom #Liste l\r\n\tjoin (select i.object_id,i.index_id from sys.indexes i\r\n\t\tjoin sys.partition_schemes sch on sch.data_space_id=i.data_space_id\r\n\t\tjoin sys.partition_range_values rv on rv.function_id=sch.function_id\r\n\t\tgroup by i.object_id,i.index_id\r\n\t\thaving count(*)>1) p on p.object_id=l.ObjectId\r\n\t\t\t\t\t\t\tand p.index_id=l.IndexId\r\n\t'\r\n\texec sp_executesql @Requete\r\n\t-- On pour les reconstructions, on regarde si elles peuvent \u00eatre faites en ligne\r\n\tupdate l\r\n\t\tset OnlineRebuild=@RebuildOnlineFeature\r\n\tFROM #Liste l\r\n\twhere l.Fragmentation>=@Seuil_Rebuild\r\n\r\n\tif @RebuildOnlineFeature=1\r\n\tBEGIN\r\n\t\tset @Requete = N'\r\n\t\tupdate l\r\n\t\t\tset OnlineRebuild=0\r\n\t\tFROM #Liste l\r\n\t\t\tjoin sys.index_columns ic on ic.object_id=l.ObjectId\r\n\t\t\t\t\t\t\t\t\t\tand ic.index_id=l.IndexId\r\n\t\t\tjoin sys.columns c on c.object_id=ic.object_id\r\n\t\t\t\t\t\t\t\tand (c.column_id=ic.column_id or l.IndexId=1) -- L_index Clustered \"contient\" toutes les colonnes\r\n\t\t\t\t\t\t\t\tand (c.system_type_id in (34,35,99,241) -- Type image, text et ntext non support\u00e9s\r\n\t\t\t\t\t\t\t\t\tor (' + convert(varchar,@RebuildOnlineDenali) + N'=0 and c.max_length=-1) -- varchar(MAX), XML, ... avant SQL Server 2012\r\n\t\t\t\t\t\t\t\t\tor c.is_filestream=1) -- Filestream'\r\n\t\texec sp_executesql @Requete\r\n \tEND\r\n\r\n\t-- Traitement des heaps (tables sans index clustered)\r\n\t-- On ne traite que les reconstructions\r\n\tif @RebuildTableKatmai=1\r\n\tBEGIN\r\n\t\tDECLARE Curseur_Tables INSENSITIVE CURSOR FOR\r\n\t\tselect l.SchemaName,l.ObjectName,l.PartitionNumber,l.OnlineRebuild,l.AvecPartitions\r\n\t\tfrom #Liste l\r\n\t\tWHERE l.Fragmentation>=@Seuil_Rebuild\r\n\t\t\tand l.IndexId=0\r\n\r\n\t\tOPEN Curseur_Tables\r\n\t\tFETCH NEXT FROM Curseur_Tables INTO @Schema,@Table,@Partition,@Online,@AvecPartitions\r\n\r\n\t\tWHILE @@FETCH_STATUS = 0\r\n\t\tBEGIN\r\n\t\t\tset @Requete=N'ALTER TABLE ' + QUOTENAME(@Schema) + N'.' + QUOTENAME(@Table) + N' REBUILD '\r\n\t\t\t\t\t\t+ case when @AvecPartitions=1\r\n\t\t\t\t\t\t\t\tthen N'PARTITION = ' + convert(nvarchar(max),@Partition)\r\n\t\t\t\t\t\t\t\telse N''\r\n\t\t\t\t\t\t\t\tEND\r\n\t\t\texec sp_executesql @Requete\r\n\r\n\t\t\tFETCH NEXT FROM Curseur_Tables INTO @Schema,@Table,@Partition,@Online,@AvecPartitions\r\n\t\tEND\r\n\r\n\t\tCLOSE Curseur_Tables\r\n\t\tDEALLOCATE Curseur_Tables\r\n\tEND\r\n\r\n\t-- Traitement des index : d'abord les clustered puis les non clustered\r\n\tDECLARE Curseur_Index INSENSITIVE CURSOR FOR\r\n\tselect l.SchemaName,l.ObjectName,l.IndexName,l.partitionnumber,l.Fragmentation,l.OnlineRebuild,l.AvecPartitions\r\n\tfrom #Liste l\r\n\twhere IndexId>=1\r\n\torder by l.IndexId -- Pour traiter d'abord les index clustered\r\n\r\n \tOPEN Curseur_Index\r\n\tFETCH NEXT FROM Curseur_Index INTO @Schema,@Table,@Index,@Partition,@Frag,@Online,@AvecPartitions\r\n\tWHILE @@FETCH_STATUS = 0\r\n\tBEGIN\r\n\t\tset @Requete=N'ALTER INDEX ' + QUOTENAME(@Index) \r\n\t\t\t\t\t+ N' ON ' + QUOTENAME(@Schema) + N'.' + QUOTENAME(@Table) \r\n\t\t\t\t\t+ CASE WHEN @Frag>=@Seuil_Rebuild THEN N' REBUILD ' ELSE N' REORGANIZE ' END\r\n\t\t\t\t\t+ case when @AvecPartitions=1\r\n\t\t\t\t\t\t\tthen N'PARTITION = ' + convert(nvarchar(max),@Partition)\r\n\t\t\t\t\t\t\telse case when @Frag>=@Seuil_Rebuild\r\n\t\t\t\t\t\t\t\t\tthen N' WITH (ONLINE=' + case when @Online=1 then N'ON' else N'OFF' end + N')'\r\n\t\t\t\t\t\t\t\t\telse N''\r\n\t\t\t\t\t\t\t\t\tend\r\n\t\t\t\t\t\t\tend\t\texec sp_executesql @Requete\r\n\r\n\t\tFETCH NEXT FROM Curseur_Index INTO @Schema,@Table,@Index,@Partition,@Frag,@Online,@AvecPartitions\r\n\tEND\r\n\r\n\tCLOSE Curseur_Index\r\n\tDEALLOCATE Curseur_Index\r\n\r\nEND\r\nGO<\/pre>\nApr\u00e8s, il n’y a plus qu’\u00e0 vous positionner sur la base \u00e0 traiter, et \u00e0 lancer la proc\u00e9dure stock\u00e9e, \u00e9ventuellement avec les param\u00e8tres ad\u00e9quats si vous souhaitez d’autres valeurs de seuils que celles par d\u00e9faut.<\/p>\n","protected":false},"excerpt":{"rendered":"
M\u00eame si cela n’est malheureusement pas suffisamment mis en place dans la pratique, il est rappel\u00e9 qu’il est tr\u00e8s important d’avoir des plans de maintenance des bases de donn\u00e9es SQL Server, avec notamment une \u00e9tape li\u00e9es \u00e0 la d\u00e9fragmentation des … Continuer la lecture