Skip to main content
added 10 characters in body
Source Link
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

from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.yourtable'), default, default, 'detailed') where index_id = 1

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

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
Source Link

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