question

Stefan-1807 avatar image
0 Votes"
Stefan-1807 asked sreejitg-8603 commented

Insert in Clustered Column Store Index deadlocked by tuple mover

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

sql-server-generalsql-server-transact-sqlsql-server-integration-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Stefan-1807, we have not get a reply from you. Did any answers could help you? If there has an answer helped, please do "Accept Answer". If not, please let us know. By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

0 Votes 0 ·
JakubK-7942 avatar image
0 Votes"
JakubK-7942 answered sreejitg-8603 commented

is it addressed by this - https://support.microsoft.com/en-us/topic/kb4506912-fix-concurrent-inserts-into-a-cci-can-cause-deadlock-under-memory-pressure-in-sql-server-2016-and-2017-3b73121c-345e-601c-61c8-b7a9ccaa96cf

it depends what you want to achieve and which is more important and how often it's happening - and is it getting more frequent

some options are
do nothing - the insert runs every hour so the data gets in eventually?
set the ssis job to retry on failure and hope that it works
increase the deadlock_priority of the oledb connection session so the insert wins

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

We receive the same deadlock in Sql 2019 RTM version. Do we have similar CU patch for Sql 2019?

Thanks,
-SreejitG

0 Votes 0 ·
CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered CarrinWu-MSFT edited

Hi @Stefan-1807,

Welcome to Microsoft Q&A!

Have you install KB4527377? This issue has been resolved by KB4527377. I suggest that please install Cumulative Update package 18 (CU18) for SQL Server 2017. And also, here has a blog might be help.


Best regards,
Carrin


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Stefan-1807 avatar image
0 Votes"
Stefan-1807 answered

Thanks for your replies @JakubK-7942 and @CarrinWu-MSFT . Sorry for late reply. I am not able to update CU right now. I ended up un-ticking the "table lock" at the oledb destination component in SSIS. That solved the problem for now.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.