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;
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!