Index Usage - Deleted

Question

Index Usage - Deleted

SQLSACT

SSC-Insane

Points: 21838
More actions
July 31, 2012 at 4:48 am

#278762

Hi All
I'm using the below script to track index usage on a particular table.
Why does the leaf_delete_count show zero? I deleted some records from the table but it still shows zero.
Am I missing something here?
SELECT SO.name,
SI.name IndexName
,SI.type_desc IndexType
,IPS.avg_fragmentation_in_percent
,IPS.index_level
,IOS.range_scan_count
,IUS.user_lookups
,IUS.user_scans
,IUS.user_seeks
,IUS.index_id
,IOS.leaf_delete_count
,IOS.leaf_insert_count
,IOS.leaf_update_count
FROM sys.indexes SI
INNER JOIN sys.objects SO
ON SO.object_id = SI.object_id
INNER JOIN sys.dm_db_index_physical_stats (DB_ID('dbname'), OBJECT_ID('tablename'), NULL , NULL, N'LIMITED')IPS
ON IPS.object_id = SI.object_id
AND IPS.index_id = SI.index_id
inner join sys.dm_db_index_operational_stats (DB_ID('dbname'), OBJECT_ID('tablename'), NULL , NULL) IOS
on IOS.object_id = SO.object_id
and IOS.index_id = SI.index_id
inner join sys.dm_db_index_usage_stats IUS
on IUS.object_id = SO.object_id
and IUS.index_id = SI.index_id
where SI.name is not null
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

pooyan_pdm Hall of Fame Points: 3007 More actions · Answer 1

To increase the performance of DELETE operation the records are not deleted immediately, instead they are marked as ghosted . A background process called ghost cleanup task later removes the deleted records physically from the index page.you should look for 'leaf_ghost_count' column in the 'sys.dm_db_index_operational_stats' for number of logically deleted records.

Pooyan

SQLSACT SSC-Insane Points: 21838 More actions · Answer 2

pooyan_pdm (7/31/2012)
To increase the performance of DELETE operation the records are not deleted immediately, instead they are marked as ghosted . A background process called ghost cleanup task later removes the deleted records physically from the index page.you should look for 'leaf_ghost_count' column in the 'sys.dm_db_index_operational_stats' for number of logically deleted records.

How long after the delete does the Ghost Cleanup happen?

Thanks

pooyan_pdm Hall of Fame Points: 3007 More actions · Answer 3

When a record is marked as ghost in an index page , it is reflected in the header of this page you can check it by running dbcc page m_ghostreccount . During the next time the page is scanned the ghost clean up task removes the deleted record from the slot array of that page but the record is never actually deleted . It is not accessable anymore instead.

Pooyan

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

SQLSACT (7/31/2012)
pooyan_pdm (7/31/2012)
To increase the performance of DELETE operation the records are not deleted immediately, instead they are marked as ghosted . A background process called ghost cleanup task later removes the deleted records physically from the index page.you should look for 'leaf_ghost_count' column in the 'sys.dm_db_index_operational_stats' for number of logically deleted records.
How long after the delete does the Ghost Cleanup happen?
Thanks

There is no way to predict when it will be physcially cleaned up. The ghost cleanup thread wakes up every 5 seconds looking for things to clean, but it will not be guaranteed to cleanup all ghosted records each time it wakes up. The algorithm is hidden from us and it will only do enough work to try and keep up while balancing overall system performance.

Deep dive: Inside the Storage Engine: Ghost cleanup in depth

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

SQLSACT SSC-Insane Points: 21838 More actions · Answer 5

opc.three (8/1/2012)
SQLSACT (7/31/2012)
pooyan_pdm (7/31/2012)
To increase the performance of DELETE operation the records are not deleted immediately, instead they are marked as ghosted . A background process called ghost cleanup task later removes the deleted records physically from the index page.you should look for 'leaf_ghost_count' column in the 'sys.dm_db_index_operational_stats' for number of logically deleted records.
How long after the delete does the Ghost Cleanup happen?
Thanks
There is no way to predict when it will be physcially cleaned up. The ghost cleanup thread wakes up every 5 seconds looking for things to clean, but it will not be guaranteed to cleanup all ghosted records each time it wakes up. The algorithm is hidden from us and it will only do enough work to try and keep up while balancing overall system performance.
Deep dive: Inside the Storage Engine: Ghost cleanup in depth

Thanks for this