Sql-server – Why isn’t space deallocated after deleting all rows from a clustered table

sql serversql-server-2016

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

UPDATE  tc
SET tc.Status   = 0
FROM    #RC_Task_Calc   AS tc
CROSS
APPLY   (   SELECT  TOP 1
            _tt.CalcStamp
        ,   _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
        ORDER
        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,
 CONSTRAINT [ITableNameId] PRIMARY KEY NONCLUSTERED 
(
    [Id] ASC
),
 CONSTRAINT [ITableNameIdentity] UNIQUE CLUSTERED 
(
    [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: )

Best Answer

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:

SELECT * 
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.

Related Question