Update Intersect hangs
jamie marshall (imported from SE) 
We have this really weird behavior we just started experiencing with our update intersect statements.  These were working fine, but now we are ingesting a pretty wide data source in terms of columns and slows gradually till hanging indefinitely. 

The query below will get longer and longer as we add data in sets of like 20K rows (which is very small), and hang around 70K rows.  There are no indexes being used, we drop them before we ingest the data.

Here is the statement:

    UPDATE Staging.[TdDailyPerformance]
    SET [SYS_OPERATION] = 'U'
    FROM (
        SELECT [HashCode]           
        FROM Staging.[TdDailyPerformance]
        INTERSECT
        SELECT [HashCode]
        FROM [IdMatch].[TdDailyPerformance]
    ) AS A

Execution plan:

[![ExecutionPlan_1][1]][1]

[![enter image description here][2]][2]

Now this query works in many other places on our server but not here.  The interesting thing is that no matter whether the `INTERSECT` returns rows, the query hangs forever (I test this by running the intersect independently - it takes less than 2ms.).

It seems like according to SQL it shouldn't work, but it does.  If the `HasCode` in the `Staging` table exists already in the `IdMatch` table it updates the `[SYS_OPERATION]` of the `Staging` table to be "U".  We use this several places and it only started failing on this one dataset recently.

Any ideas what might be causing this?

No blocking, as far as we can see.  The only wait types on the transaction are `CXPACKET` which is what I would expect from the QP.  I've queried `sp_who2`, looked at All Transactions and Activity Monitor to identify blocks and have found nothing.  I haven't traced.

Mostly, our tests work out now, so that it has 0 rows when it hangs. But we have verified that it also hangs with 1-100 rows that `INTERSECT`.

`IdMatch` has no `HashCodes` that exist in `Staging`, but both tables have around 70K rows at time of hang. So to be clear, both tables have around 70K, but the intersection on `HashCode` is 0 rows.

We've tested with indexes. We get overall poor performance before we hit the query in question. The index just fragments too quickly to be helpful.

### Table definitions

