We have been trying to implement a multi-server database solution that tracks 
millions of invoices. However, updating a bit data type status column across 
the link has proved problematic. The following update fails due to a timeout 
because of a full table scan of the remote table

SET XACT_ABORT ON
BEGIN TRAN
     UPDATE LinkedServer.Account.dbo.Invoice
     SET IsPaid = 1
     WHERE InvoiceId = 1234
COMMIT TRAN

--Query Plan:
|--Remote Update(SOURCE:(LinkedServer), OBJECT:("Account"."dbo"."Invoice"), 
SET:([LinkedServer].[Account].[dbo].[Invoice].[IsPaid] = [Expr1003]))
     |--Compute Scalar(DEFINE:([Expr1003]=(1)))
            |--Table Spool
                 
|--Filter(WHERE:([LinkedServer].[Account].[dbo].[Invoice].[InvoiceId]=(1234)))
                     |--Remote Scan(SOURCE:(LinkedServer), 
OBJECT:("Account"."dbo"."Invoice"))


However, updating a character field works fine!:
SET XACT_ABORT ON
BEGIN TRAN
     UPDATE LinkedServer.Account.dbo.Invoice
     SET CustomerName = 'Fred'
     WHERE InvoiceId = 1234
COMMIT TRAN

Query Plan:
|--Remote Query(SOURCE:(LinkedServer), QUERY:(UPDATE 
"Account"."dbo"."Invoice" set "CustomerName" = 'Fred' WHERE 
"InvoiceId"=(1234)))

So other than the appearance of this being a bug, is there any way to stop 
SQL Server from doing the full scan?  And yes, I tried OPENQUERY. It is not 
an option, since it's performance is just as bad as the full scan.

Thanks.

Dan (Dan@discussions.microsoft.com) writes:
> We have been trying to implement a multi-server database solution that
> tracks millions of invoices. However, updating a bit data type status
> column across the link has proved problematic. The following update
> fails due to a timeout because of a full table scan of the remote table 
>... 
> --Query Plan:
>|--Remote Update(SOURCE:(LinkedServer), OBJECT:("Account"."dbo"."Invoice"), 
> SET:([LinkedServer].[Account].[dbo].[Invoice].[IsPaid] = [Expr1003]))
>      |--Compute Scalar(DEFINE:([Expr1003]=(1)))
>             |--Table Spool
>                  
>|--
Filter(WHERE:([LinkedServer].[Account].[dbo].[Invoice].[InvoiceId]=(1234)))
>                      |--Remote Scan(SOURCE:(LinkedServer), 
> OBJECT:("Account"."dbo"."Invoice"))
> 
> 
> However, updating a character field works fine!:
>...
> Query Plan:
>|--Remote Query(SOURCE:(LinkedServer), QUERY:(UPDATE 
> "Account"."dbo"."Invoice" set "CustomerName" = 'Fred' WHERE 
> "InvoiceId"=(1234)))

I was able to reproduce this with a table I just made up, and I added a
repro to your Connect bug, 
https://connect.microsoft.com/SQLServer/feedback/details/528007/updating-
bit-field-on-linked-server-fails

I can see how this could happen with newer data types like datetime2,
for which there is no matching core type in OLE DB, but it should not
happen on bit, I think.

But the general problems with linked servers is that the query plans
are generated with knowledge of what is on the other end, to the
smallest common denominator is used.
 
> So other than the appearance of this being a bug, is there any way to
> stop SQL Server from doing the full scan?  And yes, I tried OPENQUERY.
> It is not an option, since it's performance is just as bad as the full
> scan. 

Did you try EXEC AT:

  EXEC('UPDATE Account.dbo.Invoice 
        SET    IsPaid = 1
        WHERE  InvoiceID = ?', 1234) AT LinkedServer

You don't have to pass 1234 as a parameter, but I wanted to show that
it is possible.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Dan wrote:
> 
> We have been trying to implement a multi-server database solution that tracks
> millions of invoices. However, updating a bit data type status column across
> the link has proved problematic. The following update fails due to a timeout
> because of a full table scan of the remote table
> 
> SET XACT_ABORT ON
> BEGIN TRAN
>      UPDATE LinkedServer.Account.dbo.Invoice
>      SET IsPaid = 1
>      WHERE InvoiceId = 1234
> COMMIT TRAN
> 
> --Query Plan:
> |--Remote Update(SOURCE:(LinkedServer), OBJECT:("Account"."dbo"."Invoice"),
> SET:([LinkedServer].[Account].[dbo].[Invoice].[IsPaid] = [Expr1003]))
>      |--Compute Scalar(DEFINE:([Expr1003]=(1)))
>             |--Table Spool
> 
> |--Filter(WHERE:([LinkedServer].[Account].[dbo].[Invoice].[InvoiceId]=(1234)))
>                      |--Remote Scan(SOURCE:(LinkedServer),
> OBJECT:("Account"."dbo"."Invoice"))
> 
> However, updating a character field works fine!:
> SET XACT_ABORT ON
> BEGIN TRAN
>      UPDATE LinkedServer.Account.dbo.Invoice
>      SET CustomerName = 'Fred'
>      WHERE InvoiceId = 1234
> COMMIT TRAN
> 
> Query Plan:
> |--Remote Query(SOURCE:(LinkedServer), QUERY:(UPDATE
> "Account"."dbo"."Invoice" set "CustomerName" = 'Fred' WHERE
> "InvoiceId"=(1234)))
> 
> So other than the appearance of this being a bug, is there any way to stop
> SQL Server from doing the full scan?  And yes, I tried OPENQUERY. It is not
> an option, since it's performance is just as bad as the full scan.
> 
> Thanks.

Another thing you could try is this:

  UPDATE LinkedServer.Account.dbo.Invoice
  SET IsPaid = CAST(1 AS bit)
  WHERE InvoiceId = 1234

-- 
Gert-Jan

Gert-Jan Strik (sorrytoomuchspamalready@xs4all.nl) writes:
> Another thing you could try is this:
> 
>   UPDATE LinkedServer.Account.dbo.Invoice
>   SET IsPaid = CAST(1 AS bit)
>   WHERE InvoiceId = 1234
 
Nope. That was my initial thought, but I tested it before I posted, and
it doesn't help.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx