Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Consider the following query:

MERGE [Parameter] with (rowlock) AS target
    USING (SELECT @AreaId, @ParameterTypeId, @Value)
            AS source (AreaId, ParameterTypeId, Value)
    ON (target.AreaId = source.AreaId AND 
        target.ParameterTypeId = source.ParameterTypeId)
    WHEN MATCHED THEN 
        UPDATE SET target.Value = source.Value, @UpdatedId = target.Id
    WHEN NOT MATCHED THEN
        INSERT ([AreaId], [ParameterTypeId], [Value])
        VALUES (source.AreaId, source.ParameterTypeId, source.Value);

Statistics I/O gives the following output:

Table 'ParameterType'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Area'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Parameter'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Worktable appears in the messages tab which makes me think that tempdb is being used by MERGE.

I am not seeing anything in the Execution plan that would indicate a need for tempdb

Does MERGE always use tempdb?

Is there anything in BOL that explains this behavior?

Would using INSERT & UPDATE be faster in this situation ?

Left

enter image description here

Right

enter image description here

Here is the table structure

enter image description here

share|improve this question
1  
Can you show the execution plan? Does it have a merge join operating in many-many mode? That would use a worktable in tempdb for example. –  Martin Smith Jul 18 '13 at 9:19
    
Added the execution plan, sorry so small. –  Craig Efrein Jul 18 '13 at 9:28
    
The spool in the plan is a worktable in tempdb. Does seem odd that it is there for a single row though. I guess it may be there for Halloween protection. –  Martin Smith Jul 18 '13 at 9:28
    
I see it now. Stores the data from the input into a temporary table in order to optimize rewinds. –  Craig Efrein Jul 18 '13 at 9:36
1  
This is broken code without a unique constraint on (AreaId, ParameterTypeId). –  Jon Seigel Jul 18 '13 at 13:18

2 Answers 2

up vote 4 down vote accepted

(Expanding on my comment on the question.)

Without a unique constraint on the combination of AreaId and ParameterTypeId, the given code is broken because @UpdatedId = target.Id will only ever record a single row Id.

Unless you tell it so, SQL Server can't implicitly know the possible states of the data. Either the constraint should be enforced, or if multiple rows are valid, the code will need to be changed to use a different mechanism to output the Id values.

Because of the possibility that the scan operator will come across multiple matching rows, the query must eager spool all the matches for Halloween protection. As indicated in the comments, the constraint is valid, so adding it will not only change the plan from a scan to a seek, but also eliminate the need for the table spool, as SQL Server will know there is going to be either 0 or 1 rows returned from the seek operator.

share|improve this answer

If the update can change the position of the row in the index scanned by the update, SQL Server need to protect from the Halloween Problem. For that SQL Server usually inserts an eager table spool into the execution plan right after the index scan. That operator basically creates a copy of the rows in question and it uses tempdb for that.

The update portion of the MERGE statement has to follow the same rules and also uses a table spool in most cases where Halloween Protection is required.

While I can't tell if this is the case in your query, as I don't know the index definition(s), this is most likely what is happening here.

share|improve this answer
    
I have added in the table structure. I would basically like to determine if A. I can avoid tempdb while using merge and B. whether merge is better or worse than insert/update/delete in this case. –  Craig Efrein Jul 18 '13 at 12:56
    
@Craig I really doubt using a one-row worktable in tempdb is going to have a dramatic impact on performance, but we have no idea if an equivalent insert/update/delete transaction will be worse. You are in the best position to test that, using your data, your queries, with your concurrency and on your hardware... –  Aaron Bertrand Jul 18 '13 at 13:30
    
@AaronBertrand just removing tempdb from the equation was really great. I would like to know how adding the unique constraint resolved that –  Craig Efrein Jul 18 '13 at 14:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.