Qlik Replicate Troubleshooting Example: Latency on Hypercluster Azure SQL environment

[Dineshan]

A Qlik Replicate task has high latency and fails to start. Blocks were observed on the database and all available processors were consumed for a single, long-running Azure SQL transaction. 

Errors to look out for:

[SOURCE_CAPTURE ]E: RetCode: SQL_ERROR SqlState: 40001 NativeError: 1205 Message: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Transaction (Process ID 1834) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Line: 1 Column: -1

Environment

Qlik Replicate 

Resolution

Killed the long running transaction on Azure SQL and changed the MAX DOP (see img 01) setting to 8, then task was resumed. After few hours the tasks eventually started functioning as expected and latencies came back to normal.

img 01

Cause 

The root cause that was identified, an update statement on target Azure SQL shown long wait and was blocking all the other transactions. One another reason identified was the MXDOP setting in Azure SQL DB was set as 0.