Mediante esta consulta, podemos obtener el promedio de fragmentacion total que cada Datafile posee en nuestra base de datos.
DECLARE @FragmentationDB AS TABLE ([Row] SMALLINT, Fragmentation MONEY) INSERT INTO @FragmentationDB SELECT ROW_NUMBER() OVER (ORDER BY CAST(1.00*((CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2)))-(CONVERT(DECIMAL(12,2),ROUND(fileproperty(a.name,'SpaceUsed')/128.000,2)))) / (CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2))) *100 AS DECIMAL(6,2))) AS Row, AVG(CAST(1.00*((CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2)))-(CONVERT(DECIMAL(12,2),ROUND(fileproperty(a.name,'SpaceUsed')/128.000,2)))) / (CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2))) *100 AS DECIMAL(6,2))) AS Fragmentation FROM sysfiles a INNER JOIN sys.database_files b ON a.fileid=b.file_id WHERE CAST(1.00*((CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2)))-(CONVERT(DECIMAL(12,2),ROUND(fileproperty(a.name,'SpaceUsed')/128.000,2)))) / (CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2))) *100 AS DECIMAL(6,2)) != 0 GROUP BY CAST(1.00*((CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2)))-(CONVERT(DECIMAL(12,2),ROUND(fileproperty(a.name,'SpaceUsed')/128.000,2)))) / (CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2))) *100 AS DECIMAL(6,2)) --- Uncomment for Detail --SELECT * FROM @FragmentationDB SELECT SUM(Fragmentation) / @@ROWCOUNT AS AvgDatabaseFragmentation FROM @FragmentationDB
En caso de que queramos ver en detalle, cual es dicho porcentaje por cada Datafile, simplemente tenemos que descomentar la siguiente linea:
--SELECT * FROM @FragmentationDB