By sqlbanana

2018-07-04 20:20:53 8 Comments

I've created a varbinary hash to check changes between 2 tables.

Here is the execution plan, I'm a bit stumped on the indexing, or indeed if there is a better way of writing this.

The 2 columns in the join are the PK in the target and have a non clustered index on them in the source. The bit that bothers me is the spill to tempdb caused by the sort.


@Paul White 9 2018-07-07 04:41:16


The sort spill itself can probably be addressed by enabling trace flag 7470. See FIX: Sort operator spills to tempdb when estimated number of rows and row size are correct. This trace flag corrects an oversight in the calculation. It is quite safe to use, and in my opinion ought to be on by default. The change is protected by a trace flag simply to avoid unexpected plan changes.

That said, avoiding the sort completely would be better as Rob Farley mentions in his answer. Changing the clustering key is one way to achieve that, but it may not be the optimal choice.

SQL Server chooses not to use your nonclustered index to avoid the sort because that index does not provide the other columns potentially needed for the update. Forcing that index with a hint would produce a plan with a large number of key lookups. The high estimated cost of that explains why the optimizer prefers a sort.

An alternative approach, which the optimizer is not currently able to consider on its own, is to find the keys of rows that will be updated, then fetch the additional columns (via a lookup type of operation) just for those rows. The plan provided shows no rows being updated. If that is the common case, or at least that a small fraction of the rows qualify for update, it might be worth coding that logic explicitly.

Another issue in the execution plan is that each target update row might be associated with multiple source rows. This is why there are ANY aggregates in the Stream Aggregate operator. Given multiple matches on the join keys (and a mismatch on the hash), which row will be used for the update is non-deterministic.

If the update had been written as a MERGE, an error would be thrown when multiple source rows are encountered. It is generally best to write deterministic updates, where each target row is associated with at most one source row.


The question does not provide DDL or much background, so the following is a simple approximation where all the non-key columns are represented by a single large column, and cardinality & indexing are inferred from the plan:


CREATE TABLE dbo.dwSource
    loadkey bigint NOT NULL,
    mytableid integer NOT NULL,
    ppw_id integer NOT NULL,
    other_columns varchar(1000) NOT NULL,
    row_hash binary(20) NOT NULL,

    CONSTRAINT PK_dbo_dwSource
        PRIMARY KEY CLUSTERED (loadkey),

CREATE TABLE dbo.dwTarget
    mytableid integer NOT NULL,
    ppw_id integer NOT NULL,
    other_columns varchar(1000) NOT NULL,
    row_hash binary(20) NOT NULL,

    CONSTRAINT PK_dbo_dwTarget
        PRIMARY KEY CLUSTERED (ppw_id, mytableid)

WITH ROWCOUNT = 1295450, PAGECOUNT = 100000;

WITH ROWCOUNT = 1296390, PAGECOUNT = 100000;

Given that approximate schema (ignoring the nonclustered indexes on source and target), the current update statement is:

SET DT.other_columns = DS.other_columns
FROM dbo.dwSource AS DS
JOIN dbo.dwTarget AS DT
    ON DT.ppw_id = DS.ppw_id
    AND DT.mytableid = DS.mytableid
WHERE DS.row_hash <> DT.row_hash;


Existing plan

As mentioned, if the number of rows to be updated is relatively small, it may be worth locating the keys only as a first step. To do this optimally, we need a couple of nonclustered indexes, which may be similar to the existing ones:

-- Narrower than the clustered primary key
CREATE UNIQUE INDEX [UQ dbo.dwTarget ppw_id, mytableid (row_hash)]
ON dbo.dwTarget (ppw_id, mytableid) 
INCLUDE (row_hash);

-- Not guaranteed to be unique    
CREATE INDEX [IX dbo.dwSource ppw_id, mytableid (loadkey, row_hash)]
ON dbo.dwSource (ppw_id, mytableid) 
INCLUDE (loadkey, row_hash);

We can then write a query to locate the update keys and ensure that only one source row maps to each target row (arbitrarily choosing the row with the highest loadkey):

