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:

FROM sys.dm_db_index_physical_stats
WHERE OBJECT_ID = 516196889
ORDER BY avg_fragmentation_in_percent DESC;

And my results: Fragmentation query 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!

  • Here's a possible explanation: sqlskills.com/blogs/paul/… . I dealt with this a while ago where a straight SELECT * from a table with only some 6000 rows and it took minutes. Not having a heap sorted it. So, you are probably stuck with this until you create a clustered index on the table. Commented Apr 15, 2019 at 6:51
  • @TiborKaraszi this is very interesting...as is said in the article, this is not very intuitive. Never would I have thought SQL Server would allow millions of allocated pages to just sit empty indefinitely, but that does seem to be what's happening in this case.
    – Boatmarker
    Commented Apr 18, 2019 at 0:38

2 Answers 2


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

  • Great, I will rebuild the heap and see what happens. Unfortunately, clustered table is not an option here since the database structure is specified by our product, and it may be a design decision (nearly every other table in our database is clustered, so it seems likely this table was made non-clustered intentionally).
    – Boatmarker
    Commented Apr 18, 2019 at 0:10

This can also be caused by inserting rows with small LOB values, and updating to a size requiring off-row storage. And can be mitigated by setting the 'large value types out of row' table option in sp_tableoption. And if this is the case it will affect heaps and clustered index tables identically.

EG, below switching to a clustered PK doesn't change the behavior, and setting the table option forces all the LOB data off-row and leaves the IN_ROW_DATA pages full.

use tempdb

drop table if exists ht
create table ht(id int identity primary key nonclustered, a int, data varchar(max))
--exec sp_tableoption 'ht', 'large value types out of row', 1

with q as
  select top 10000 row_number() over (order by (select null)) i
  from sys.messages m, sys.messages m2
insert into ht(a,data)
select i, replicate(cast('x' as nvarchar(max)), (i%5) * 1024) 
from q 

update ht set data = replicate(cast('x' as nvarchar(max)), 20 * 1024) 


select index_id, index_type_desc, alloc_unit_type_desc, avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(db_id(), object_id('ht'),null, null, 'detailed') s
  • Thanks, that's interesting. So with this table option off, LOB data is stored in-row if the row fits, and with the option on, LOB data is always stored off-row? It's unlikely to be the cause for my issue since the rows in my table are never updated after insertion, but good to know still.
    – Boatmarker
    Commented Apr 18, 2019 at 0:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.