Réorganiser l'index par rapport à l'indice de reconstruction dans le plan de maintenance du server SQL Server

Faire un REORGANIZE puis un REBUILD sur les mêmes index est inutile, car tout changement par le REORGANIZE serait perdu en faisant le REBUILD .

Pire que cela, c'est que dans le diagramme de plan de maintenance de SSW, il effectue d'abord un SHRINK , qui fragmente les index comme un effet secondaire de la façon dont il libère de l'espace. Ensuite, le REBUILD alloue à nouveau plus d'espace aux files de database en tant qu'espace de travail pendant l'opération REBUILD .

• REORGANIZE est une opération en ligne qui défragmente les pages feuilles dans un index clusterisé ou non clusterisé page par page en utilisant peu d'espace de travail supplémentaire.

• REBUILD est une opération en ligne dans les éditions Enterprise, hors ligne dans d'autres éditions, et utilise à nouveau autant d'espace de travail supplémentaire que la taille de l'index. Il crée une nouvelle copy de l'index, puis supprime l'ancien, éliminant ainsi la fragmentation. Les statistics sont recalculées par défaut dans le cadre de cette opération, mais cela peut être désactivé.

Voir Réorganisation et reconstruction des index pour plus d'informations.

N'utilisez pas SHRINK sauf avec l'option TRUNCATEONLY et même si le file va se développer à nouveau, vous devez réfléchir sérieusement à la nécessité de:

sqlservercentral_SHRINKFILE

Les réorganiser et rebuild sont des choses différentes.

Réorganiser: c'est une défragmentation pour les index. Prend les index existants et défragmente les pages existantes. Cependant, si les pages ne sont pas contiguës, elles restnt comme avant. Seul le contenu des pages change.

Rebuild: en fait, il supprime l'index et le reconstruit à partir de zéro. Cela signifie que vous obtiendrez un index complètement nouveau, avec des pages défragmentées et contiguës.

De plus, avec rebuild, vous pouvez modifier le partitionnement ou les groupes de files, mais avec la réorganisation, vous pouvez défragmenter non seulement l'index entier, mais également une seule partition de l'index.

Les statistics de mise à jour sont automatiques sur les index clusterisés, mais pas sur les index non clusterisés.

Avant d'envisager la maintenance des index, il est important de répondre à deux questions principales:

1. Quel est le degré de fragmentation?
2. Quelle est l'action appropriée? Réorganiser ou rebuild?

Comme décrit dans cet article http://solutioncenter.apexsql.com/why-when-and-how-to-rebuild-and-reorganize-sql-server-indexes/ , et pour vous aider à déterminer si vous devez effectuer une reconstruction d'index ou réorganisation de l'indice, veuillez comprendre ce qui suit:

• La réorganisation d'index est un process dans lequel SQL Server parcourt l'index existant et le nettoie. La reconstruction d'index est un process robuste où l'index est supprimé puis recréé à partir de zéro avec une structure entièrement nouvelle, libre de tous les fragments empilés et des pages vides.

• Tandis que la réorganisation d'index est une opération de nettoyage pur qui laisse l'état du système sans verrouiller les tables et les vues affectées, le process de reconstruction verrouille la table affectée pendant toute la période de reconstruction, ce qui peut entraîner de longs time d'inactivité. certains environnements. En gardant cela à l'esprit, il est clair que la reconstruction de l'index est un process avec une solution «plus forte», mais avec un prix – des verrous longs possibles sur les tables indexées affectées.

D'un autre côté, la réorganisation de l'index est un process «léger» qui résoudra la fragmentation de manière less efficace – puisque l'index nettoyé sera toujours le deuxième du nouveau entièrement fabriqué à partir de zéro. Mais réorganiser l'index est beaucoup mieux du sharepoint vue de l'efficacité, car il ne bloque pas la table indexée affectée au cours de l'opération.

L'article mentionné ci-dessus explique également comment réorganiser et rebuild des index en utilisant SSMS, T-SQL (pour réorganiser / rebuild des index dans une table) et un outil tiers appelé ApexSQL Backup.

Lorsque vous effectuez une réorganisation d'un index, si l'index est réparti sur deux files physiques ou plus, datatables ne seront défragmentées que dans le file de données. Les pages ne sont pas déplacées d'un file de données à un autre.

