SQL-IMPORTER job throws error. about sqlwatch

[DickUMCG]

Update: In the repository database the sqlwatch_logger_xes_query_processing-table doesn't show entries for this partical instance.
But...

So, there was another sql_instance with particular data with exactly the same timestamp.

That must be causing this. Do we need a modification of the index. With multiple instances events can occur at exactly the same time.

[DickUMCG]

Continue:

I tried issuing this on the repository-database:

update sqlwatch_logger_xes_query_processing set event_time='2022-01-27 19:26:49.136' where event_time='2022-01-27 19:26:49.137'

This results in 1 row affected and committed, but when querying again it still shows the old value.

So tried it on the server which can't pass the information but this results in the same.

As the information is not that important I did a delete of the culprit on the server with problems and ran SQLWatchimport again, and this time it complains about the next entry in the table with the timestamp 5 minutes later.

Next step:

Now no errors on this table anymore. But the next table :

2022-02-01 17:43:52.170 ERROR Failed to merge table "[server0978].dbo.sqlwatch_logger_xes_iosubsystem"
at Cannot insert duplicate key row in object 'dbo.sqlwatch_logger_xes_iosubsystem' with unique index 'idx_sqlwatch_xes_iosubsystem_event_time'. The duplicate key value is (Jan 27 2022 7:26PM).
;merge dbo.sqlwatch_logger_xes_iosubsystem as target
using (
select s.* from [#dbo.sqlwatch_logger_xes_iosubsystem] s
inner join dbo.sqlwatch_logger_snapshot_header h
on s.[snapshot_time] = h.[snapshot_time]
and s.[snapshot_type_id] = h.[snapshot_type_id]
and s.[sql_instance] = h.[sql_instance]) as source
on (source.[snapshot_time] = target.[snapshot_time] and source.[snapshot_type_id] = target.[snapshot_type_id] and source.[sql_instance] = target.[sql_instance] and source.[event_time] = target.[event_time])
when not matched
then insert ([event_time],[io_latch_timeouts],[total_long_ios],[longest_pending_request_file],[longest_pending_request_duration],[snapshot_time],[snapshot_type_id],[sql_instance])
values (source.[event_time],source.[io_latch_timeouts],source.[total_long_ios],source.[longest_pending_request_file],source.[longest_pending_request_duration],source.[snapshot_time],source.[snapshot_type_id],source.[sql_instance]);

Another index modification:

This time Sqlwatchimport.exe completes without errors:

[pluim003]

Update: I'm moving stuff from DickUMCG to my other account and will close the other one. Inconvenient to use 2 accounts. Forked repository has also been transferred.

[pluim003]

Another thing. Discovered now that in the repository database there were about 170 tables named _DUMP_20220201_timestamp_server_tablename . Had to remove those manually (fortunately with one statement :-) ).

[marcingminski]

This has already been explained in: #394 (comment)

[pluim003]

@marcingminski but shouldn't the index-definitions be changed to prevent this?

[marcingminski]

To prevent errors? Are those errors caused by the index?

[pluim003]

Yep. As event_time might be the same for more sql_instances.

[pluim003]

Made a new pullrequest to remove some typos