--Fill Factor AYARLARINI AÇAR
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'fill factor (%)', 90;
RECONFIGURE;

GO

-- İlk olarak, tüm indekslerin varsayılan Fill Factor değerini %90 olarak güncelleyelim
EXEC sp_MSforeachdb 'USE [?]; EXEC sp_configure ''fill factor'', 90; RECONFIGURE;';

GO

-- Aşağıdaki kod, parçalanma derecesine göre indeksleri REORGANIZE veya REBUILD eder ve Fill Factor = %90 olarak ayarlar.
DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql = @sql + 
    CASE 
        WHEN avg_fragmentation_in_percent BETWEEN 10 AND 30 
        THEN 'ALTER INDEX [' + REPLACE(i.name, ']', ']]') + '] ON [' + s.name + '].[' + t.name + '] REORGANIZE;' + CHAR(13) 
        ELSE 'ALTER INDEX [' + REPLACE(i.name, ']', ']]') + '] ON [' + s.name + '].[' + t.name + '] REBUILD WITH (FILLFACTOR = 90);' + CHAR(13) 
    END
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') d
JOIN sys.indexes i ON d.[object_id] = i.[object_id] AND d.index_id = i.index_id
JOIN sys.tables t ON t.[object_id] = d.[object_id]
JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]
WHERE i.[name] IS NOT NULL;

PRINT @sql;  -- Önce sorguları kontrol et
EXEC sp_executesql @sql;

GO

-- İstatistikleri güncellemek de performansı artırır:
EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN;';
