Hi,
We are using SQL Server 2017 and we have a table used to save information from monitoring of some event-data (originally from xEvents).
The table has a clustered column store index.
There is a SSIS job running and doing ~250 bulk-inserts per hour (bulk)-inserting data into the table. OleDb destination component with fast load and table lock.
The number of rows inserted via the bulk-insert differs but are usually only around 1-1.000 rows. It seems like each bulk-insert results in a new compressed row group with trim_reason "BULKLOAD".
Then each night we have an indexing job that reorganize the index resulting in fewer and bigger compressed rowgroups. We also get "a lot" (~4.000) of row groups with state "TOMBSTONE". Then during the day it seems like the tuple mover is "slowly" removing the TOMBSTONE row groups.
My problem is that the bulk-insert sometimes fails due to deadlocking. Did some profiling ("Deadlock graph") where I see that each time this happen it is because of the tuple mover. The bulk-insert is choosen as deadlock victim by a process running EXEC sys.sp_cci_tuple_mover .
Any idea´s on this?
Best regards
/ Stefan