Central repository failed to merge (duplicate key row)

[gbutler202]

Did you check DOCS to make sure there is no workaround? https://sqlwatch.io/docs/ Yes.

Describe the bug We have multiple SQLWATCH data sets being pulled to central repository by SqlWatchImport. Unique index violation for [dbo].[sqlwatch_logger_xes_query_processing] and [dbo].[sqlwatch_logger_xes_iosubsystem]. Obviously this was a fluke that two independent SQL instances generated entirely separate data with the exact same [event_time] value, but highlights that the uniqueness constraint on these (and possibly other) tables may need to include [event_time] and [sql_instance]. Log extract attached.

To Reproduce As above, and see attached log extract. sqlwatchimport.log

Expected behaviour Rows from different servers with the exact same timestamp should not cause a uniqueness violation in a central repository.

Workaround -- Apply to SQLWATCH central repository (though it shouldn't impact individual instances) USE [SQLWATCH]; GO

DROP INDEX [idx_sqlwatch_xes_query_processing_event_time] ON [dbo].[sqlwatch_logger_xes_query_processing]; CREATE UNIQUE NONCLUSTERED INDEX [idx_sqlwatch_xes_query_processing_event_time] ON [dbo].[sqlwatch_logger_xes_query_processing] ( [event_time] ASC, [sql_instance] ASC );

DROP INDEX [idx_sqlwatch_xes_iosubsystem_event_time] ON [dbo].[sqlwatch_logger_xes_iosubsystem]; CREATE UNIQUE NONCLUSTERED INDEX [idx_sqlwatch_xes_iosubsystem_event_time] ON [dbo].[sqlwatch_logger_xes_iosubsystem] ( [event_time] ASC, [sql_instance] ASC ); GO

Windows Server (please complete the following information): Windows Server 2019.

SQL Server (please complete the following information):

• SQL Version: [e.g. SQL Server 2016 SP1]
• SQL Edition: [e.g. Enterprise] SQL Server 2019 Enterprise.

SQL Server Management Studio (SSMS -> about -> copy info):

• paste "about" info here: N/A SQLWATCH version (from DACPAC or from sysinstances)
• 4.3

Additional context Love the product @marcingminski, keep up the amazing work!

[marcingminski]

Hi,

the primary key on [dbo].[sqlwatch_logger_xes_iosubsystem] already includes sql_instance and event_time

    constraint [pk_logger_performance_xes_iosubsystem] primary key (
        [snapshot_time], [snapshot_type_id], [sql_instance], [event_time]
        ),

I think that the issue is not with the primary key, but with the merge, which is trying to re-insert the same data from the remote XES (as the data exists in the remote XES as well as central repo). Does this sound like a possibility? However, if this was the case, it would have had a different snapshot_time so there must be something else going on, like duplicate values in the XES?

[gbutler202]

@marcingminski the log extract sqlwatchimport.log shows the unique indexes on [dbo].[sqlwatch_logger_xes_iosubsystem] and [dbo].[sqlwatch_logger_xes_query_processing] are to blame, not the primary keys. After I adjusted the two particular indexes as in my workaround above and reran SqlWatchImport, the merge succeeded. The issue is specifically with [event_time} being the identical from two SQL instances. This is very much an edge case, but here is the data: If there were XES duplicates, wouldn't that be from the same SQL instance? Doesn't seem to be the case here. Let me know if I can supply any more info, cheers

[marcingminski]

Ah, I get you. Apologies, I misunderstood your original issue. You are right. I will get those indexes fixed. Thank you.