3

We have two databases on linked SQL Servers that are partially synchronized. Occasionally, one side goes down for an upgrade, then the other goes down for an upgrade, and then we re-synchronize.

Upgrades can take upwards of two days. Re-synchronizing two days worth of data is a large operation, so we broke it into batches. During testing, we completed twenty batches successfully before this message appeared in the query window in SSMS:

OLE DB provider "SQLNCLI11" for linked server "MyLinkedServer" returned message "Query timeout expired".

We restored, tested it again, and the operation was successful. We tested on a slower pair of machines, and it timed out on the first batch. We concluded that environmental noise (other processes on the server) and random variance is causing our query to occasionally take too long.

Here is the query that is timing out:

MERGE INTO dbo.MyTable AS target
USING (
    SELECT localData.ID, remoteData.Name AS Name, remoteData.Value AS Value
    FROM OPENQUERY (
        MyLinkedServer,'
        SELECT TOP 500000 Name, Value
        FROM MyDatabase.dbo.MyTable
        WHERE Synced = 0'
    ) AS remoteData
    JOIN dbo.MyTable AS localData ON localData.Name = remoteData.Name
    WHERE localData.Synced = 0
) AS source
ON source.ID = target.ID
-- We need to pull this record
WHEN NOT MATCHED BY target THEN 
    INSERT (
        Name, Value, Synced
    ) VALUES (
        source.Name, source.Value, 1
    )
-- We already have this record
WHEN MATCHED THEN
    UPDATE SET target.Synced = 1
-- There is an OUTPUT clause here that collects the remote IDs of all of 
-- the rows that were synced and stores them in a table. We update the
-- remote table before we do the next batch, so that we don't sync the same
-- 500000 rows over and over again.
;

The query above represents one batch. It replaces batchablequery below. We separated it out like this because we sync more than one table.

DECLARE @count INT = 500000
WHILE @count > 0
BEGIN
    batchablequery
    SET @count = @@ROWCOUNT

    INSERT INTO MyLogTable 
    VALUES(GETDATE(), 'We finished a batch of size ' + CONVERT(varchar(20), @count))

    CHECKPOINT
END

We could fix the query timeout in one of three ways:

  • Improve query performance
    • It spends most of its time waiting with a PAGEIOLATCH_SH
  • Decrease or throttle batch size
  • Increase timeout

Before we choose a solution, we want to fully understand which timeouts are affecting us. We are confused by which ones are applicable to our query. Here are our current settings, and some notes we took from MSDN:

  • Server Remote Query Timeout
    • Ours is set to 600
  • Linked Server Connection Timeout
    • Ours is set 0, so sp_configure Remote Query Timeout Option is used instead.
  • Linked Server Query Timeout
    • Ours is set to 0, so sp_configure Query Wait option is used instead.
  • Query Wait
    • Ours is set to -1, so 25 times the estimated query cost is used.
    • The estimated query cost doesn't have a unit of measure on it...

Which setting or settings are the cause of our timeout?

  • query batch of 500K seems to be huge for linked server + your servers location and network bandwidth plays an important role. Have you thought of using SSIS instead of linked server ? That will be more efficient. – Kin Shah Sep 3 '15 at 15:30
  • @Kin We arrived at 500k experimentally. We tested with 250k and 1000k, and both took longer (for the entire table) than 500k to finish. Our current implementation uses linked servers, but if SSIS is the only thing that can save our performance, I wouldn't be averse to reworking. The thing is, we are at the point where we succeed most of the time, and fail only sometimes, so we just want to patch the issue, not rework it entirely. – Rainbolt Sep 3 '15 at 15:36
  • Just getting more context of your problem - where are you running the query - on source (pushing data to destination) or on destination (pulling data from source) as direction matters ! – Kin Shah Sep 3 '15 at 15:52
  • @Kin The merge runs on target, and we pull data from source. In other words, target is local, and source is remote. We use OPENQUERY to get the data from the remote source. We chose OPENQUERY over EXECUTE AT because it allows us to join onto the results. – Rainbolt Sep 3 '15 at 16:12
  • I realized that as I was modifying my query to make it suitable for posting, I accidentally removed the SQL that updates the remote side. As written, it looks like the query will keep trying to sync the same rows over and over. This was a posting mistake. The original query updates the remote side before moving on to the next batch. I updated the question to show this, and I sincerely apologize to anyone who was in the middle of writing a lengthy answer based on that mistake. – Rainbolt Sep 3 '15 at 16:24
1

You could try putting the remoteData into a temp table before doing the merge. a lot of your time will be taken up with comparisons of data not the actual retrieval so if you run the

SELECT remoteData.Name AS Name, remoteData.Value AS Value
into #remoteData
    FROM OPENQUERY (
        MyLinkedServer,'
        SELECT TOP 500000 Name, Value
        FROM MyDatabase.dbo.MyTable
        WHERE Synced = 0'
    )

then in the merge statement

MERGE INTO dbo.MyTable AS target
USING (
    SELECT localData.ID, remoteData.Name AS Name, remoteData.Value AS Value
    FROM #remoteData as remoteData
    JOIN dbo.MyTable AS localData ON localData.Name = remoteData.Name
    WHERE localData.Synced = 0
) AS source

It may help (can't promise)

0

If you are in doubt of which is the timeout that's affecting you, just look at the duration since the start of the query and the timeout error, as simple of that.

If it's exactly those 10 minutes you'll know for sure that's the Server Remote Query Timeout, since you have 0 in your local connection settings.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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