Sql-server – Failing MERGE Statement: How to monitor with XEvents and specifically get parameter values for a prepared statement

extended-eventsmergesql serversql-server-2017unique-constraint

My problem

I am running SQL Server 2017 and would like to investigate an error with a MERGE statement like the following

MERGE INTO table
USING 
    (SELECT ? AS Search_Col) AS SRC
    ON table.blobTask_id = SRC.Search_Col
WHEN MATCHED THEN
    UPDATE SET
    x= ?,
    y= ?,
    z= ? 
WHEN NOT MATCHED THEN
    INSERT (blobTask_id ,x, y, z, ) 
    VALUES (SRC.Search_Col, ?, ?, ?)

Error is

Violation of UNIQUE KEY constraint 'IX_U_CCC_GDV_CONTENTCCC__blobTask_id'. Cannot insert duplicate key in object 'table'. The statement has been terminated.

I am aware that the MERGE statement comes with serveral gotchas. However I could outrule the typical issues ( no triggers, no concurrency on data modifications on the table).

Odd thing is that the Error just comes up just every once in a while and executing the statement again works without errors.

My diagnostic approach

I set up an Extended Events Session filtering for just the query_hash of the problematic statement. Jeremiah Peschka has a great blog post which I followed to do this.

Currently my XEvent Session looks like this:

CREATE EVENT SESSION [query hash] ON SERVER 
ADD EVENT sqlserver.prepare_sql(
    ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name)
    WHERE ([sqlserver].[query_hash]=(6577967268103212561.))),
ADD EVENT sqlserver.sp_statement_completed(SET collect_statement=(1)
    ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.sql_text)
    WHERE ([sqlserver].[query_hash]=(6577967268103212561.))),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name)
    WHERE ([sqlserver].[query_hash]=(6577967268103212561.)))
ADD TARGET package0.event_file(SET filename=N'X:\DB_Data\query_hash.xel',max_file_size=(5),max_rollover_files=(5),metadatafile=N'X:\DB_Data\query_hash.xem')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

My question

Problem is that with this session I only get recordings like the following:
Xevent Recording

Somehow only sp_statement_completed Events show up.
In order to debug the problem it is vital to get to the parameters of the SQL.

Specific question: How do I modify the XEvent session to show me the param values?

I was hoping to get that information from the prepare_sql event but it doesn't show up in the session….perhaps due to my filter?

General request for help: If you know about other ways to effectively troubleshoot and alleviate the issue please let me know as well.

Your Help is much appreciated

Thank you in advance

Martin

Best Answer

Your merge statement is not correctly formed and will always insert all the records, so you will need to update it and tell it to insert the records not matched in the destination table using WHEN NOT MATCHED like this:

MERGE table
USING 
    (SELECT ? AS Search_Col) AS SRC
    ON table.blobTask_id = SRC.Search_Col
WHEN MATCHED THEN
    UPDATE SET
    x= ?,
    y= ?,
    z= ? 
WHEN NOT MATCHED THEN
    INSERT (blobTask_id ,x, y, z, ) 
    VALUES (SRC.Search_Col, ?, ?, ?)

To get the exact statement and parameters for a statement that fails you'll need to capture events before the statement is executed, as the statement fails you will not get a completed statement.

 CREATE EVENT SESSION [a] ON SERVER 
  ADD EVENT sqlserver.sql_statement_starting(
  ACTION(sqlserver.sql_text))
Related Question