What is the transaction isolation level for SP_EXECUTE_REMOTE

Question

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?

Answer 1

The isolation level can be different according to your setting and actions. You can directly check the information by using SP_EXECUTE_REMOTE and run a query to check it.

• To check the Connection Isolation Level run DBCC USEROPTIONS

• To check the Database Isolation Level run the query: SELECT name, is_read_committed_snapshot_on FROM sys.databases WHERE name = DB_NAME();

• To check the Statement Isolation Level you can use on the remote server Extended Events