-- Find keys for updated rows
    loadkey = MAX(DS.loadkey)
INTO #Delta
FROM dbo.dwSource AS DS
    SELECT 1 
    FROM dbo.dwTarget AS DT
        DT.ppw_id = DS.ppw_id
        AND DT.mytableid = DS.mytableid
        AND DT.row_hash <> DS.row_hash

key location plan

If testing shows this query would benefit from parallelism, you could add a OPTION (USE HINT ('ENABLE_PARALLEL_PLAN_PREFERENCE')) hint to give:

parallel key location plan

Now that we have the keys, we can tell the optimizer about the uniqueness using:

ADD PRIMARY KEY CLUSTERED (ppw_id, mytableid);

The final update is then:

SET DT.other_columns = DS.other_columns
JOIN dbo.dwTarget AS DT WITH (INDEX(1))
    ON DT.ppw_id = DEL.ppw_id
    AND DT.mytableid = DEL.mytableid
JOIN dbo.dwSource AS DS
    ON DS.loadkey = DEL.loadkey;

update plan

This ensures that non-key columns are only looked up for rows that will actually be updated. The WITH (INDEX(1)) hint ensures that rowset sharing can be used (so the index seek directly provides the location of the update). This can be omitted if testing shows the alternative plan naturally selected by the optimizer is better in practice. Note that it is important for nested loops to be chosen here. You might need to enforce that with a hint like OPTION (FAST 1). If the number of rows updated is truly always a small fraction, the optimizer ought to choose a nested loops plan naturally.


The key-location plan (with the Right Semi Merge Join) is still quite expensive, because all rows from both tables are read and tested.

If you have complete freedom over indexing (and there are no other significant drawbacks) a potentially optimal plan could be obtained via a couple of secondary columnstore indexes on the static (non-updated) columns:

ON dbo.dwSource (ppw_id, mytableid, loadkey, row_hash);

ON dbo.dwTarget (ppw_id, mytableid, row_hash);

This makes the key-location plan:

columnstore key-location plan

At the cost of a memory grant for the hash, this plan provides pure batch-mode operation for all operators (except the parallel insert), and early bitmap semi-join reduction. This will likely execute extremely quickly.

Columnstore performance is most impressive when batch mode execution is used, and the data is optimally arranged in highly-compressed segments. Data changes may be slower compared with rowstore, and may contribute to slower performance due to deleted row bitmaps and rows being held in rowstore delta segments. Choosing and maintaining an optimal columnstore configuration is not necessarily trivial, see the documentation starting at Columnstore indexes - Overview.

These are the main alternatives for you to investigate and apply (or not) as appropriate to your circumstances.

You could also consider making the hash code column a fixed-length binary rather than varbinary, assuming whatever hashing implementation you are using produces a fixed length result. I am also assuming you are happy to accept the small chance of the hash not detecting a change.

@Rob Farley 2018-07-06 02:55:16

You wouldn’t need that sort if your DWLoad data could be fetched in (ppw_id, mytableid) order. I’m not saying you should change your clustered index necessarily, because that’s the kind of thing that requires more thought, but if you were to do that, you wouldn’t need that Sort operator.

@sqlbanana 2018-07-07 01:11:18

Thank you, I created a non clustered index on the dwload table hoping it would use that. I didn't want to cause fragmentation when loading dwload itself hence i just had a single identity pk there (it in turn loads from an informix system).

@Rob Farley 2018-07-07 11:26:11

But loading it would need to populate the non clustered index too, so you’re going to get page splits regardless. Maybe just accept there will be page splits there and take advantage at the update step.

Related Questions

Sponsored Content

1 Answered Questions

[SOLVED] Ideas how to make this query faster?

1 Answered Questions

[SOLVED] Forcing execution plan with local join hints

1 Answered Questions

2 Answered Questions

[SOLVED] Row estimates always too low

2 Answered Questions

[SOLVED] Performance tuning on a query

4 Answered Questions

[SOLVED] Hash Match inner join in simple query with in statement

Sponsored Content