Staging.TdDailyPerformance

    CREATE TABLE [Staging].[TdDailyPerformance]
    (
    	[ID] INT NOT NULL,
    	[SYS_OPERATION] CHAR(8) NULL,
    	[HashCode] BINARY(65) NULL,
    	[Ad Environment] NVARCHAR(1024) NULL,
    	[Ad Format] NVARCHAR(10) NULL,
    	[Ad Group] NVARCHAR(1024) NULL,
    	[Ad Group ID] NVARCHAR(32) NULL,
    	[Ad Server Creative Placement ID] NVARCHAR(1024) NULL,
    	[Ad Server Name] NVARCHAR(1024) NULL,
    	[Advertiser] NVARCHAR(1024) NULL,
    	[Advertiser Currency Code] NVARCHAR(32) NULL,
    	[Advertiser ID] NVARCHAR(32) NULL,
    	[App] NVARCHAR(1024) NULL,
    	[Audience] NVARCHAR(1024) NULL,
    	[Audience ID] NVARCHAR(32) NULL,
    	[Browser] NVARCHAR(30) NULL,
    	[Campaign] NVARCHAR(1024) NULL,
    	[Campaign ID] NVARCHAR(32) NULL,
    	[Carrier ID] INT NULL,
    	[Carrier Name] NVARCHAR(1024) NULL,
    	[Category ID] NVARCHAR(1024) NULL,
    	[Category Name] NVARCHAR(1024) NULL,
    	[City] NVARCHAR(50) NULL,
    	[Country] NVARCHAR(50) NULL,
    	[Creative] NVARCHAR(1024) NULL,
    	[Creative Duration In Seconds] INT NULL,
    	[Creative ID] NVARCHAR(32) NULL,
    	[Date] NVARCHAR(1024) NULL,
    	[Deal ID] NVARCHAR(128) NULL,
    	[Device Make] NVARCHAR(32) NULL,
    	[Device Type] NVARCHAR(15) NULL,
    	[Fold] NVARCHAR(128) NULL,
    	[Language] NVARCHAR(1024) NULL,
    	[Market Type] NVARCHAR(32) NULL,
    	[Media Type] NVARCHAR(32) NULL,
    	[Metro] NVARCHAR(128) NULL,
    	[Metro Code] INT NULL,
    	[Operating System] NVARCHAR(32) NULL,
    	[Operating System Family] NVARCHAR(1024) NULL,
    	[Partner ID] NVARCHAR(32) NULL,
    	[Partner Name] NVARCHAR(32) NULL,
    	[Recency Group] NVARCHAR(32) NULL,
    	[Recency Group End In Minutes] INT NULL,
    	[Recency Group Start In Minutes] INT NULL,
    	[Region] NVARCHAR(128) NULL,
    	[Site] NVARCHAR(128) NULL,
    	[Site List Name] NVARCHAR(1024) NULL,
    	[Site/Category Bid Factor] NVARCHAR(1024) NULL,
    	[Supply Vendor] NVARCHAR(25) NULL,
    	[Supply Vendor Publisher Id] NVARCHAR(64) NULL,
    	[Timezone] NVARCHAR(1024) NULL,
    	[Video Playback Type] NVARCHAR(32) NULL,
    	[Whitelist Site] NVARCHAR(32) NULL,
    	[Partner Currency Code] NVARCHAR(1024) NULL,
    	[Additional Fee Cost (Adv Currency)] DECIMAL(37,15) NULL,
    	[Additional Fee Cost (Partner Currency)] DECIMAL(37,15) NULL,
    	[Additional Fee Cost (USD)] DECIMAL(37,15) NULL,
    	[Advertiser Cost (Adv Currency)] DECIMAL(37,15) NULL,
    	[Advertiser Cost (Partner Currency)] DECIMAL(37,15) NULL,
    	[Advertiser Cost (USD)] DECIMAL(37,15) NULL,
    	[All Last Click + View Conversions] INT NULL,
    	[Bids] INT NULL,
    	[Clicks] INT NULL,
    	[Companion Clicks] INT NULL,
    	[Companion Impressions] INT NULL,
    	[Data Cost (Adv Currency)] DECIMAL(37,15) NULL,
    	[Data Cost (Partner Currency)] DECIMAL(37,15) NULL,
    	[Data Cost (USD)] DECIMAL(37,15) NULL,
    	[Fee Features Cost (Adv Currency)] DECIMAL(37,15) NULL,
    	[Fee Features Cost (Partner Currency)] DECIMAL(37,15) NULL,
    	[Fee Features Cost (USD)] DECIMAL(37,15) NULL,
    	[IAS Display Fully In View 0 Seconds] INT NULL,
    	[IAS Display Fully In View 1 Second] INT NULL,
    	[IAS Display Fully In View 15 Seconds] INT NULL,
    	[IAS Display Fully In View 5 Seconds] INT NULL,
    	[IAS Display In View 1 Second] INT NULL,
    	[IAS Display In View 15 Seconds] INT NULL,
    	[IAS Display In View 5 Seconds] INT NULL,
    	[IAS High Risk Impression Count] INT NULL,
    	[IAS Low Risk Impression Count] INT NULL,
    	[IAS Moderate Risk Impression Count] INT NULL,
    	[IAS Non GVIT Impression Count] INT NULL,
    	[IAS Suspicious Activity] INT NULL,
    	[IAS Total Impression Count] INT NULL,
    	[IAS Very High Risk Impression Count] INT NULL,
    	[IAS Video 25% Complete] INT NULL,
    	[IAS Video 50% Complete] INT NULL,
    	[IAS Video 75% Complete] INT NULL,
    	[IAS Video Completed Views] INT NULL,
    	[IAS Video Muted] INT NULL,
    	[IAS Video Viewable 25% Complete] INT NULL,
    	[IAS Video Viewable 50% Complete] INT NULL,
    	[IAS Video Viewable 75% Complete] INT NULL,
    	[IAS Video Viewable Completed Views] INT NULL,
    	[Impressions] INT NULL,
    	[In-banner Player Impressions] INT NULL,
    	[Large Player Impressions] INT NULL,
    	[Media Cost (Adv Currency)] DECIMAL(37,15) NULL,
    	[Media Cost (Partner Currency)] DECIMAL(37,15) NULL,
    	[Media Cost (USD)] DECIMAL(37,15) NULL,
    	[Medium Player Impressions] INT NULL,
    	[MOAT Display In View Time 10 Seconds] INT NULL,
    	[MOAT Display In View Time 15 Seconds] INT NULL,
    	[MOAT Display In View Time 30 Seconds] INT NULL,
    	[MOAT Display In View Time 5 Seconds] INT NULL,
    	[MOAT Display On Screen] INT NULL,
    	[MOAT Display Universal Interaction] INT NULL,
    	[MOAT Display Universal Interaction Time 10 Seconds] INT NULL,
    	[MOAT Display Universal Interaction Time 15 Seconds] INT NULL,
    	[MOAT Display Universal Interaction Time 30 Seconds] INT NULL,
    	[MOAT Display Universal Interaction Time 5 Seconds] INT NULL,
    	[MOAT Video Audible and Visible on Complete] INT NULL,
    	[MOAT Video In View Time 3 Seconds] INT NULL,
    	[MOAT Video In View Time 5 Seconds] INT NULL,
    	[MOAT Video Visible on Complete] INT NULL,
    	[Non-USD Currency Cost (Adv Currency)] DECIMAL(37,15) NULL,
    	[Non-USD Currency Cost (Partner Currency)] DECIMAL(37,15) NULL,
    	[Non-USD Currency Cost (USD)] DECIMAL(37,15) NULL,
    	[Partner Cost (Adv Currency)] DECIMAL(37,15) NULL,
    	[Partner Cost (Partner Currency)] DECIMAL(37,15) NULL,
    	[Partner Cost (USD)] DECIMAL(37,15) NULL,
    	[Player 25% Complete] INT NULL,
    	[Player 50% Complete] INT NULL,
    	[Player 75% Complete] INT NULL,
    	[Player Audible Event] INT NULL,
    	[Player Close] INT NULL,
    	[Player Collapse] INT NULL,
    	[Player Completed Views] INT NULL,
    	[Player Engaged Views] INT NULL,
    	[Player Errors] INT NULL,
    	[Player Expansion] INT NULL,
    	[Player Full Screen] INT NULL,
    	[Player Invitation Accept] INT NULL,
    	[Player Mute] INT NULL,
    	[Player Pause] INT NULL,
    	[Player Playing Event] INT NULL,
    	[Player Resume] INT NULL,
    	[Player Rewind] INT NULL,
    	[Player Skip] INT NULL,
    	[Player Starts] INT NULL,
    	[Player Total Playing Seconds] INT NULL,
    	[Player Unmute] INT NULL,
    	[Player Views] INT NULL,
    	[Predictive Clearing Savings (Adv Currency)] DECIMAL(37,15) NULL,
    	[Predictive Clearing Savings (Partner Currency)] DECIMAL(37,15) NULL,
    	[Predictive Clearing Savings (USD)] DECIMAL(37,15) NULL,
    	[Profit (Adv Currency)] DECIMAL(37,15) NULL,
    	[Profit (Partner Currency)] DECIMAL(37,15) NULL,
    	[Profit (USD)] DECIMAL(37,15) NULL,
    	[Sampled Tracked Impressions] INT NULL,
    	[Sampled Viewed Impressions] INT NULL,
    	[Small Player Impressions] INT NULL,
    	[Total Audible Seconds] INT NULL,
    	[Total Bid Amount (Adv Currency)] DECIMAL(37,15) NULL,
    	[Total Bid Amount (Partner Currency)] DECIMAL(37,15) NULL,
    	[Total Bid Amount (USD)] DECIMAL(37,15) NULL,
    	[Total Custom CPA Conversions] INT NULL,
    	[Total Seconds In View] INT NULL,
    	[TTD Cost (Adv Currency)] DECIMAL(37,15) NULL,
    	[TTD Cost (Partner Currency)] DECIMAL(37,15) NULL,
    	[TTD Cost (USD)] DECIMAL(37,15) NULL,
    	[TTD Margin (Adv Currency)] DECIMAL(37,15) NULL,
    	[TTD Margin (Partner Currency)] DECIMAL(37,15) NULL,
    	[TTD Margin (USD)] DECIMAL(37,15) NULL,
    	[Video In View Event] INT NULL,
    	[White Ops SIVT Bids Avoided] INT NULL,
    
    	[CreatedOn] DATETIME2 NULL CONSTRAINT df_Staging_TdDailyPerformance_CreatedOn DEFAULT SYSUTCDATETIME(),
    	[ModifiedOn] DATETIME2 NULL CONSTRAINT df_Staging_TdDailyPerformance_ModifiedOn DEFAULT SYSUTCDATETIME(),
    	[Retired] BIT NULL CONSTRAINT df_Staging_TdDailyPerformance_Retired DEFAULT 0
    )

