In a previous chapter, we listed fragmentation information using the EnumFragmentation() method. Using the EnumFragmentation() method can be quite slow. An alternative to using this method is using the Dynamic Management Views (DMVs) and the
Dynamic Management Functions (DMFs) related to fragmentation. The following is an example of using the DMF dm_db_index_physical_stats() to query the average fragmentation for all indexes in a database:
Import-Module SQLPS -DisableNameChecking #current server name $servername = "ROGUE" $database = "Chinook" $query = @" SELECT OBJECT_NAME(phys_stats.OBJECT_ID) AS [Object], idx.name AS [Index Name], phys_stats.index_type_desc [Index Type], phys_stats.avg_fragmentation_in_percent [Fragmentation %], phys_stats.page_count [# Pages] FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL , N'LIMITED') AS phys_stats INNER JOIN sys.indexes AS idx WITH (NOLOCK) ON phys_stats.[object_id] = idx.[object_id] ...