This script will let you know “how much your index is fragmented”
SELECT
OBJECT_NAME(o.object_id) AS TableName
,i.name AS IndexName
,i.type_desc AS IndexType
,CASE WHEN ISNULL(ps.function_id,1) = 1 THEN 'NO' ELSE 'YES' END AS Partitioned
,COALESCE(fg.name ,fg2.name) AS FileGroupName
,p.partition_number AS PartitionNumber
,p.rows AS PartitionRows
,ddips.Avg_Fragmentation_In_Percent
,ddips.Fragment_Count
,ddips.Avg_Fragment_Size_In_Pages
,ddips.Page_Count
,prvl.value AS PartitionLowerBoundaryValue
,prvr.value AS PartitionUpperBoundaryValue
,CASE WHEN pf.boundary_value_on_right = 1 THEN 'RIGHT' WHEN pf.boundary_value_on_right = 0 THEN 'LEFT' ELSE 'NONE' END AS PartitionRange
,pf.name AS PartitionFunction
,ds.name AS PartitionScheme
FROM sys.partitions AS p
INNER JOIN sys.indexes AS i
ON i.object_id = p.object_id
AND i.index_id = p.index_id
INNER JOIN sys.objects AS o
ON o.object_id = i.object_id
INNER JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') ddips
ON ddips.OBJECT_ID = i.object_id
AND ddips.index_id = i.index_id
AND ddips.partition_number = p.partition_number
LEFT JOIN sys.data_spaces AS ds
ON ds.data_space_id = i.data_space_id
LEFT JOIN sys.partition_schemes AS ps
ON ps.data_space_id = ds.data_space_id
LEFT JOIN sys.partition_functions AS pf
ON pf.function_id = ps.function_id
LEFT JOIN sys.destination_data_spaces AS dds
ON dds.partition_scheme_id = ps.data_space_id
AND dds.destination_id = p.partition_number
LEFT JOIN sys.filegroups AS fg
ON fg.data_space_id = i.data_space_id
LEFT JOIN sys.filegroups AS fg2
ON fg2.data_space_id = dds.data_space_id
LEFT JOIN sys.partition_range_values AS prvl
ON ps.function_id = prvl.function_id
AND prvl.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values AS prvr
ON ps.function_id = prvr.function_id
AND prvr.boundary_id = p.partition_number
ORDER BY
SchemaName
,TableName
,IndexName
,PartitionNumber