Lorsque l'index est dans un seul file, le reorg et le reindex auront le même résultat final.

Parfois, le reorg sera plus rapide, et parfois le réindex sera plus rapide en fonction de la fragmentation de l'index. Moins l'indice est fragmenté, plus le reorg sera rapide, plus le reorg sera fragmenté, plus le reindex sera rapide.

Exactement ce que Biri a dit. Voici comment je réindexerais une database entière:

 EXEC [sp_MSforeachtable] @command1="RAISERROR('DBCC DBREINDEX(''?'') ...',10,1) WITH NOWAIT DBCC DBREINDEX('?')" 

J'utilise ce SP

 CREATE PROCEDURE dbo.[IndexRebuild] AS DECLARE @TableName NVARCHAR(500); DECLARE @SQLIndex NVARCHAR(MAX); DECLARE @RowCount INT; DECLARE @Counter INT; DECLARE @IndexAnalysis TABLE ( AnalysisID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY , TableName NVARCHAR(500) , SQLText NVARCHAR(MAX) , IndexDepth INT , AvgFragmentationInPercent FLOAT , FragmentCount BIGINT , AvgFragmentSizeInPages FLOAT , PageCount BIGINT ) BEGIN INSERT INTO @IndexAnalysis SELECT [objects].name , 'ALTER INDEX [' + [indexes].name + '] ON [' + [schemas].name + '].[' + [objects].name + '] ' + ( CASE WHEN ( [dm_db_index_physical_stats].avg_fragmentation_in_percent >= 20 AND [dm_db_index_physical_stats].avg_fragmentation_in_percent < 40 ) THEN 'REORGANIZE' WHEN [dm_db_index_physical_stats].avg_fragmentation_in_percent > = 40 THEN 'REBUILD' END ) AS zSQL , [dm_db_index_physical_stats].index_depth , [dm_db_index_physical_stats].avg_fragmentation_in_percent , [dm_db_index_physical_stats].fragment_count , [dm_db_index_physical_stats].avg_fragment_size_in_pages , [dm_db_index_physical_stats].page_count FROM [sys].[dm_db_index_physical_stats](DB_ID(), NULL, NULL, NULL, 'LIMITED') AS [dm_db_index_physical_stats] INNER JOIN [sys].[objects] AS [objects] ON ( [dm_db_index_physical_stats].[object_id] = [objects].[object_id] ) INNER JOIN [sys].[schemas] AS [schemas] ON ( [objects].[schema_id] = [schemas].[schema_id] ) INNER JOIN [sys].[indexes] AS [indexes] ON ( [dm_db_index_physical_stats].[object_id] = [indexes].[object_id] AND [dm_db_index_physical_stats].index_id = [indexes].index_id ) WHERE index_type_desc <> 'HEAP' AND [dm_db_index_physical_stats].avg_fragmentation_in_percent > 20 END SELECT @RowCount = COUNT(AnalysisID) FROM @IndexAnalysis SET @Counter = 1 WHILE @Counter <= @RowCount BEGIN SELECT @SQLIndex = SQLText FROM @IndexAnalysis WHERE AnalysisID = @Counter EXECUTE sp_executesql @SQLIndex SET @Counter = @Counter + 1 END GO 

et créez un Job qui exécute ce SP chaque semaine.

Mieux encore:

 EXEC sp_MSforeachtable 'ALTER INDEX ALL ON ? REINDEX' 

ou

 EXEC sp_MSforeachtable 'ALTER INDEX ALL ON ? REORGANIZE' 

