Forum Discussion

rcfoyn's avatar
rcfoyn
Copper Contributor
May 06, 2019

SQL Server update query based on view takes a very long time

I have a terribly simple update query:

UPDATE <schema>.<table> SET
    <column1> = a.<column1>
  , <column2> = a.<column2>
  ...
FROM
  <schema>.<table> b
INNER JOIN
  <schema>.<view> a ON a.<PK> = b.<PK>

This query executes in 1 second (!) when limiting the number of columns to update to 23! When trying to update 24 or more columns, the query ran for almost an hours, after which the "query executed successfully"! The number of rows in both the table and the view is around 110k.

 

There is no problem running neither SELECT * FROM <schema>.<view>, <schema>.<table>, nor on an INNER JOIN of both. All of those three operations takes a few seconds each (the latter takes 7 seconds).

 

It seems to have nothing to say which columns I choose to update, only the *number of columns* that I update at a time.

 

Another thing I should mention is that CPU, Memory, and disk usage remain low during that overly long execution time. In other words, nothing much seems to be going on. There is, however, a lot of CXCONSUMER waits in the Activity Monitor.

 

Finally, when I put the view into a temporary table and uses this instead, everything runs smoothly!

 

It's the first time I have encountered anything like this. Anyone got a clue what could be causing this or how I can try to resolve it??

 

I just updated to SQL2017 RTM CU14, so the build is currently 14.0.3076.1.

Windows Server 2016, Version 1607 (OS Build 14393.2906)

4 vCPU, 40 GB RAM

1 Reply

  • Ronen_Ariely's avatar
    Ronen_Ariely
    MVP
    May 23, 2019

    Good day rcfoyn,

     

    During the PASS Summit in 2017 Microsoft announced the new wait type CXCONSUMER. This type of wait used to be part of the CXPACKET wait, which was too general and hard to work with. Therefore, Microsoft split it into two types of waits. You can read more about this wait here. The Book Online have a short explanation "Occurs with parallel query plans when a consumer thread waits for a producer thread to send rows. This is a normal part of parallel query execution."

     

    Please provide queries to create the table(s) and the view (so we will have the table's structure including any index and constraint and relations) and queries to insert some sample data for the test/discussion. In addition we must get your real Execution Plan when the query run fast and the real Execution Plan when the query run slow (not image if the Execution Plan but the full XML).

     

    In the meantime,  You can try to change the setting of the MAXDOP and Cost Threshold For Parallelism (CTFP) in order to reduce the use of  parallelism, but based only on this wait is not a reason usually to do so.Anyhow, you should try and test it and check the behavior (for example first try to use MAXDOP 1 to remove any parallelism).

     

    Secondly, try to use direct query instead of using view. It's possible that your view includes many columns which are not needed in the query?

Resources