В правилах SSW для лучшей базы данных SQL Server приведен пример полного плана обслуживания базы данных: SSW. В этом примере они запускают как реорганизованный индекс, так и индекс перестройки, а затем обновляют статистику. Есть ли смысл в этом? Я думал, что Reorganize Index был быстрой, но менее эффективной версией индекса перестройки? и что перестроение индекса также автоматически обновит статистику (по меньшей мере, по кластерному индексу).
Перестроить индекс против индекса перестройки в плане обслуживания сервера Sql
Ответ 1
Выполнение REORGANIZE
, а затем a REBUILD
в тех же индексах бессмысленно, так как любые изменения REORGANIZE
будут потеряны при выполнении REBUILD
.
Хуже того, что на диаграмме плана обслуживания из SSW сначала выполняется SHRINK
, которая фрагментирует индексы как побочный эффект того, как он освобождает место. Затем REBUILD
выделяет больше места для файлов базы данных снова как рабочее пространство во время операции REBUILD
.
-
REORGANIZE
- это онлайн-операция, которая дефрагментирует страницы листа в кластерной или некластеризованной странице индекса за страницей, используя небольшое дополнительное рабочее пространство. -
REBUILD
- это онлайн-операция в выпусках Enterprise, в автономном режиме в других выпусках, и снова использует столько дополнительного рабочего пространства, как размер индекса. Он создает новую копию индекса, а затем отбрасывает старую, тем самым избавляясь от фрагментации. Статистика вычисляется по умолчанию как часть этой операции, но ее можно отключить.
Подробнее см. Реорганизация и восстановление индексов.
Не используйте SHRINK
, за исключением опции TRUNCATEONLY
, и даже тогда, если файл будет расти снова, вам следует подумать, нужно ли это:
Ответ 2
Реорганизация и перестройка - это разные вещи.
Реорганизовать: это дефрагментация для индексов. Принимает существующие индексы и дефрагментирует существующие страницы. Однако, если страницы не находятся в непрерывном режиме, они остаются прежними. Изменяется только содержимое страниц.
Перестроить: на самом деле он отбрасывает индекс и восстанавливает его с нуля. Это означает, что вы получите совершенно новый индекс с дефрагментированными и непрерывными страницами.
Кроме того, с перестройкой вы можете изменить разделы или группы файлов, но при реорганизации вы можете дефрагментировать не только весь индекс, но и только один раздел индекса.
Статистические данные обновления являются автоматическими для кластеризованных индексов, но не для некластеризованных.
Ответ 3
Прежде чем рассматривать обслуживание индексов, важно ответить на два основных вопроса:
- Какова степень фрагментации?
- Что такое подходящее действие? Реорганизовать или перестроить?
Как описано в этой статье http://solutioncenter.apexsql.com/why-when-and-how-to-rebuild-and-reorganize-sql-server-indexes/, и чтобы помочь вам определить, следует ли выполнять перестройку индекса или реорганизацию индекса, пожалуйста, поймите следующее
-
Реорганизация индекса - это процесс, когда SQL Server проходит через существующий индекс и очищает его. Перестройка индекса - это тяжелый процесс, в котором индекс удаляется, а затем воссоздается с нуля с совершенно новой структурой, свободной от всех сложенных фрагментов и пустых пространственных страниц.
-
В то время как реорганизация индекса - это операция чистой очистки, которая оставляет состояние системы без блокировки затронутых таблиц и представлений, процесс восстановления блокирует затронутую таблицу для всего периода восстановления, что может привести к длительному сокращению времени что не может быть приемлемым в некоторых средах. Имея это в виду, ясно, что перестройка индекса - это процесс с более сильным решением, но он связан с ценой - возможно, длинные блокировки на затронутых индексированных таблицах.
С другой стороны, реорганизация индекса - это "легкий процесс, который решает фрагментацию менее эффективным образом, поскольку очищенный индекс всегда будет вторым по сравнению с новым, полностью выполненным с нуля". Но реорганизационный индекс намного лучше с точки зрения эффективности, поскольку он не блокирует затронутую индексированную таблицу во время работы.
В этой статье также объясняется, как реорганизовывать и восстанавливать индексы с использованием SSMS, T-SQL (для реорганизации/восстановления индексов в таблице) и стороннего инструмента ApexSQL Backup.
Ответ 4
При выполнении реорганизации индекса, если индекс распространяется по двум или более физическим файлам, данные будут дефрагментировать только в файле данных. Страницы не перемещаются из одного файла данных в другой.
Когда индекс находится в одном файле, reorg и reindex будут иметь одинаковый конечный результат.
Несколько раз реорганизация будет быстрее, и иногда повторное задание будет быстрее в зависимости от того, насколько фрагментирован индекс. Чем меньше фрагментированный индекс, тем регресс будет быстрее, тем более фрагментированным будет медленнее реорганизация, но тем быстрее будет reindex.
Ответ 5
Именно то, что Biri сказал. Вот как я бы переиндексировал всю базу данных:
EXEC [sp_MSforeachtable] @command1="RAISERROR('DBCC DBREINDEX(''?'') ...',10,1) WITH NOWAIT DBCC DBREINDEX('?')"
Ответ 6
Я использую этот 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
и создайте одно задание, которое будет выполнять этот SP каждую неделю.
Ответ 7
Еще лучше:
EXEC sp_MSforeachtable 'ALTER INDEX ALL ON ? REINDEX'
или
EXEC sp_MSforeachtable 'ALTER INDEX ALL ON ? REORGANIZE'
Ответ 8
Мои два цента... Этот метод следует спецификациям, указанным в технической сети: 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
Ответ 9
Я исследовал в Интернете и нашел несколько хороших статей. На и я написал функцию и script ниже, которая реорганизует, воссоздает или восстанавливает все индексы в базе данных.
Сначала вам может потребоваться прочитать эту статью, чтобы понять, почему мы не просто воссоздаем все индексы.
Во-вторых, нам нужна функция для создания create script для индекса. Поэтому эта статья может помочь. Также я делюсь рабочей функцией ниже.
Последний шаг, создающий цикл while, чтобы найти и упорядочить все индексы в базе данных. Это видео - это грандиозный пример, чтобы сделать это.
Функции:
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 для while:
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 ['[email protected]+'] ON ['[email protected]+'].['[email protected]+'] 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 ['[email protected]+'] ON ['[email protected]+'].['[email protected]+'] 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