1

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

1

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))
  • Thanks. Actually the MERGE is correctly formed but I made a mistake by coying it here and obfuscating it...sorry. Regarding sql_statement_starting I will have a look. Are you sure that I will get the parameters for a prepared statement (sp_statement_prepare)? – Martin Guth Dec 13 '19 at 9:57
  • No luck there...sp_statement_starting does not get me parameter values for a prepared statement. The output is pretty much the same regarding to the query text as sp_statement_completed. – Martin Guth Dec 13 '19 at 10:01
  • 2
    @MartinGuth, capture the rpc_completed event. That will show the parameters passed to the statement. – Dan Guzman Dec 13 '19 at 11:27
1

Thank you @Dan Guzman. rpc_completed was the missing event for me. Unfortunately there's no simple filtering possible an a query hash because there isn't one associated with the sp_executesql procedure call. Thus I chose to filter on the sqltext itself (possibly a costly operation but it works).

Here's my final XEvent session in case somebody can take advantage of it:

DROP EVENT SESSION [query hash] ON SERVER;
GO
CREATE EVENT SESSION [query hash] ON SERVER 
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,sqlserver.session_id)
    WHERE ([sqlserver].[query_hash]=(6577967268103212561.))),
ADD EVENT sqlserver.rpc_completed(
    ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name, sqlserver.query_hash)
    --WHERE ([sqlserver].[query_hash]=(6577967268103212561.))
    WHERE sqlserver.sql_text LIKE '%ERGE INTO table%'
    ),
ADD EVENT sqlserver.error_reported(
    ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.sql_text,sqlserver.session_id)
    WHERE (([error_number]=2627))
    )
ADD TARGET package0.ring_buffer(SET max_events_limit=(100000),max_memory=(512000))
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

Now I wait for the next error to occur and will post the findings and the solution after I have one.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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