question

jariquelme avatar image
0 Votes"
jariquelme asked ·

Bad performance in SQL Server 2019 to SQL Server 2016 Snapshot replication

Hi.

I'm trying to replicate a database from SQL Server 2019 to another database on SQL Server 2016. Both servers are connected through internet.
I have a perfomance trouble with a table with 80 millions of records. SQL Server does a bulk copy of 5 millions of records each step.
First step took about 3 hours.
Second step took about 4 hours.
Third step took about 5 hours.
Fourth step took about 6 hours.
Now is in fifth step.
The message in the Replication Monitor is: The process is running and is waiting for a response from the server.
Table has not FK and has not indexes. Only have a PK (Identity). Database model is inherited.
Any ideas to accelerate the process or any configuration option that I forgot?

I updload an image of the process log.
76914-logreplicacion-forum.png

TIA.


sql-server-general
· 12
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Have you looked at the wait stats on the target server? You find these in sys.dm_exec_session_wait_stats. Filter on the spid for the bulk load.

What is the recovery model of the target database?

1 Vote 1 ·
jariquelme avatar image jariquelme ErlandSommarskog ·

Hi. Thanks for the answer.
In target server I have a wait type async_network_io, an IO_Completation and others wait type.

77206-image.png

It's normal?
The recovery model in target database is Simple.


0 Votes 0 ·
image.png (63.7 KiB)

Are all these spids related to the load of the snapshot? I told you to filter the output.

Since spid 60 is the only one that has a lot of waits, one could guess that this our guy. But the top wait type is a little unexpected, so please confirm this first.

1 Vote 1 ·
Show more comments

Change the recovery model on the subscriber to bulk-logged.

I would check for locking on the subscriber server to see if there is some process locking with the distribution agent.

select blocking_session_id,last_wait_type, wait_type, wait_time from sys.dm_exec_requests
where session_id=60

Now I suspect the bcp process has completed and you are building your index(es).
Use this command to see what the replication agent is up to.

select * from sys.dm_exec_input_buffer ( 60,0)

You will also need to set your heartbeat interval on your publisher/distributor process to prevent it from timing out.

sp_changedistributor_property heartbeat_interval,120

The above will set it to 2 hours.

The high async_network_io wait stat is typical of replication as it has to send a lot of status information over the network.

HTH






1 Vote 1 ·
Show more comments

1 Answer

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered ·

Hi @jariquelme,

Suggest you check if there is any blocking process that is stopping your replication.

 USE Master
 GO
 SELECT * 
 FROM sys.dm_exec_requests
 WHERE blocking_session_id <> 0;
 GO

Or

 USE Master
 GO
 SELECT session_id, wait_duration_ms, wait_type, blocking_session_id 
 FROM sys.dm_os_waiting_tasks 
 WHERE blocking_session_id <> 0
 GO


If the response is helpful, please click "Accept Answer" and upvote it, thank you.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi.

Thanks for your answer. I don't have any records with the querys that you specified me, so, i don't have blocking sessions.

0 Votes 0 ·