9

I am using simple update statement for specific primary key for SQL linked server as follow

UPDATE t
SET
    processed = 1,
    processed_on = GETDATE()
FROM [LINKED\SERVER].DATABASE.dbo.FileQueue t
WHERE t.FileId = '3b33eff6-fde1-4e8c-9c23-2dbd45f50222'

The both servers are SQL Server 2019. The table definition is

CREATE TABLE dbo.FileQueue
(
    FileId UNIQUEIDENTIFIER NOT NULL,
    Processed BIT NOT NULL,
    Processed_on DATETIME NULL
 CONSTRAINT PK_FileQueue PRIMARY KEY CLUSTERED 
 (
    FileId ASC
 )
)

The Processed column has bit type. The query is slow due to full table scan.

query plan

Why is this happening? When I remove the bit column from the statement, everything works fine as usual with reading and updating single remote row.

The Id column is the clustered primary key. I have a ton of tables with a similar key.

I tried with CONVERT or CAST function and the result is the same.

For query without bit column the execution plan is pretty well.

UPDATE t
SET
    --processed = 1,
    -- any other columns can be added to be updated except bit
    processed_on = GETDATE()
FROM [LINKED\SERVER].DATABASE.dbo.FileQueue t
WHERE t.FileId = 'ABD4442F-8560-43B5-8B04-000000B2A626'

query plan

0

2 Answers 2

9

Ideally, you would call a stored procedure on the remote server to make this modification, with the UUID as a parameter.

Meantime, try using a variable for the bit constant:

DECLARE @true bit = 1;

UPDATE t
SET processed = @true,
    processed_on = GETDATE()
FROM [LINKED\SERVER].DATABASE.dbo.Queue t
WHERE
    t.Id = CONVERT(uniqueidentifier, '3b33eff6-fde1-4e8c-9c23-2dbd45f50222');

Or, if your linked server is configured for RPC OUT, using EXECUTE...AT:

DECLARE 
    @id uniqueidentifier = {GUID '3b33eff6-fde1-4e8c-9c23-2dbd45f50222'},
    @processed bit = 1,
    @processed_on datetime = GETDATE();

EXECUTE 
(
    -- Parameterized statement
    N'
    UPDATE [DATABASE].dbo.Queue 
    SET processed = ?, 
        processed_on = ? 
    WHERE Id = ?',
    --- Parameter values
    @processed,
    @processed_on,
    @id
)
AT [LINKED\SERVER];

Plan captured at the remote server:

Remote plan

If you need it in a single local statement without RPC:

UPDATE t
SET processed = V.processed,
    processed_on = GETDATE()
FROM [LINKED\SERVER].DATABASE.dbo.Queue AS t
CROSS JOIN (VALUES(1)) AS V (processed)
WHERE
    T.Id = CONVERT(uniqueidentifier, '3b33eff6-fde1-4e8c-9c23-2dbd45f50222');

Plan

SQL Server uses a framework called Distributed Query (DQ) to convert your SQL to a form the remote server can understand. Some things in DQ work better than others. The bit data type has always a been a bit of an odd case.

It's a bug of course. If you feel like reporting it, you can do so via Microsoft Support in the usual way, or at https://aka.ms/sqlfeedback

0
1

In your query, SQL Server collects the whole remote table and then it applies locally the filter.

Use openquery in this way:

UPDATE OPENQUERY([LINKED\SERVER], 'select * from DATABASE.dbo.Queue t where WHERE t.Id = CONVERT(uniqueidentifier, ''3b33eff6-fde1-4e8c-9c23-2dbd45f50222'')')
SET
 processed = CAST(1 as bit),
 processed_on = GETDATE()

You can also use the execute statement in this way:

execute ('UPDATE t
SET
    processed = 1,
    processed_on = GETDATE()
FROM DATABASE.dbo.FileQueue t
WHERE t.FileId = ''3b33eff6-fde1-4e8c-9c23-2dbd45f50222''') at [LINKED\SERVER]

Ensure RPC Out is set to True on the Linked Server properties

2
  • Just tried it. OPENQUERY works better, but not ideally. There are two remote actions: remote scan and remote update. When I remove bit field, then only one remote query is performed and all on remote side.
    – supervisor
    Sep 14, 2022 at 9:26
  • not sue if ithis works: SELECT * FROM OPENQUERY([LINKED\SERVER], 'UPDATE t SET processed = 1, processed_on = GETDATE() FROM DATABASE.dbo.FileQueue t WHERE t.FileId = ''3b33eff6-fde1-4e8c-9c23-2dbd45f50222''')
    – MBuschi
    Sep 14, 2022 at 9:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.