The system I am administering is deadlocking very frequently on a specific stored procedure.
The application is made up of an Azure SQL Database (ASD) that is connected to a web application. The ASD is integrated with the main OLTP database on an Azure VM. The ASD stores a small amount of data, but most of the data is accessed through external tables and CRUD operations are executed on the VM using "SP_EXECUTE_REMOTE" from the ASD.
The problem query looks like:
BEGIN TRY
EXEC SP_EXECUTE_REMOTE @data_source_name = N'VM_db',
@stmt = N'BEGIN TRY
EXEC [dbo].[StoredProc] @Var1,@Var2,@Var3,@Var4
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS ERROR_MSG;
END CATCH',
@params = N'@Var1 NVARCHAR(80), @Var2 BIT, @Var3 UNIQUEIDENTIFIER, @Var4 DATETIME',
Var1=@pVar1, @Var2=@pVar2, @Var3=@pVar3, @Var4=@now
The data source (VM_db) is set to use Read Committed Snapshot Isolation (RCSI). Does the SP_EXECUTE_REMOTE honour the target database's Isolation level, or does it default to READ COMMITTED?