MSSQL Index Bloat Analysis — Prod (2026-03-13)

Queries Used

1. Space bloat (via sys.allocation_units)

SELECT TOP 30
  OBJECT_NAME(p.object_id) AS TableName,
  i.name AS IndexName,
  i.type_desc AS IndexType,
  SUM(au.total_pages) AS TotalPages,
  CAST(SUM(au.total_pages) * 8.0 / 1024 AS DECIMAL(10,2)) AS TotalMB,
  CAST(SUM(au.used_pages) * 8.0 / 1024 AS DECIMAL(10,2)) AS UsedMB,
  CAST((SUM(au.total_pages) - SUM(au.used_pages)) * 8.0 / 1024 AS DECIMAL(10,2)) AS UnusedMB,
  CASE WHEN SUM(au.total_pages) > 0
    THEN CAST(100.0 * (SUM(au.total_pages) - SUM(au.used_pages)) / SUM(au.total_pages) AS DECIMAL(5,2))
    ELSE 0 END AS UnusedPct
FROM sys.partitions p
JOIN sys.allocation_units au ON p.partition_id = au.container_id
JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE OBJECTPROPERTY(p.object_id, 'IsUserTable') = 1
GROUP BY p.object_id, i.name, i.type_desc
HAVING SUM(au.total_pages) > 100
ORDER BY SUM(au.total_pages) DESC

2. Index usage stats (reads vs writes)

SELECT TOP 40
  OBJECT_NAME(i.object_id) AS Tbl,
  i.name AS Idx,
  i.type_desc AS Tp,
  ISNULL(ius.user_seeks, 0) AS Seeks,
  ISNULL(ius.user_scans, 0) AS Scans,
  ISNULL(ius.user_lookups, 0) AS Lkups,
  ISNULL(ius.user_updates, 0) AS Upd
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius
  ON i.object_id = ius.object_id
  AND i.index_id = ius.index_id
  AND ius.database_id = DB_ID()
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
  AND i.name IS NOT NULL
ORDER BY ISNULL(ius.user_seeks,0) + ISNULL(ius.user_scans,0) + ISNULL(ius.user_lookups,0) ASC

3. Unused indexes with sizes (0 reads)

SELECT
  OBJECT_NAME(i.object_id) AS Tbl,
  i.name AS Idx,
  i.type_desc AS Tp,
  CAST(SUM(au.total_pages) * 8.0 / 1024 AS DECIMAL(10,2)) AS SizeMB,
  ISNULL(ius.user_seeks,0) + ISNULL(ius.user_scans,0) + ISNULL(ius.user_lookups,0) AS Reads,
  ISNULL(ius.user_updates, 0) AS Writes
FROM sys.indexes i
JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.allocation_units au ON p.partition_id = au.container_id
LEFT JOIN sys.dm_db_index_usage_stats ius
  ON i.object_id = ius.object_id
  AND i.index_id = ius.index_id
  AND ius.database_id = DB_ID()
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
  AND i.name IS NOT NULL
  AND ISNULL(ius.user_seeks,0) + ISNULL(ius.user_scans,0) + ISNULL(ius.user_lookups,0) = 0
GROUP BY i.object_id, i.name, i.type_desc,
         ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates
HAVING SUM(au.total_pages) > 100
ORDER BY SUM(au.total_pages) DESC

---

Space Bloat (via sys.allocation_units)

Top indexes by total size, showing unused space:

