May 13, 2011 at 1:09 am
Hi,
I did create a clustered index which did bring down the fragmentation level but again fragmentation level has started growing as shown below:
DatabaseIdIndexIdFragmentationPercent
72287.5
72478.26
72366.67
72164.29
71162.5
7957.14
72850
7144.44
72943.75
73043.75
72743.75
72543.75
72631.25
I rebuild the indexes using the following script every 3 hours:
USE MASTER
GO
SET NOCOUNT ON;
SELECT ROW_NUMBER() OVER(ORDER BY name) Seq, name Banco
INTO #Databases
FROM sys.databases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb', 'ReportServerTempDB')
AND compatibility_level > 80
ORDER BY Banco;
DECLARE
@Loop INT = 1,
@QT INT = (SELECT COUNT(1) FROM #Databases),
@Banco VARCHAR(50);
WHILE @Loop <= @QT
BEGIN
SET @Banco = (SELECT Banco FROM #Databases WHERE Seq = @Loop);
EXEC(
'USE ' + @Banco + '; ' +
'PRINT ''Database em uso: '' + db_name();
SELECT
ROW_NUMBER() OVER(ORDER BY p.object_id, p.index_id) Seq,
t.name Tabela, h.name Esquema,
i.name Indice, p.avg_fragmentation_in_percent Frag
INTO #Consulta
FROM
sys.dm_db_index_physical_stats(DB_ID(),null,null,null,null) p
join sys.indexes i on (p.object_id = i.object_id and p.index_id = i.index_id)
join sys.tables t on (p.object_id = t.object_id)
join sys.schemas h on (t.schema_id = h.schema_id)
where p.avg_fragmentation_in_percent > 10.0
and p.index_id > 0
and p.page_count >= 10
ORDER BY Esquema, Tabela;
DECLARE
@Loop INT = 1,
@Total INT = (SELECT COUNT(1) FROM #Consulta),
@Comando VARCHAR(500)
WHILE @Loop <= @Total
BEGIN
SELECT @Comando = ''ALTER INDEX '' + Indice +
'' ON '' + Esquema + ''.'' + Tabela +
( CASE WHEN Frag > 30.0 THEN '' REBUILD'' ELSE '' REORGANIZE'' END)
FROM #Consulta
WHERE Seq = @Loop;
EXEC(@Comando);
PRINT ''Executado: '' + @Comando;
SET @Loop = @Loop + 1;
END;
PRINT DB_NAME() + '' Qtde de índices afetados: '' + CONVERT(VARCHAR(5),@Total);
PRINT ''-----'';
DROP TABLE #Consulta;');
SET @Loop = @Loop + 1;
END;
DROP TABLE #Databases;
After I rebuild the indexes using the above script, I get the following fragmentation levels:
DatabaseIdIndexIdFragmentationPercent
71162.5
7957.14
72946.67
7144.44
72840
72240
72440
72533.33
72633.33
73033.33
Within an hour or so the fragmentation levels return to as shown in the first table.
May 13, 2011 at 1:32 am
Will dropping the indexes & recreating them once again be of any use?
May 13, 2011 at 1:33 am
and p.page_count >= 10
why 10? should be a lot higher value.
__________________________
Allzu viel ist ungesund...
May 13, 2011 at 1:45 am
What should be the ideal value for p.pagecount ????
May 13, 2011 at 1:53 am
kr.nithin (5/13/2011)
What should be the ideal value for p.pagecount ????
As advised above you can set it to 1000.
__________________________
Allzu viel ist ungesund...
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply