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?