High Fragmentation Condition (over 70%)
SELECT PASD.Name
, PAST.TableName
, PASI.IndexName
, PASI.IndexType
, PASIP.[RowCount]
, PASIP.AvgFragmentationInPercent
, PASIP.AvgFragmentSizeInPages
, PASIP.PageCount
FROM [dbo].[PerformanceAnalysisSqlIndexPartition] PASIP
INNER JOIN [dbo].[PerformanceAnalysisSqlDatabase] PASD
ON PASIP.EventSourceConnectionID = PASD.EventSourceConnectionID
AND PASIP.DatabaseID = PASD.DatabaseID
INNER JOIN [dbo].[PerformanceAnalysisSqlTable] PAST
ON PASIP.EventSourceConnectionID = PAST.EventSourceConnectionID
AND PASIP.DatabaseID = PAST.DatabaseID
AND PASIP.TableID = PAST.TableID
INNER JOIN [dbo].[PerformanceAnalysisSqlIndex] PASI
ON PASIP.EventSourceConnectionID = PASI.EventSourceConnectionID
AND PASIP.DatabaseID = PASI.DatabaseID
AND PASIP.TableID = PASI.TableID
AND PASIP.IndexID = PASI.IndexID
WHERE PASIP.AvgFragmentationInPercent > 70
AND PASIP.[EventSourceConnectionID] = @ConnectionID
ORDER BY PASIP.EventSourceConnectionID
, PASIP.DatabaseID
, PASIP.TableID
, PASIP.IndexID