Fregmentation

-- internal Fragmentation - space at the page level.
-- Most of the pages should be full.
SELECT IX.name AS 'Name'
     , PS.index_level AS 'Level'
     , PS.page_count AS 'Pages'
     , PS.avg_page_space_used_in_percent AS 'Page Fullness (%)'
  FROM sys.dm_db_index_physical_stats( 
           DB_ID(), 
           OBJECT_ID('Product'), 
           DEFAULT, DEFAULT, 'DETAILED') PS
  JOIN sys.indexes IX
    ON IX.OBJECT_ID = PS.OBJECT_ID AND IX.index_id = PS.index_id 
  WHERE IX.name = 'PK_Product_ProductID';
GO

-- External Fragmentation - pages range are not in the following pages.
-- there is a gap between the first page to the last page.
SELECT IX.name AS 'Name'
, PS.index_level AS 'Level'
, PS.page_count AS 'Pages'
, PS.avg_fragmentation_in_percent AS 'External Fragmentation (%)'
, PS.fragment_count AS 'Fragments'
, PS.avg_fragment_size_in_pages AS 'Avg Fragment Size'
FROM sys.dm_db_index_physical_stats(
DB_ID(),
OBJECT_ID('Product'),
DEFAULT, DEFAULT, 'LIMITED') PS
JOIN sys.indexes IX
ON IX.OBJECT_ID = PS.OBJECT_ID AND IX.index_id = PS.index_id
WHERE IX.name = 'PK_Product_ProductID';