Mes deux cents … Cette méthode suit la spécification décrite sur tech net: http://technet.microsoft.com/en-us/library/ms189858(v=sql.105).aspx

 USE [MyDbName] GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [maintenance].[IndexFragmentationCleanup] AS DECLARE @reIndexRequest VARCHAR(1000) DECLARE reIndexList CURSOR FOR SELECT INDEX_PROCESS FROM ( SELECT CASE WHEN avg_fragmentation_in_percent BETWEEN 5 AND 30 THEN 'ALTER INDEX [' + i.NAME + '] ON [' + t.NAME + '] REORGANIZE;' WHEN avg_fragmentation_in_percent > 30 THEN 'ALTER INDEX [' + i.NAME + '] ON [' + t.NAME + '] REBUILD with(ONLINE=ON);' END AS INDEX_PROCESS ,avg_fragmentation_in_percent ,t.NAME FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, NULL) AS a INNER JOIN sys.indexes AS i ON a.object_id = i.object_id AND a.index_id = i.index_id INNER JOIN sys.tables t ON t.object_id = i.object_id WHERE i.NAME IS NOT NULL ) PROCESS WHERE PROCESS.INDEX_PROCESS IS NOT NULL ORDER BY avg_fragmentation_in_percent DESC OPEN reIndexList FETCH NEXT FROM reIndexList INTO @reIndexRequest WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRY PRINT @reIndexRequest; EXEC (@reIndexRequest); END TRY BEGIN CATCH DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = 'UNABLE TO CLEAN UP INDEX WITH: ' + @reIndexRequest + ': MESSAGE GIVEN: ' + ERROR_MESSAGE() ,@ErrorSeverity = 9 ,@ErrorState = ERROR_STATE(); END CATCH; FETCH NEXT FROM reIndexList INTO @reIndexRequest END CLOSE reIndexList; DEALLOCATE reIndexList; RETURN 0 GO 

J'ai fait des searchs sur le web et j'ai trouvé quelques bons articles. Au et j'ai écrit la fonction et le script ci-dessous qui est réorganiser, recréer ou rebuild tous les index dans une database.

Vous devrez peut-être d'abord lire cet article pour comprendre pourquoi nous ne recréons pas tous les index.

Deuxièmement, nous avons besoin d'une fonction pour créer un script de création pour l'index. Donc, cet article peut aider. Aussi je partage la fonction de travail ci-dessous.

Dernière étape faisant une boucle while pour find et organiser tous les index dans la database. Cette video est un exemple de grid pour faire ceci.

Fonction:

 create function GetIndexCreateScript( @index_name nvarchar(100) ) returns nvarchar(max) as begin declare @Return varchar(max) SELECT @Return = ' CREATE ' + CASE WHEN I.is_unique = 1 THEN ' UNIQUE ' ELSE '' END + I.type_desc COLLATE DATABASE_DEFAULT +' INDEX ' + I.name + ' ON ' + Schema_name(T.Schema_id)+'.'+T.name + ' ( ' + KeyColumns + ' ) ' + ISNULL(' INCLUDE ('+IncludedColumns+' ) ','') + ISNULL(' WHERE '+I.Filter_definition,'') + ' WITH ( ' + CASE WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON ' ELSE ' PAD_INDEX = OFF ' END + ',' + 'FILLFACTOR = '+CONVERT(CHAR(5),CASE WHEN I.Fill_factor = 0 THEN 100 ELSE I.Fill_factor END) + ',' + -- default value 'SORT_IN_TEMPDB = OFF ' + ',' + CASE WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON ' ELSE ' IGNORE_DUP_KEY = OFF ' END + ',' + CASE WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF ' ELSE ' STATISTICS_NORECOMPUTE = ON ' END + ',' + -- default value ' DROP_EXISTING = ON ' + ',' + -- default value ' ONLINE = OFF ' + ',' + CASE WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON ' ELSE ' ALLOW_ROW_LOCKS = OFF ' END + ',' + CASE WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON ' ELSE ' ALLOW_PAGE_LOCKS = OFF ' END + ' ) ON [' + DS.name + ' ] ' FROM sys.indexes I JOIN sys.tables T ON T.Object_id = I.Object_id JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid JOIN (SELECT * FROM ( SELECT IC2.object_id , IC2.index_id , STUFF((SELECT ' , ' + C.name + CASE WHEN MAX(CONVERT(INT,IC1.is_descending_key)) = 1 THEN ' DESC ' ELSE ' ASC ' END FROM sys.index_columns IC1 JOIN Sys.columns C ON C.object_id = IC1.object_id AND C.column_id = IC1.column_id AND IC1.is_included_column = 0 WHERE IC1.object_id = IC2.object_id AND IC1.index_id = IC2.index_id GROUP BY IC1.object_id,C.name,index_id ORDER BY MAX(IC1.key_ordinal) FOR XML PATH('')), 1, 2, '') KeyColumns FROM sys.index_columns IC2 --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables GROUP BY IC2.object_id ,IC2.index_id) tmp3 )tmp4 ON I.object_id = tmp4.object_id AND I.Index_id = tmp4.index_id JOIN sys.stats ST ON ST.object_id = I.object_id AND ST.stats_id = I.index_id JOIN sys.data_spaces DS ON I.data_space_id=DS.data_space_id JOIN sys.filegroups FG ON I.data_space_id=FG.data_space_id LEFT JOIN (SELECT * FROM ( SELECT IC2.object_id , IC2.index_id , STUFF((SELECT ' , ' + C.name FROM sys.index_columns IC1 JOIN Sys.columns C ON C.object_id = IC1.object_id AND C.column_id = IC1.column_id AND IC1.is_included_column = 1 WHERE IC1.object_id = IC2.object_id AND IC1.index_id = IC2.index_id GROUP BY IC1.object_id,C.name,index_id FOR XML PATH('')), 1, 2, '') IncludedColumns FROM sys.index_columns IC2 --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables GROUP BY IC2.object_id ,IC2.index_id) tmp1 WHERE IncludedColumns IS NOT NULL ) tmp2 ON tmp2.object_id = I.object_id AND tmp2.index_id = I.index_id WHERE I.is_primary_key = 0 AND I.is_unique_constraint = 0 AND I.[name] = @index_name return @Return end 

