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: )