Skip to main content
2 of 2
added 10 characters in body
Paul White
  • 90.3k
  • 30
  • 424
  • 663

Tables that have a pattern of many inserts and deletes tend to have a problem with accumulating ghost records (a row which is marked as deleted but not actually removed, there is a later asynchronous process that is supposed to come along and clean them up - but it can fall behind).

If you run this query and post the results we should be able to see if this is what is causing your issue.

    select 
       sum(record_count) as records,
       SUM(PAGE_COUNT) AS pages,
       sum(ghost_record_count) as ghost_records,
       sum(version_ghost_record_count) as version_ghost_records
from sys.dm_db_index_physical_stats(
       db_id(), object_id('dbo.yourtable'), default, default, 'detailed')
 where index_id = 1