Table	Index	Type	Total Size	Used	Unused	Bloat %
ReviewEmailProducts	HEAP (no clustered index!)	HEAP	1.1 TB	778 GB	370 GB	32.28%
QueueReviewEmail	PK (clustered)	CLUSTERED	734 GB	734 GB	0.08 GB	0.01%
ReviewEmailRecommendations	PK (clustered)	CLUSTERED	519 GB	350 GB	169 GB	32.54%
ReviewEmailProducts	IX_ReviewEmailProducts_1	NC	380 GB	372 GB	8.4 GB	2.21%
ReviewEmailRecommendations	NC_..queueReviewEmailId	NC	245 GB	244 GB	1.4 GB	0.55%
Review	PK (clustered)	CLUSTERED	175 GB	175 GB	0.17 GB	0.10%
ReviewEmailProducts2	IX_ReviewEmailProducts2_1	NC	165 GB	165 GB	0.006 GB	0.00%
ReviewEmailProducts2	PK_ReviewEmailProducts2_Id	CLUSTERED	165 GB	165 GB	0.04 GB	0.02%
QueueReviewEmail	IX_QueueReviewEmail_1	NC	161 GB	161 GB	0.07 GB	0.05%
Review	IX_Shop_ID_ReviewState_Rating_...	NC	154 GB	154 GB	0.18 GB	0.12%

Unused Indexes (0 reads, write-only)

Indexes consuming space and write I/O but never read (since last SQL Server restart):

Table	Index	Type	Size	Writes	Verdict
QueueReviewEmailLog	NC_..ShopId_TrackingStatus_DateSent	NC	23.4 GB	6.6M	Drop candidate
Customer	NC_Customer_ShopId_IsDeleted_FirstName	NC	9.5 GB	315K	Drop candidate
RewardCampaignActivity	IX_RewardCampaignReferralId	NC	3.6 GB	41K	Drop candidate
SentimentAnalysisTopicReviewMapping	PK (clustered)	CL	1.4 GB	11K	Table itself unused
VisionPropertyReviewMedia	PK (clustered)	CL	1.3 GB	12	Table itself unused
Unsubscribe	NC_..Shop_Id_Source	NC	885 MB	8.7K	Drop candidate
Unsubscribe	IX_Shop_Id	NC	678 MB	8.7K	Drop candidate
ReviewEmailLinkClicks	PK (clustered)	CL	473 MB	0	Table itself unused
temp_TemplateS3Fix	PK (clustered)	CL	167 MB	0	Leftover temp table — drop entirely

Near-Zero-Read Indexes (high write cost)

These indexes have almost no reads but millions of writes:

Table	Index	Reads	Writes	Size
QueueReviewEmail	NonClusteredIndex-status-20220210-200000	2	36M	~18 GB
QueueReviewEmail	IX_Shop_DateScheduled_State	5	36M	—
QueueReviewEmail	NonClusteredIndex-orderNumber-20220211-171002	5	1.8M	~20 GB
ReviewEmailRecommendations	IX_queueReviewEmailId	1	1.2M	—

Summary of Reclaimable Space

Category	Reclaimable
Bloat in ReviewEmailProducts (heap, no clustered index)	~370 GB
Bloat in ReviewEmailRecommendations	~169 GB
Write-only nonclustered indexes	~37 GB
Near-zero-read QueueReviewEmail indexes	~38 GB
Unused/temp tables	~3 GB
Total potential savings	~617 GB

Recommended Actions

1. ReviewEmailProducts — Add a clustered index or ALTER TABLE REBUILD to reclaim ~370 GB. Being a heap is the root cause.
2. ReviewEmailRecommendations — ALTER INDEX ... REBUILD to reclaim ~169 GB.
3. Drop write-only NC indexes — The 3 large ones (QueueReviewEmailLog, Customer, RewardCampaignActivity) save ~37 GB and reduce write overhead.
4. Drop temp_TemplateS3Fix — Leftover temp table, 167 MB.
5. Evaluate near-zero-read QueueReviewEmail indexes — ~38 GB of indexes with <10 reads total.

Caveats

• sys.dm_db_index_usage_stats resets on SQL Server restart. Check SELECT sqlserver_start_time FROM sys.dm_os_sys_info to verify how long stats have been accumulating.
• Large rebuilds need a maintenance window or REBUILD WITH (ONLINE = ON) (Enterprise edition only).