Iako na linku http://ola.hallengren.com/Documentation.html postoje vec gotove procedure za rekreiranje i reogranizovanje indeksa, video sam da rekreiranje bas i ne radi onako kako sam hteo.
Ista stvar se moze uraditi jednostavnije, mada sporije.
Prvo cemo proveriti fragmentaciju indeksa za videti sve tabele:
select OBJECT_NAME(object_id),index_type_desc,avg_fragmentation_in_percent,page_count
from sys.dm_db_index_physical_stats(DB_ID(N'Tabela'), NULL, NULL, NULL, NULL)
order by avg_fragmentation_in_percent desc
Zatim cemo izvrsiti upit koji radi reindeksiranje za sve tabele kojima je fragmentacja veca od 1% i koji moze da traje i nekoliko desetina minuta:
DECLARE @TableName VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE TableCursor CURSOR FOR
select OBJECT_NAME(object_id) AS TableName
from sys.dm_db_index_physical_stats(DB_ID(N'Tabela'), NULL, NULL, NULL, NULL)
where avg_fragmentation_in_percent > 1
order by avg_fragmentation_in_percent desc
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @TableName
SET @cmd = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = 1)'
EXEC (@cmd)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment