Réorganiser l'index par rapport à reconstruire L'Index dans le plan de Maintenance du serveur Sql
dans les règles SSW pour améliorer la base de données du serveur SQL il y a un exemple de plan de maintenance de base de données complète: SSW . Dans l'exemple, ils exécutent à la fois un indice réorganiser et puis un indice reconstruire et puis mettre à jour des statistiques. Il y a tout point à cela? Je pensais que réorganiser L'Index était une version rapide mais moins efficace de Rebuild Index? et qu'une reconstruction de l'indice mettrait aussi automatiquement à jour les statistiques (sur l'indice groupé au moins).
9 réponses
en Faisant un REORGANIZE
puis un REBUILD
sur le même index est inutile, comme tout changement par l' REORGANIZE
serait perdu en faisant le REBUILD
.
pire que cela est que dans le diagramme du plan de maintenance de SSW, il effectue un SHRINK
d'abord, qui fragmente les index comme un effet secondaire de la façon dont il libère de l'espace. Puis le REBUILD
attribue plus d'espace aux fichiers de base de données à nouveau comme espace de travail pendant le REBUILD
opération.
-
REORGANIZE
il s'agit d'une opération en ligne qui défragme les pages dans un index groupé ou non groupé page par page en utilisant peu d'espace de travail supplémentaire. -
REBUILD
est une opération en ligne dans les éditions D'entreprise, hors ligne dans d'autres éditions, et utilise autant d'espace de travail supplémentaire que la taille de l'index. Il crée une nouvelle copie de l'index, puis supprime l'ancienne, donc se débarrasser de la fragmentation. Les statistiques sont recalculées par défaut dans le cadre de cette opération, mais elles peuvent être désactivées.
Voir la Réorganisation et la Reconstruction d'Index pour plus d'informations.
N'utilisez pas SHRINK
sauf avec l'option TRUNCATEONLY
et même si le fichier va se développer à nouveau alors vous devriez réfléchir si c'est nécessaire:
la réorganisation et la reconstruction sont des choses différentes.
réorganise: c'est un defrag pour les index. Prend l'index existant(es) et défragmente les pages existantes. Cependant, si les pages ne sont pas contiguës, elles restent comme avant. Seul le contenu des pages change.
Rebuild: en fait, il laisse tomber l'index et le reconstruit à partir de zéro. Cela signifie que vous obtiendrez un indice complètement nouveau, avec défragmenté et contigu page.
de plus avec rebuild vous pouvez changer le partitionnement ou les groupes de fichiers, mais avec reorganizate vous pouvez frauder non seulement l'index entier, mais aussi une seule partition de l'index.
les statistiques de mise à jour sont automatiques sur les index groupés, mais pas sur les non-groupés.
avant d'envisager la mise à jour des index, il est important de répondre à deux questions principales:
- Quel est le degré de fragmentation?
- Quelle est la mesure appropriée? Réorganiser ou reconstruire?
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 devriez effectuer la reconstruction de l'index ou la réorganisation de l'index, s'il vous plaît comprendre ce qui suit:
-
la réorganisation de L'Index est un processus par lequel le serveur SQL passe en revue l'index existant et le nettoie. La reconstruction d'Index est un processus lourd 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.
-
alors que la réorganisation indicielle est pur nettoyage opération qui laisse l'état du système tel qu'il est sans verrouillage des tables et des vues affectées, le processus de reconstruction verrouille la table affectée pour toute la période de reconstruction, ce qui peut entraîner de longs temps d'arrêt qui ne pourrait pas être acceptable dans certains environnements. Dans cet esprit, il est clair que la reconstruction de l'indice est un processus avec une solution "plus forte", mais il est livré avec un prix – de longues serrures possibles sur les tables indexées affectées.
sur l'autre side, index reorganization est un processus "léger" qui résoudra la fragmentation d'une manière moins efficace – depuis index nettoyé sera toujours la deuxième place après la nouvelle entièrement faite à partir de zéro. Mais réorganiser l'indice est beaucoup mieux du point de vue de l'efficacité, car il ne bloque pas la table indexée affectée au cours du fonctionnement.
l'article mentionné ci-dessus explique aussi comment réorganiser et reconstruire les index en utilisant SSMS, T-SQL (pour réorganiser / reconstruire index dans une table) et un outil tiers appelé sauvegarde ApexSQL.
Lors d'une réorganisation d'un indice, si l'indice est réparti entre deux ou plusieurs fichiers physiques les données seront seulement être défragmenté dans le fichier de données. Les Pages ne sont pas déplacées d'un fichier de données à un autre.
Lorsque l'index est dans un seul fichier à la réorganisation et réindexer aura le même résultat final.
parfois le reorg sera plus rapide, et parfois le reindex sera plus rapide en fonction de la fragmentation de l'indice. Le moins fragmenté l' index alors un reorg sera plus rapide, plus fragmenté le reorg sera lent, mais plus un reindex sera rapide.
Exactement ce que Biri , a déclaré. Voici comment je réindexerais une base de données entière:
EXEC [sp_MSforeachtable] @command1="RAISERROR('DBCC DBREINDEX(''?'') ...',10,1) WITH NOWAIT DBCC DBREINDEX('?')"
J'utilise cette 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 de créer un emploi qui exécutent cette PS chaque semaine.
encore mieux est:
EXEC sp_MSforeachtable 'ALTER INDEX ALL ON ? REINDEX'
ou
EXEC sp_MSforeachtable 'ALTER INDEX ALL ON ? REORGANIZE'
mes deux cents... Cette méthode suit les spécifications décrites 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 recherches 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 reconstruire tous les index dans une base de données.
D'abord, vous pourriez avoir besoin de lire cet article pour comprendre pourquoi nous ne sommes pas juste recréer tous les index.
Deuxièmement, nous avons besoin d'une fonction pour construire le script create Pour index. Donc cet article peut aider. Je suis aussi le partage de travail de la fonction ci-dessous.
dernière étape faire une boucle while pour trouver et organiser tous les index dans la base de données. Cette vidéo est râper exemple, pour les rendre cette.
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 tout:
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