I have a query running on one server (let's call it Server B). It looks something like the below - using a linked server to query data from another server ('Server A')
-- in SERVER B
CREATE PROC [foo]
AS
INSERT [Table]
SELECT this, that
FROM [LnkServer Server A].[database].[schema].[table];
Server B has is_read_committed_snapshot_on = 1, while Server A does not - would the sPROC foo read snapshots on Server A, or wait for transactions to complete?
Similarly to the above, If I were to change foo to include the line SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED, would it read uncommitted data from Server A, or would it still be reading as before?
I would like to change the way the scripts are written so they look more like this:
------------- In Server A --
CREATE PROC [bar]
AS
-- I have included a local isolation set just as an example, Read uncommitted may not always be appropriate and I know the risks
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT this, that
FROM [database].[schema].[table];
GO
--------------- Server B --
CREATE PROC [foo]
AS
DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM OPENQUERY([LnkServer Server A],''EXEC [database].[schema].[bar]'')';
INSERT [Table]
EXEC sp_executesql @SQL;
GO
I feel like using OPENQUERY like this should result in better execution plans and would allow the isolation level to be a bit more visible, but there are loads of queries like the first example and I worry that they might be causing a problem.
Everything I search for on the web tells me lots of information about when to use each isolation level and why, but I can't see anything about how it interacts with linked servers and I worry that it might be causing some of the problems we are seeing...