IdMatch.TdDailyPerformance

    CREATE TABLE [IdMatch].[TdDailyPerformance]
    (
    	[ID] INT IDENTITY(1,1) NOT NULL,
    	[HashCode] BINARY(65) NOT NULL,
    	[Ad Environment] NVARCHAR(1024) NULL,
    	[Ad Format] NVARCHAR(10) NULL,
    	[Ad Group] NVARCHAR(1024) NULL,
    	[Ad Group ID] NVARCHAR(32) NULL,
    	[Ad Group Integer ID] INT NULL,
    	[Ad Server Creative Placement ID] NVARCHAR(1024) NULL,
    	[Ad Server Name] NVARCHAR(1024) NULL,
    	[Advertiser] NVARCHAR(1024) NULL,
    	[Advertiser Currency Code] NVARCHAR(1024) NULL,
    	[Advertiser ID] NVARCHAR(32) NULL,
    	[App] NVARCHAR(1024) NULL,
    	[Browser] NVARCHAR(30) NULL,
    	[Campaign] NVARCHAR(1024) NULL,
    	[Campaign ID] NVARCHAR(32) NULL,
    	[Carrier ID] INT NULL,
    	[Carrier Name] NVARCHAR(1024) NULL,
    	[Category ID] NVARCHAR(1024) NULL,
    	[Category Name] NVARCHAR(1024) NULL,
    	[City] NVARCHAR(50) NULL,
    	[Country] NVARCHAR(50) NULL,
    	[Creative] NVARCHAR(1024) NULL,
    	[Creative Duration In Seconds] INT NULL,
    	[Creative ID] NVARCHAR(32) NULL,
    	[Date] NVARCHAR(1024) NULL,
    	[Device Make] NVARCHAR(32) NULL,
    	[Device Type] NVARCHAR(15) NULL,
    	[Fold] NVARCHAR(128) NULL,
    	[Language] NVARCHAR(1024) NULL,
    	[Market Type] NVARCHAR(32) NULL,
    	[Media Type] NVARCHAR(32) NULL,
    	[Metro] NVARCHAR(128) NULL,
    	[Metro Code] INT NULL,
    	[Operating System] NVARCHAR(32) NULL,
    	[Operating System Family] NVARCHAR(1024) NULL,
    	[Partner ID] NVARCHAR(32) NULL,
    	[Partner Name] NVARCHAR(32) NULL,
    	[Region] NVARCHAR(128) NULL,
    	[Site] NVARCHAR(128) NULL,
    	[Site List Name] NVARCHAR(1024) NULL,
    	[Site/Category Bid Factor] NVARCHAR(1024) NULL,
    	[Supply Vendor] NVARCHAR(25) NULL,
    	[Supply Vendor Integer ID] INT NULL,
    	[Supply Vendor Publisher Id] NVARCHAR(64) NULL,
    	[Timezone] NVARCHAR(1024) NULL,
    	[Video Playback Type] NVARCHAR(32) NULL,
    	[Whitelist Site] NVARCHAR(32) NULL,
    	[CreatedOn] DATETIME2 NOT NULL CONSTRAINT df_IdMatch_TdDailyPerformance_CreatedOn DEFAULT SYSUTCDATETIME(),
    	[ModifiedOn] DATETIME2 NOT NULL CONSTRAINT df_IdMatch_TdDailyPerformance_ModifiedOn DEFAULT SYSUTCDATETIME(),
    	[Retired] BIT NOT NULL CONSTRAINT df_IdMatch_TdDailyPerformance_Retired DEFAULT 0
    )
    GO


  [1]: https://i.stack.imgur.com/UhbMw.png
  [2]: https://i.stack.imgur.com/HuavC.png
