Arioch Arioch
NReilingh NReilingh
Joe Obbish Joe Obbish
Hannah Hannah
rahularyansharma rahularyansharma
searle1986 searle1986
Anonymous 1800 Anonymous 1800
artaxerxe artaxerxe
TiffanyP TiffanyP
4 The Clustered Index Scan shows 423,723 logical reads to return the first row, taking 1926 ms: [![NUTS](https://i.stack.imgur.com/1AGv4.png)][1] This seems rather a lot to locate the first row in index order. Most likely your **ghost cleanup** task is running a long way behind, or has stopped. You should check the `ghost_record_count` for the clustered index in [`sys.dm_db_index_physical_stats`](https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql) and monitor changes over time. The **ordered scan** from the end of the index that is seeing constant delete activity has to scan over an awful lot of ghosted records before it finds the first 'alive' row to return. This explains the extra logical reads. A seek down the b-tree to the lowest value of the index will encounter much fewer ghosted records. Another performance-affecting factor is that the scan itself becomes responsible for removing the ghost records as mentioned in [Inside the Storage Engine: Ghost cleanup in depth](https://www.sqlskills.com/blogs/paul/inside-the-storage-engine-ghost-cleanup-in-depth/) by Paul Randal. You should check that trace flag 661 (disable ghost cleanup) is not active. ### Solutions * You may find that running [sp_clean_db_free_space][2] provides relief. * Changing the process that deletes rows from that end of the index to use a `PAGLOCK` hint [would enable ghost cleanup on the spot](https://blogs.msdn.microsoft.com/sqljourney/2012/07/27/an-in-depth-look-at-ghost-records-in-sql-server), which could well solve the problem as well. If the ghost cleanup process has stopped completely, the most effective solution is normally to restart the SQL Server instance. You should also ensure that the SQL Server is running one of the latest Cumulative Updates. There have been many ghost cleanup bugs over the years. In your specific case: >It turned out the problem was caused by another test database on the same server. That test database was restored with "data loss", and is corrupt. Surprisingly, the ghost cleanup process apparently was stuck in that database. Once we deleted that corrupted database from SMSS, the problem resolved by itself (took a long time and might have caused DB to lockup for a short while). [1](https://i.stack.imgur.com/1AGv4.png): https://i.stack.imgur.com/1AGv4.png [2](https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql): https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql [3](https://www.sqlskills.com/blogs/paul/inside-the-storage-engine-ghost-cleanup-in-depth/): https://www.sqlskills.com/blogs/paul/inside-the-storage-engine-ghost-cleanup-in-depth/ [4](https://blogs.msdn.microsoft.com/sqljourney/2012/07/27/an-in-depth-look-at-ghost-records-in-sql-server): https://blogs.msdn.microsoft.com/sqljourney/2012/07/27/an-in-depth-look-at-ghost-records-in-sql-server [5](https://support.microsoft.com/en-nz/help/920093/tuning-options-for-sql-server-when-running-in-high-performance-workloa): https://support.microsoft.com/en-nz/help/920093/tuning-options-for-sql-server-when-running-in-high-performance-workloa
Paul White
anoldmaninthesea anoldmaninthesea

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.