Перепостроение и реорганизация индексов

Перестроение и реорганизация индексов направлены на борьбу с различными типами фрагментации. В случае перепостроения индекс создается заново что влечет за собой наложение блокировок и недоступность индекса (или таблицы, если индекс кластерный) в течении всего этого процесса.
Реорганизация индексов - более легкая операция, таблицы и индексы в течении нее остаются доступными.
В процессе поддержки БД перепостроение и реорганизацию индексов, рекомендуется делать регулярно. Для индексов с фрагментацией менее 30 процентов достаточно реорганизации, если фрагментации больше, то следует делать перепостроение.
Скрипт для избирательно выполнения этих действий приведен ниже и взят отсюда

SQL SCRIPT:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DB_Maint_Indexes]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[DB_Maint_Indexes]
GO
CREATE PROCEDURE [dbo].[DB_Maint_Indexes]
AS
BEGIN
-- This will work on MS SQL Server 2005+
-- Based on the websites:
-- http://msdn.microsoft.com/en-us/library/ms189858.aspx
-- http://msdn.microsoft.com/en-us/library/ms189858(v=SQL.105).aspx
-- http://msdn.microsoft.com/en-us/library/ms189858(v=SQL.90).aspx
--> 5% and < = 30% - ALTER INDEX REORGANIZE
--> 30% - ALTER INDEX REBUILD WITH (ONLINE = ON)*


-- update all stats... use at your own discretion
--EXEC sp_updatestats;


DECLARE indexes_cur CURSOR
FOR
-- Change the top to the number of indexes you want to update
SELECT TOP 20
CASE WHEN avg_fragmentation_in_percent > 30 THEN
'ALTER INDEX ' + b.name + ' ON [' + s.name + '].[' + t.name
+ '] REBUILD;'
ELSE
'ALTER INDEX ' + b.name + ' ON [' + s.name + '].[' + t.name
+ '] REORGANIZE;'
END AS [sSQL]
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS a
INNER JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
INNER JOIN sys.tables t on t.object_id = b.object_id
INNER JOIN sys.schemas s on t.schema_id = s.schema_id
WHERE NOT b.name IS NULL
AND avg_fragmentation_in_percent > 5
ORDER BY avg_fragmentation_in_percent DESC;


OPEN indexes_cur;
DECLARE @sSQL varchar(1024);
FETCH NEXT FROM indexes_cur INTO @sSQL;
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- EXECUTE THE SQL TO REBUILD THE INDEXES
EXECUTE ('' + @sSQL + ';');
FETCH NEXT FROM indexes_cur INTO @sSQL
END;
CLOSE indexes_cur;
DEALLOCATE indexes_cur;
END


GO

DECLARE @SQL nvarchar(2048);
declare @dbname nvarchar(50);
declare @dbid int

set @dbname = 'Office';
set @dbid = DB_ID('Office');

DECLARE reindex10 CURSOR GLOBAL FAST_FORWARD READ_ONLY FOR

SELECT 'ALTER INDEX ALL ON [' + @dbname+ ']..[' + OBJECT_NAME(afp.[OBJECT_ID]) + '] REBUILD WITH (SORT_IN_TEMPDB = Off);' AS [Инструкция T-SQL]
FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL, NULL, 'SAMPLED') AS afp
WHERE afp.database_id = @dbid
AND afp.index_type_desc IN ('CLUSTERED INDEX')
AND (afp.avg_fragmentation_in_percent >= 15 OR afp.avg_page_space_used_in_percent <= 60)
AND afp.page_count > 12
UNION ALL
SELECT [Инструкция T-SQL] =
CASE
WHEN afp.avg_fragmentation_in_percent >= 15
OR afp.avg_page_space_used_in_percent <= 60
THEN 'ALTER INDEX [' + i.name + '] ON [' + @dbname + ']..[' + OBJECT_NAME(afp.[OBJECT_ID]) + '] REBUILD WITH (SORT_IN_TEMPDB = Off);'
WHEN (afp.avg_fragmentation_in_percent < 15 AND afp.avg_fragmentation_in_percent >= 10)
OR (afp.avg_page_space_used_in_percent > 60 AND afp.avg_page_space_used_in_percent < 75)
THEN 'ALTER INDEX [' + i.name + '] ON [' + @dbname + ']..[' + OBJECT_NAME(afp.[OBJECT_ID]) + '] REORGANIZE;'
END
FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL, NULL, 'SAMPLED') AS afp
JOIN sys.indexes AS i
ON (afp.[OBJECT_ID] = i.[OBJECT_ID] AND afp.index_id = i.index_id)
AND afp.database_id = @dbid
AND afp.index_type_desc IN ('NONCLUSTERED INDEX')
AND (
(afp.avg_fragmentation_in_percent >= 10 AND afp.avg_fragmentation_in_percent < 15)
OR (afp.avg_page_space_used_in_percent > 60 AND afp.avg_page_space_used_in_percent < 75)
)
AND afp.page_count > 12
AND afp.[OBJECT_ID] NOT IN (
SELECT [OBJECT_ID]
FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL, NULL, 'SAMPLED')
WHERE database_id = @dbid
AND index_type_desc IN ('CLUSTERED INDEX')
AND (avg_fragmentation_in_percent >= 15 OR avg_page_space_used_in_percent < 60)
AND page_count > 1
)
ORDER BY [Инструкция T-SQL]



OPEN GLOBAL reindex10
WHILE 1 = 1
BEGIN
FETCH reindex10 INTO @SQL
IF @@fetch_status <> 0 BREAK
--EXEC(@SQL)
PRINT @SQL

END
CLOSE GLOBAL reindex10
DEALLOCATE GLOBAL reindex10