SQL Server remotely deletes data performance problem resolution through linked servers

Source: Internet
Author: User

Original: SQL Server remotely deletes data performance problem resolution via linked servers

In the previous article, the workaround for SQL Server access to Oracle performance issues through linked servers is described in this article, which addresses the performance issues of remotely deleting SQL Server data under linked servers

1. Problem finding

There is a feature in the system that requires remote deletion of the SQL Server Instance's table data, and a where condition in the DELETE statement, with a subquery in the condition.

The function of the foreground execution is very slow. So prepare for tuning.

The following is the demo code, which is not optimized as follows:

DELETE  from [Linkservername].[AdventureWorks2008].[Sales].[SalesOrderDetail]WHERESalesorderdetailid=5     and EXISTS(SELECT TOP 1 1  from [Linkservername].[AdventureWorks2008].[Sales].[SalesOrderDetail])

The execution plan at this time is as follows:

You can see that there is a remote scan of the execution plan and then perform the filtering locally.

Start profiler tracking on the remote server, some of the following:

You can see that the remote server has a cursor open and then reads the data row by line and returns it to the caller.

Predictable performance can be very poor, how to avoid the remote scanning without the Where condition?

2. Problem solving2.1 OpenQuery

Use OpenQuery to submit a filter for the delete data to the remote server for execution.

DELETE  from OPENQUERY ([linkservername]    ,'SELECT * FROM [adventureworks2008].[ Sales]. [SalesOrderDetail] WHERE salesorderdetailid=5 and    EXISTS (SELECT TOP 1 1 from [adventureworks2008].[ Sales]. [SalesOrderDetail]) ' )

At this point, the execution plan

2.2 sp_executesql

Commits the entire DELETE statement to the remote execution

 @ SQL  nvarchar  (max  )  select   @sql  =   " delete from [adventureworks2008].[ Sales]. [SalesOrderDetail] WHERE salesorderdetailid=5 and EXISTS (SELECT TOP 1 1 from [adventureworks2008].[ Sales]. [SalesOrderDetail])    exec  [linkservername  . [ ] . dbo.sp_executesql  @sql   

The following statements are tracked by the profiler:

 

If there is no place, welcome to shoot bricks, if there are other ways to share, thank you!

SQL Server remotely deletes data performance problem resolution through linked servers

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.