Top Answer
Paul White (imported from SE) 
You've written an accidental cross join:

```
UPDATE Staging.[TdDailyPerformance]
SET [SYS_OPERATION] = 'U'
FROM (
    SELECT [HashCode]           
    FROM Staging.[TdDailyPerformance]
    INTERSECT
    SELECT [HashCode]
    FROM [IdMatch].[TdDailyPerformance]
) AS A
```

That is quite a [common error][1] when using the `FROM` extension of T-SQL `UPDATE`.

The expectation is that both references to `Staging.[TdDailyPerformance]` identify the same instance of the object, but that is not how it works.

The statement above actually specifies that **all** rows of the target should be updated if the derived table `A` produces **any** rows at all. The two instances of `Staging.[TdDailyPerformance]` are bound separately.

The query appears to hang for the reasons I discuss separately at the end of this answer.

The safest way to write this sort of update is to alias the tables and **always** use an alias as the target. (You should also write the query deterministically such that each target row can only be updated at most once.)

If we try to follow the alias rule with the statement above:

```
UPDATE S_TDP
SET SYS_OPERATION = 'U'
FROM
(
    SELECT S_TDP.HashCode
    FROM Staging.TdDailyPerformance AS S_TDP
    INTERSECT
    SELECT I_TDP.HashCode
    FROM IdMatch.TdDailyPerformance AS I_TDP
) AS A;
```