Sql pour le moment:

 declare @RebuildIndex Table( IndexId int identity(1,1), IndexName varchar(100), TableSchema varchar(50), TableName varchar(100), Fragmentation decimal(18,2) ) insert into @RebuildIndex (IndexName,TableSchema,TableName,Fragmentation) SELECT B.[name] as 'IndexName', Schema_Name(O.[schema_id]) as 'TableSchema', OBJECT_NAME(A.[object_id]) as 'TableName', A.[avg_fragmentation_in_percent] Fragmentation FROM sys.dm_db_index_physical_stats(db_id(),NULL,NULL,NULL,'LIMITED') A INNER JOIN sys.indexes B ON A.[object_id] = B.[object_id] and A.index_id = B.index_id INNER JOIN sys.objects O ON O.[object_id] = B.[object_id] where B.[name] is not null and B.is_primary_key = 0 AND B.is_unique_constraint = 0 and A.[avg_fragmentation_in_percent] >= 5 --select * from @RebuildIndex declare @begin int = 1 declare @max int select @max = Max(IndexId) from @RebuildIndex declare @IndexName varchar(100), @TableSchema varchar(50), @TableName varchar(100) , @Fragmentation decimal(18,2) while @begin <= @max begin Select @IndexName = IndexName from @RebuildIndex where IndexId = @begin select @TableSchema = TableSchema from @RebuildIndex where IndexId = @begin select @TableName = TableName from @RebuildIndex where IndexId = @begin select @Fragmentation = Fragmentation from @RebuildIndex where IndexId = @begin declare @sql nvarchar(max) if @Fragmentation < 31 begin set @sql = 'ALTER INDEX ['+@IndexName+'] ON ['+@TableSchema+'].['+@TableName+'] REORGANIZE WITH ( LOB_COMPACTION = ON )' print 'Reorganized Index ' + @IndexName + ' for ' + @TableName + ' Fragmentation was ' + convert(nvarchar(18),@Fragmentation) end else begin set @sql = (select dbo.GetIndexCreateScript(@IndexName)) if(@sql is not null) begin print 'Recreated Index ' + @IndexName + ' for ' + @TableName + ' Fragmentation was ' + convert(nvarchar(18),@Fragmentation) end else begin set @sql = 'ALTER INDEX ['+@IndexName+'] ON ['+@TableSchema+'].['+@TableName+'] REBUILD PARTITION = ALL WITH (ONLINE = ON)' print 'Rebuilded Index ' + @IndexName + ' for ' + @TableName + ' Fragmentation was ' + convert(nvarchar(18),@Fragmentation) end end execute(@sql) set @begin = @begin+1 end