Today one of our queries has gone crazy. It was hanging for more than an hour before I killed the process.

SET tc.Status   = 0
FROM    #RC_Task_Calc   AS tc
        ,   _tt.TrDateInto
        FROM    dbo.TableName   AS _tt
        WHERE   _tt.TableId = tc.TableId
        AND _tt.EventKind   = tc.EventKind
        AND _tt.DayDate <= @DayDate
        AND _tt.Status  = 1
        BY  _tt.DayDate DESC
    )   AS tt
WHERE   tc.CalcStamp    = tt.CalcStamp
AND tc.TrDateInto   = tt.TrDateInto

It was expected to perform about 600k loops to dbo.TableName which had no rows. But something went wrong.

CREATE TABLE [dbo].[TableName](
    [Id] [dbo].[TBigInt] IDENTITY(1,1) NOT NULL,
    [TableId] [dbo].[TRowId] NOT NULL,
    [EventKind] [dbo].[TRowId] NOT NULL,
    [DayDate] [dbo].[TDate] NOT NULL,
    [CalcStamp] [varbinary](8) NULL,
    [TrDateInto] [dbo].[TDate] NULL,
    [Flag] [dbo].[TFlag] NOT NULL,
    [Status] [dbo].[TTinyInt] NOT NULL,
    [Stamp] [timestamp] NOT NULL,
    [CreatorId] [dbo].[TRowId] NOT NULL,
    [Created] [dbo].[TDateTime] NOT NULL,
    [ChangerId] [dbo].[TRowId] NOT NULL,
    [Changed] [dbo].[TDateTime] NOT NULL,
    [Id] ASC
    [TableId] ASC,
    [EventKind] ASC,
    [DayDate] ASC

I saw in properties of dbo.TableName (using SSMS) such information:

Data space ~ 129 MB

Index space ~ 49 MB

Row count = 0

After rebuilding indexes on the table the query completed in less than a second

ALTER INDEX all ON [dbo].[TableName] REBUILD

I'm aware of removing all data from the table using a DELETE command yesterday. But why did I have to rebuild indexes so that the space was deallocated?

The table is clustered so it shouldn't have heaps' problems on deletion. All pages should have been empty after deletion because all rows was removed. I thought it could be linked to mixed extents, but is_mixed_page_allocation_on=0 for that database.

So could someone explain me what I missed here? Why those pages weren't deallocated? Or maybe I'm a victim of some misconception about the deletion from clustered table process.

Microsoft SQL Server 2016 (SP2-CU4) (KB4464106) - 13.0.5233.0 (X64) Nov 3 2018 00:01:54 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: )

  • Please check your table DDL, it seems to be incorrect. – Learning_DBAdmin May 15 at 11:01
  • @Learning_DBAdmin, it's here just to provide indexing information, on real data we have default constraints and created user-defined data types (TBigInt, TRowId and so on). DDL is correct, I can successfully create such a table. – Denis Rubashkin May 15 at 11:36
  • 2
    @DenisRubashkin - can you create a Minimal, Reproducible Example that shows this behavior? Also, how long between the deleting of rows and the checking of space used? My understanding is that, even with a clustered index, the deallocation of pages occurs in a background process unless the tablock hint is included. – Scott Hodgin May 15 at 13:09
  • I am still not able to reproduce the behavior on our test environment but I go on trying. There were more than 12 hours between the deletion and the update query. – Denis Rubashkin May 15 at 13:37
  • 1
    @DenisRubashkin - do you have any trace flags enabled in production? Just wondering if the Ghost Cleanup process isn't running. See information about Ghost cleanup process guide, specifically the section Disable the ghost cleanup. I don't know if you're in this situation or not - just thought I'd pass the information along. – Scott Hodgin May 15 at 15:04

I was able to reproduce described behavior. I created the same table, fill it with data and then delete all rows.

After deleting I had a look at indexes:

FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('TableName_Test'), -1, NULL, 'DETAILED')

Results (I hid columns which don't matter):

enter image description here

Thank you, Scott Hodgin and Erik Darling, you both were partly right and direct me to the right way in my researches. The issue occurs because Ghost Cleanup process can't clean the version ghost records.

The behavior is similar to one described here: Problem With Too Many version_ghost_records.

Unfortunately, I can't call exactly what is root cause of being the versioned rows still held. I have not found any long transaction on any database which could cause it.

Index rebuilding and sometimes (as told in the article linked above) server restart help to solve the issue.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.