We get a binding error, which alerts us to the mistake:

>Msg 208, Level 16, State 1, Line xxx  
Invalid object name 'S_TDP'.

You're already aware of the working alternatives, so I won't labour that point, except to mention that you should probably look at combining the two updates.

## Plan Analysis

I don't know how interested you are in the execution plan for the incorrect update statement, but just in case, here is a brief analysis of the serial version of the plan.

The portion of the plan below the *Top* is concerned with finding the first row (if any) resulting from the `INTERSECT`:

[![Top Subtree][2]][2]

The [*Flow Distinct*][3] is a row-goal optimization that aims to produce the first distinct hash code value quickly. The *Nested Loops Join* is chosen because the optimizer only expects the scan the inner side heap table once to find a matching hash code.

This strategy is exposed when there is **no match** on hash code. In that case, the inner side will be fully scanned for every row on the outer side - 70,000 full scans in total. This might take a while. You can test the effect of removing the [row goal][4] by using [documented trace flag 4138][5] e.g. via a query hint `OPTION (QUERYTRACEON 4138)`. The update statement will still be incorrect, but at least it won't appear to hang.

You don't see this problem when running the `INTERSECT` on its own because the row goal is introduced (with the Top) by the optimizer as it searches for a reasonable plan. You can simulate it with a query like:

```
SELECT TOP (1) 1
FROM 
(
    SELECT S_TDP.HashCode
    FROM Staging.TdDailyPerformance AS S_TDP
    INTERSECT
    SELECT I_TDP.HashCode
    FROM IdMatch.TdDailyPerformance AS I_TDP
) AS A
```

Or:

```
SELECT DISTINCT 
    TDP.HashCode
FROM Staging.TdDailyPerformance AS TDP
CROSS JOIN 
(
    SELECT [HashCode]           
    FROM Staging.[TdDailyPerformance]
    INTERSECT
    SELECT [HashCode]
    FROM [IdMatch].[TdDailyPerformance]
) AS A;

```
 
The remainder of the plan updates the whole target table if a row was found:

[![Update][6]][6]

The *Nested Loops Join* has no join predicate. The *Sort* and *Stream Aggregate* group records by heap RID. This is pointless but it is part of the general logic used to collapse plans that might update the same target row multiple times to a single (non-deterministic) update per-row.


  [1]: https://sqlserverfast.com/blog/hugo/2008/03/lets-deprecate-update-from/
  [2]: https://i.stack.imgur.com/IKx4J.png
  [3]: https://www.sql.kiwi/2010/08/row-goals-and-grouping.html
  [4]: https://www.sql.kiwi/2010/08/inside-the-optimiser-row-goals-in-depth.html
  [5]: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql
  [6]: https://i.stack.imgur.com/VE92E.png
comments
transcript

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.