Let's break down the most likely reasons for this discrepancy—I’ve run into this exact head-scratcher a few times, so here’s what to check:

• You’re measuring different page metrics: Used vs. Reserved
  Your T-SQL uses SUM(sz.[used_page_count]), which only counts pages actively storing data. But SSMS’s "Index Space" metric typically relies on reserved_page_count instead—this includes all pages allocated to the index, even empty ones reserved for future growth (especially relevant after rebuilding indexes with a non-100% fill factor). To match SSMS’s number, swap used_page_count with reserved_page_count in your query, and simplify the calculation (your original math had redundant conversions):

  SELECT 
      tn.[name] AS [Table name], 
      ix.[name] AS [Index name], 
      SUM(sz.[reserved_page_count]) * 8 / 1024 AS [Index size (MB)] 
  FROM sys.dm_db_partition_stats AS sz 
  INNER JOIN sys.indexes AS ix ON sz.[object_id] = ix.[object_id] AND sz.[index_id] = ix.[index_id] 
  INNER JOIN sys.tables tn ON tn.OBJECT_ID = ix.object_id 
  WHERE tn.[name] = 'MyTableName' 
  GROUP BY tn.[name], ix.[name] 
  ORDER BY tn.[name]
  

• SSMS might exclude the clustered index (or vice versa)
  In SSMS’s table properties, "Index Space" usually refers only to non-clustered indexes. The clustered index is counted under "Data Space" because it’s the physical storage of the table data itself. If you’re summing all indexes (including the clustered one) from your T-SQL results, that total will be larger than SSMS’s "Index Space" value. To verify, filter out the clustered index in your query by adding AND ix.index_id != 1 (index_id 1 = clustered index, 0 = heap).

• Missing special index types in your query
  Your query uses sys.indexes, which covers relational indexes (clustered, non-clustered, columnstore), but it doesn’t include full-text or XML indexes. If your table has either of these, SSMS will count their space in the "Index Space" total, but your T-SQL won’t capture them. You’d need to query sys.fulltext_indexes or sys.xml_indexes separately to include those sizes.

• SSMS is showing cached/stale data
  Sometimes SSMS doesn’t refresh table properties automatically after index rebuilds. Right-click the table, select "Refresh", then recheck the storage metrics—this quick fix is easy to overlook but often resolves the issue.

• Partitioned table edge cases
  If your table is partitioned, double-check that your query sums across all partitions (which it should, since you’re using SUM() without filtering partition_id). Rarely, SSMS might have a bug in calculating partitioned index totals, but this is far less common than the issues above.

内容的提问来源于stack exchange，提问作者Francesco Mantovani