There are a number of ways to reindex a database. This is my preferred method.
First, execute the below to check the fragmentation level of the active database:
declare @fragmentation float
set @fragmentation = 20
SELECT
S.name as [Schema],
T.name as [Table],
I.name as [Index],
DDIPS.avg_fragmentation_in_percent
FROM
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN
sys.tables T with (nolock)
on
(
T.object_id = DDIPS.object_id
)
INNER JOIN
sys.schemas S with (nolock)
on
(
T.schema_id = S.schema_id
)
INNER JOIN
sys.indexes I with (nolock)
ON
(
I.object_id = DDIPS.object_id AND
DDIPS.index_id = I.index_id
)
WHERE
DDIPS.database_id = DB_ID() and
I.name is not null AND
DDIPS.avg_fragmentation_in_percent > @fragmentation
ORDER BY
DDIPS.avg_fragmentation_in_percent desc
Run the database reindex by executing the following:
declare @fragmentation float
set @fragmentation = 20
declare @schema_name sysname,
@table_name sysname,
@index_name sysname
DECLARE cursor_tables
CURSOR FOR
SELECT
S.name as 'Schema',
T.name as 'Table',
I.name as 'Index'
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T with (nolock) on (T.object_id = DDIPS.object_id)
INNER JOIN sys.schemas S with (nolock) on (T.schema_id = S.schema_id)
INNER JOIN sys.indexes I with (nolock) ON (I.object_id = DDIPS.object_id AND DDIPS.index_id = I.index_id)
WHERE
DDIPS.database_id = DB_ID() and
I.name is not null AND
DDIPS.avg_fragmentation_in_percent > @fragmentation
ORDER BY
DDIPS.avg_fragmentation_in_percent desc
open cursor_tables
fetch next from cursor_tables
into @schema_name, @table_name, @index_name
while @@fetch_status = 0
begin
exec
(
'
ALTER INDEX [' + @index_name + '] ON [' + @schema_name + '].[' + @table_name + ']
REBUILD WITH(FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON)
'
)
exec
(
'
ALTER INDEX [' + @index_name + '] ON [' + @schema_name + '].[' + @table_name + ']
REORGANIZE
'
)
fetch next from cursor_tables
into @schema_name, @table_name, @index_name
end
close cursor_tables
deallocate cursor_tables
And finally, update statistics by running this
EXEC sp_updatestats
Article created mostly for personal reference; there are of course a few different ways in which these scripts could be improved, but when providing them to clients the simplest solution is often the best.
Top comments (0)