SQL Server 2019 CXCONSUMER wait type?

Question

Hello, we have stored proc that has two UPDATE statements within and can range from 5 mins to maybe about 1 hour to complete.

Recently this was hanging (SUSPENDED state and had CXCONSUMER wait type) throughout the entire day yesterday near 24 hours and did not complete.

How can we resolve this?

Thanks in advance.

Answer 1

CXCONSUMER is related to parallelism. There used to be only CXPACKET, but they split that in two.

The place to go if you are curious about a specific wait type is Paul Randall's Waitopedia: http://www.sqlskills.com/blogs/paul/announcing-the-comprehensive-sql-server-wait-types-and-latch-classes-library/ The specific link for CXCONSUMER is https://www.sqlskills.com/help/waits/cxconsumer/ and Paul Randall says that it is mainly benign and adds "but see below".

As for your problem, we had a somewhat similar problem today. A big helper in this case was my beta_lockinfo. It shows the current activity, the locks, the current statement etc. And the query plans. And if your instance has the lightweight profiling framework enabled (always on in SQL 2019+ and by trace flag in SQL 2016/7), the query plans have the actual values so far. By looking this plan, I could see how may rows there were to process, and how many there were to be processed. The conclusion was that the query was unacceptable slow for recurring use, but it would complete in reasonable time, so there was no reason to stop it and deploy fix straight into production. On the other had we seen that it had processed just a few per cent of the rows when we first looked at it, we would have had to kill it.

So beta_lockinfo could be a start for you. You probably need to look at the query plan, do get idea where the problems are, and from this you can define indexes, or rewrite problematic parts of the query which prevents efficient index usage.

Of course, it may also be a plain blocking situation. But beta_lockinfo will tell you that directly, if so.