Bom,,, o script abaixo analisa todos os objetos de índices da instância do SQL e mostra a fragmentação dos índices… em teoria, quanto maior a coluna fragmentation mais fragmentado estará o índice.
SELECT CONVERT(NVARCHAR(130), SERVERPROPERTY(‘servername’)) AS instancename , db.name AS databaseName , ps.OBJECT_ID AS objectID , ps.index_id AS indexID , ps.partition_number AS partitionNumber , ps.avg_fragmentation_in_percent AS fragmentation , ps.page_count FROM sys.databases db INNER JOIN sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, N’Limited’) ps ON db.database_id = ps.database_id WHERE ps.index_id > 0 AND ps.page_count > 100 AND ps.avg_fragmentation_in_percent > 30 OPTION ( MAXDOP 1 ) ;
|