I was experiencing some performance issues with a query yesterday and upon further investigation, I noticed what I believe is odd behavior with a clustered columnstore index that I'm trying to get to the bottom of.
The table is
CREATE TABLE [dbo].[NetworkVisits]( [SiteId] [int] NOT NULL, [AccountId] [int] NOT NULL, [CreationDate] [date] NOT NULL, [UserHistoryId] [int] NOT NULL )
with the index:
CREATE CLUSTERED COLUMNSTORE INDEX [CCI_NetworkVisits] ON [dbo].[NetworkVisits] WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0) ON [PRIMARY]
The table currently has 1.3 Billion rows in it and we are constantly inserting new rows to it. When I say constantly, I mean all the time. It's a steady stream of inserting one row at a time to the table.
Insert Into NetworkVisits (SiteId, AccountId, CreationDate, UserHistoryId) Values (@SiteId, @AccountId, @CreationDate, @UserHistoryId)
Execution plan here
I also have a scheduled job that runs every 4 hours to delete duplicate rows from the table. The query is:
With NetworkVisitsRows As (Select SiteId, UserHistoryId, Row_Number() Over (Partition By SiteId, UserHistoryId Order By CreationDate Asc) RowNum From NetworkVisits Where CreationDate > GETUTCDATE() - 30) DELETE FROM NetworkVisitsRows WHERE RowNum > 1 Option (MaxDop 48)
The execution plan has been pasted here.
While digging into the issue, I noticed that the
NetworkVisits table had roughly 2000 rowgroups in it, with about 800 of them being in an open state and no where near the max allowed (1048576). Here is a small sample of what I was seeing:
I ran a reorganize on the index, which compressed all but 1 rowgroup, but this morning I checked again and we again have multiple open rowgroups - the one that was created yesterday after the reorganize, then 3 others each created roughly around the time the deletion job ran:
TableName IndexName type_desc state_desc total_rows deleted_rows created_time NetworkVisits CCI_NetworkVisits CLUSTERED COLUMNSTORE OPEN 36754 0 2019-12-18 18:30:54.217 NetworkVisits CCI_NetworkVisits CLUSTERED COLUMNSTORE OPEN 172103 0 2019-12-18 20:02:06.547 NetworkVisits CCI_NetworkVisits CLUSTERED COLUMNSTORE OPEN 132628 0 2019-12-19 04:03:10.713 NetworkVisits CCI_NetworkVisits CLUSTERED COLUMNSTORE OPEN 397718 0 2019-12-19 08:02:13.063
I'm trying to determine what possibly could be causing this to create new rowgroups instead of using the existing one.
Is it possibly memory pressure or contention between the insert and the delete? Is this behavior documented anywhere?
We're running SQL Server 2017 CU 16 Enterprise Edition on this server.
INSERT is MAXDOP 0, the
DELETE is MAXDOP 48. The only closed rowgroups are the ones from the initial
BULKLOAD and then the
REORG_FORCED that I did yesterday, so the trim reasons in
NO_TRIM respectively. There are no closed rowgroups beyond those. There are no updates being run against this table. We average about 520 executions per minute on the insert statement. There is no partitioning on the table.
I am aware of trickle inserts. We do the same thing elsewhere and are not experiencing the same issue with multiple open row groups. Our suspicion is it has to do with the delete. Each newly created row group is around the time of the scheduled deletion job. There are only two delta stores showing deleted rows. We don't actually delete a lot of data from this table, for example during one execution yesterday it deleted 266 rows.