Hmmm... interesting. I just found out that a 3rd party database on my system has this problem and the tables are NOT heaps. Multiple Clustered and Non-Clustered indexes have the same problem and none of the Clustered Indexes contain LOB columns nor any "Row Overflow" data.
Sys.dm_db_index_physical_stats does NOT catch the issue, either. It reported the largest clustered index as only having ~3GB worth of pages, virtually zero logical fragmentation, and an average page density of >99% even though the "Unused" column for sp_spaceused for the table reported more than 26GB.
Rebuilding the indexes fixed that. DBCC CLEANTABLE had no effect.
I have no idea why the pages weren't auto-magically returned to an un-allocated status after the deletes that I know have been occurring. I did, however, find this little tidbit (see link below). It does seem to match what's happening but it also seems that this was meant for SQL Server 2000 or less but the article was updated as recently as April 2018.
Like I said before, I've never seen this issue before and previously thought it was impossible.
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
When you put the right degree of spin on it, the number 3|8
is also a glyph that describes the nature of a DBAs job. 😉
How to post code problems
Create a Tally Function (fnTally)