SQL Server - Extremely low avg_page_space_used_in_percent for heap table While analyzing a SQL Server 2012 database bloat issue, I found that fragmentation might be a major contributor to how much space is being taken up by certain tables, so after some Googling, I'm looking into the sys.dm_db_index_physical_stats function to try to understand what's going on.  Here's my query:

SELECT
index_id,
index_type_desc,
alloc_unit_type_desc,
avg_fragmentation_in_percent,
avg_page_space_used_in_percent,
record_count,
page_count,
min_record_size_in_bytes,
max_record_size_in_bytes,
avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats
(DB_ID(), NULL, NULL, NULL , 'SAMPLED')
WHERE OBJECT_ID = 516196889
ORDER BY avg_fragmentation_in_percent DESC;


And my results:


There are 4 non-clustered indexes on the table, so I'm guessing those are what the top 4 result rows represent.  I haven't figured out why the IN_ROW_DATA portion of the heap has such low page usage (6%).  If I'm reading the results correctly, there are more pages for this part than there are rows, but max record size is less than 8kb, so I'm trying to understand how that's even possible.  How do I find out what's causing this low page usage, and what are some things I can do to fix it?

Any help greatly appreciated!
This could be simply because of some delete or update operation removing rows from the page and up till now it was never used to fill additional rows. I do not see anything worrying here.

Go ahead and rebuild the heap to get more page fullness, but please note any nonclustered indexes (NCI) on the heap would also be rebuilt. - Shanky

Using a clustered table instead of a heap will fix likely these symptoms. - Dan Guzman