August 31, 2022 at 11:05 am
Hi,
In one of our projects we are using SQL Server 2019 Enterprise ed. 15.0.4249.2 version and we have synchronous always on high availability in all databases. There we have 5 databases. So, in one database, we have a stored procedure running everyday which includes an update on a table in a different database and this table has a clustered columnstore index. Actually, it has been working fine for a long time. 2 weeks ago, we saw that a blocking occurred when it comes to the point where the update starts on that table with a clustered columnstore index. So we checked that session, and it was a CREATE INDEX operation and was a background process in the database where that updated table is stored. So, that blocking started to happen everyday after that. That session is not a user session so we can't kill it and continue, and we can't get any more information about it. So eventually we always kill the main session because it is pointless to wait for it and as we run it once more, sometimes it gets blocked again but at most in the third try, it usually runs successfully. We also checked the tsql of the stored procedure but neither it, nor the other procedures it calls involve a create index operation on that particular table. We couldn't understand what causes this problem and because it should run everyday, our client complains everyday. I really appreciate if you have any solution or any idea about that.
September 1, 2022 at 11:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
September 1, 2022 at 4:21 pm
This may not be relevant, but I had a similar issue where I had an insert into a clustered columnstore, followed by an update. When the insert was small (not many new slowly changing facts) sometimes there would be an sa process locking the table preventing the subsequent update from happening. The sa process was not killable and had no details (SQL Server 2017).
My theory is that when the initial insert was small it was a delta store operation and the tuple mover kicked off afterwards and this blocked the next operation. When I added a wait after the insert, the blocking didn't happen again. I eventually eliminated the update, so I don't know whether my theory was correct.
Can you see the name of the index being created and is it real? Does the table have nonclustered indexes in addition to the clustered columnstore? Are you inserting before you update? I don't know how columnstore updates affect nonclustered indexes, but updates are always delta store operations, so it seems likely that index updates would be negatively affected. If you can see the name of the index and it's a real one, is it feasible to drop the index before the update? I assume it's a large table and this would take too long. If the table is partitioned, is it possible to switch out the relevant partitions(s) drop the column store, run the update, recreate columnstore and switch back in? Again, I assume that would take too long.
How big is the update? and does the size have any impact on whether the blocking occurs? If the update is large I might try updating in batches with a short delay between. This will slow it down obviously, but might be worth a try and might give you a clue as to whether the blocking process is caused by the update itself.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply