Latency issues in Transactional Replication

Question

Post reply

Latency issues in Transactional Replication

Viewing 15 posts - 16 through 30 (of 43 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

not discarded yet
however, we can ping now
A -> B 72ms
B -> A 72ms
A -> C 1ms
C -> A 1ms

Didn't you say that the 2016 and 2017 tables were both going over A-B but 2016 performed well but 2017 did not?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

sjimmo SSChampion Points: 11139 More actions · Answer 2

TheSQLGuru - Wednesday, January 18, 2017 6:01 PM
not discarded yet
however, we can ping now
A -> B 72ms
B -> A 72ms
A -> C 1ms
C -> A 1ms
Didn't you say that the 2016 and 2017 tables were both going over A-B but 2016 performed well but 2017 did not?

72ms is actually quite slow. That's about double what I would think the acceptable level should be.
For some excitement this morning I picked several random internet lines that we have, they were coming in at 13ms. But again, as I said yesterday, ours are dedicated broadband lines between us and our subscribers. I have a total of 75 broadband lines. That is why I asked about whether that is what this is. From these readings, either it isn't or there is a problem on the line. This is more what I would expect to see when I am home using the internet. But this is also one reading, which is why I would want multiple readings throughout the day to see how the load varies.

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

rogelio.vidaurri SSCrazy Points: 2117 More actions · Answer 3

I'll answer both of you here:

"Didn't you say that the 2016 and 2017 tables were both going over A-B but 2016 performed well but 2017 did not?"
Indeed, that's exactly what happens. Does this discard it's a bandwidth issue?

"as I said yesterday, ours are dedicated broadband lines between us and our subscribers"
I don't think we have dedicated broadband lines between servers. We have bare metal servers on each end, with access to Internet protected by Firewalls, etc..

" I would want multiple readings throughout the day to see how the load varies"
all readings have been 72ms so far

sjimmo SSChampion Points: 11139 More actions · Answer 4

rogelio.vidaurri - Thursday, January 19, 2017 7:44 AM
I'll answer both of you here:
"Didn't you say that the 2016 and 2017 tables were both going over A-B but 2016 performed well but 2017 did not?"
Indeed, that's exactly what happens. Does this discard it's a bandwidth issue?
"as I said yesterday, ours are dedicated broadband lines between us and our subscribers"
I don't think we have dedicated broadband lines between servers. We have bare metal servers on each end, with access to Internet protected by Firewalls, etc..
" I would want multiple readings throughout the day to see how the load varies"
all readings have been 72ms so far

Which is what I expected by the ping speed. So there isn't much that can be done there.
As for the tables, are they identical? Do they have different indexing?

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

sjimmo SSChampion Points: 11139 More actions · Answer 5

sjimmo - Thursday, January 19, 2017 7:54 AM
rogelio.vidaurri - Thursday, January 19, 2017 7:44 AM
I'll answer both of you here:
"Didn't you say that the 2016 and 2017 tables were both going over A-B but 2016 performed well but 2017 did not?"
Indeed, that's exactly what happens. Does this discard it's a bandwidth issue?
"as I said yesterday, ours are dedicated broadband lines between us and our subscribers"
I don't think we have dedicated broadband lines between servers. We have bare metal servers on each end, with access to Internet protected by Firewalls, etc..
" I would want multiple readings throughout the day to see how the load varies"
all readings have been 72ms so far
Which is what I expected by the ping speed. So there isn't much that can be done there.
As for the tables, are they identical? Do they have different indexing?

Try running this and post the results please: EXEC sys.sp_replcounters

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

rogelio.vidaurri SSCrazy Points: 2117 More actions · Answer 6

2016 and 2017 tables are different only in the name

A results:
database replicated transactions replication rate trans/sec replication latency (sec) replbeginlsn replnextlsn
mydatabase 8 6125 0.046 0x0016E38C00037DCC0001 0x0016E38C00038B040003

B results (why does it have replicated transactions if it's a passive server? it goes 1, 0, 1, 0 every time I execute the SP and so on):
database replicated transactions replication rate trans/sec replication latency (sec) replbeginlsn replnextlsn
mydatabase 1 66.66667 0.413 0x00162A490003207B0001 0x00162A4A000000100031

C results (same question here):
database replicated transactions replication rate trans/sec replication latency (sec) replbeginlsn replnextlsn
mydatabase 1 21.2766 0.02 0x00162D85000266C700F6 0x00162D850002673F0083

Thanks<b

sjimmo SSChampion Points: 11139 More actions · Answer 7

rogelio.vidaurri - Thursday, January 19, 2017 8:34 AM
2016 and 2017 tables are different only in the name
A results:
database    replicated transactions    replication rate trans/sec    replication latency (sec)    replbeginlsn    replnextlsn
mydatabase 8    6125    0.046    0x0016E38C00037DCC0001    0x0016E38C00038B040003
B results (why does it have replicated transactions if it's a passive server? it goes 1, 0, 1, 0 every time I execute the SP and so on):
database    replicated transactions    replication rate trans/sec    replication latency (sec)    replbeginlsn    replnextlsn
mydatabase 1    66.66667    0.413    0x00162A490003207B0001    0x00162A4A000000100031
C results (same question here):
database    replicated transactions    replication rate trans/sec    replication latency (sec)    replbeginlsn    replnextlsn
mydatabase 1    21.2766    0.02    0x00162D85000266C700F6    0x00162D850002673F0083
Thanks

As you can see, latency is a difficult issue to troubleshoot and can be very involved. This is only the first piece, and a flag is up for replication on B between the distributor and publisher. We haven't gotten to distributor to Subscriber.

I am going to give you a link that should help you to understand and be able to go further. Do these things and see if that makes a difference. I will be willing to assist further if necessary. I know what replication can do and am a big advocate for it. But I also know the headaches it can cause.
https://www.mssqltips.com/sqlservertip/3598/troubleshooting-transactional-replication-latency-issues-in-sql-server/

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

rogelio.vidaurri SSCrazy Points: 2117 More actions · Answer 8

sjimmo - Thursday, January 19, 2017 12:11 PM
rogelio.vidaurri - Thursday, January 19, 2017 8:34 AM
2016 and 2017 tables are different only in the name
A results:
database    replicated transactions    replication rate trans/sec    replication latency (sec)    replbeginlsn    replnextlsn
mydatabase 8    6125    0.046    0x0016E38C00037DCC0001    0x0016E38C00038B040003
B results (why does it have replicated transactions if it's a passive server? it goes 1, 0, 1, 0 every time I execute the SP and so on):
database    replicated transactions    replication rate trans/sec    replication latency (sec)    replbeginlsn    replnextlsn
mydatabase 1    66.66667    0.413    0x00162A490003207B0001    0x00162A4A000000100031
C results (same question here):
database    replicated transactions    replication rate trans/sec    replication latency (sec)    replbeginlsn    replnextlsn
mydatabase 1    21.2766    0.02    0x00162D85000266C700F6    0x00162D850002673F0083
Thanks
As you can see, latency is a difficult issue to troubleshoot and can be very involved. This is only the first piece, and a flag is up for replication on B between the distributor and publisher. We haven't gotten to distributor to Subscriber.
I am going to give you a link that should help you to understand and be able to go further. Do these things and see if that makes a difference. I will be willing to assist further if necessary. I know what replication can do and am a big advocate for it. But I also know the headaches it can cause.
https://www.mssqltips.com/sqlservertip/3598/troubleshooting-transactional-replication-latency-issues-in-sql-server/

sorry but I didnt' get this " a flag is up for replication on B between the distributor and publisher. We haven't gotten to distributor to Subscriber. "
what did you get from the 3 results?

sjimmo SSChampion Points: 11139 More actions · Answer 9

rogelio.vidaurri - Thursday, January 19, 2017 1:59 PM
sjimmo - Thursday, January 19, 2017 12:11 PM
rogelio.vidaurri - Thursday, January 19, 2017 8:34 AM
2016 and 2017 tables are different only in the name
A results:
database    replicated transactions    replication rate trans/sec    replication latency (sec)    replbeginlsn    replnextlsn
mydatabase 8    6125    0.046    0x0016E38C00037DCC0001    0x0016E38C00038B040003
B results (why does it have replicated transactions if it's a passive server? it goes 1, 0, 1, 0 every time I execute the SP and so on):
database    replicated transactions    replication rate trans/sec    replication latency (sec)    replbeginlsn    replnextlsn
mydatabase 1    66.66667    0.413    0x00162A490003207B0001    0x00162A4A000000100031
C results (same question here):
database    replicated transactions    replication rate trans/sec    replication latency (sec)    replbeginlsn    replnextlsn
mydatabase 1    21.2766    0.02    0x00162D85000266C700F6    0x00162D850002673F0083
Thanks
As you can see, latency is a difficult issue to troubleshoot and can be very involved. This is only the first piece, and a flag is up for replication on B between the distributor and publisher. We haven't gotten to distributor to Subscriber.
I am going to give you a link that should help you to understand and be able to go further. Do these things and see if that makes a difference. I will be willing to assist further if necessary. I know what replication can do and am a big advocate for it. But I also know the headaches it can cause.
https://www.mssqltips.com/sqlservertip/3598/troubleshooting-transactional-replication-latency-issues-in-sql-server/
sorry but I didnt' get this " a flag is up for replication on B between the distributor and publisher. We haven't gotten to distributor to Subscriber. "
what did you get from the 3 results?

Latency is high between the publisher and distributor on B.
The results you got were for latency between the publisher and the distributor.
Follow the tests in the link that I posted. It will help to identify the overall health of your replication system.

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

rogelio.vidaurri SSCrazy Points: 2117 More actions · Answer 10

Hi all,

Steve, maybe you misread my readings?
I'm going through the article you kindly posted and it seems we are okay about this specific topic, replication latency is less than a second everywhere.

Today's readings:
A
database <----------------> replicated transactions <----------------> replication rate trans/sec <----------------> replication latency (sec)
mydatabase <----------------> 13 <----------------> 250 <----------------> 0.243

B
database <----------------> replicated transactions <----------------> replication rate trans/sec <----------------> replication latency (sec)
mydatabase <----------------> 1 <----------------> 32.25806 <----------------> 0.173

I've done many readings and we never go to 1 second.
Still on it.

Regards

sjimmo SSChampion Points: 11139 More actions · Answer 11

rogelio.vidaurri - Friday, January 20, 2017 6:31 AM
Hi all,
Steve, maybe you misread my readings?
I'm going through the article you kindly posted and it seems we are okay about this specific topic, replication latency is less than a second everywhere.
Today's readings:
A
database <----------------> replicated transactions <---------------->   replication rate trans/sec <---------------->   replication latency (sec)
mydatabase <----------------> 13 <----------------> 250 <---------------->  0.243
B
database  <---------------->   replicated transactions   <---------------->  replication rate trans/sec  <---------------->   replication latency (sec)
mydatabase <----------------> 1 <----------------> 32.25806 <----------------> 0.173
I've done many readings and we never go to 1 second.
Still on it.
Regards

I am sorry, I did misread it.

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

rogelio.vidaurri SSCrazy Points: 2117 More actions · Answer 12

Hi guys,

UPDATE:
We created a new database, publisher and suscription; replicated it successfully from A to B (different web hosting providers).
The other publishers/subscriptions are working as usual.

It worked correctly, we inserted thousands of rows and didn't see the latency issue.
Next step was to replicate the same new database from A to C (same web hosting provider), and executed the same tests. Thousands of rows were replicated without an issue.
Except when we inserted 40k rows and then 80k rows, the latency issue came back for that database, but it went away in a minute or so.
That's when we stopped testing.

Such database is not being used, it is just for our testing. However, we are starting to see this message once in a while just for this new database's 2 publications ee image attached) even when we there are no rows to be replicated.

It changes to Critical every 10 minutes aprox, and it goes away in 30 seconds or so.

What can be happening?

Regards

rogelio.vidaurri SSCrazy Points: 2117 More actions · Answer 13

Hi again,

Something strange though, just with this new database's replication:

instance A (active):
database    replicated transactions    replication rate trans/sec    replication latency (sec)
mydatabase <------------> 9 <------------>  125 <------------>   0.016
myNEWdatabase <------------> 0  <------------>    0.1059547 <------------>     10.073

instance B (passive):
database    replicated transactions    replication rate trans/sec    replication latency (sec)
myactivedatabase <------------> 0 <------------>    62.5  <------------>   0.073
myNEWdatabase <------------> 0 <------------>    0.2172968 <------------>    5.223

even when there's no activity on both ends, the replication latency (sec) value is high
any ideas?

thanks

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 14

rogelio.vidaurri - Monday, January 30, 2017 7:14 AM
Hi again,
Something strange though, just with this new database's replication:
instance A (active):
database    replicated transactions    replication rate trans/sec    replication latency (sec)
mydatabase <------------> 9 <------------>  125 <------------>   0.016
myNEWdatabase <------------> 0  <------------>    0.1059547 <------------>     10.073
instance B (passive):
database    replicated transactions    replication rate trans/sec    replication latency (sec)
myactivedatabase <------------> 0 <------------>    62.5  <------------>   0.073
myNEWdatabase <------------> 0 <------------>    0.2172968 <------------>    5.223
even when there's no activity on both ends, the replication latency (sec) value is high
any ideas?
thanks

You put the tlog for the new database on a slow USB key drive??

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

rogelio.vidaurri SSCrazy Points: 2117 More actions · Answer 15

TheSQLGuru - Monday, January 30, 2017 9:06 AM
rogelio.vidaurri - Monday, January 30, 2017 7:14 AM
Hi again,
Something strange though, just with this new database's replication:
instance A (active):
database    replicated transactions    replication rate trans/sec    replication latency (sec)
mydatabase <------------> 9 <------------>  125 <------------>   0.016
myNEWdatabase <------------> 0  <------------>    0.1059547 <------------>     10.073
instance B (passive):
database    replicated transactions    replication rate trans/sec    replication latency (sec)
myactivedatabase <------------> 0 <------------>    62.5  <------------>   0.073
myNEWdatabase <------------> 0 <------------>    0.2172968 <------------>    5.223
even when there's no activity on both ends, the replication latency (sec) value is high
any ideas?
thanks
You put the tlog for the new database on a slow USB key drive??

no, we didn't
mdf and ldf files are on the same drive (sme folders) where myactivedatabase's files are