Getting fragmentation data

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]
 ...