2

We have two SQL server hosts. There are about 12 availability groups, where the primary replica is on the "primary" host and the secondary replicas are on the "secondary" host. Now, for one reason or another, every Saturday, the network out of the primary and the network in of the secondary spike up about 600% compared to other days. It stays this way all day. It abruptly starts in the morning and abruptly stops at night.

Given the fact that the network in of the primary does not spike, we are assuming that the network traffic is somehow involved with an "internal" process between the two hosts.

This is obviously to general of a situation to just toss a solution at me. My question is, what data or troubleshooting techniques would be helpful with finding the reason behind this traffic?

I'm familiar with the SQL profiler and activity monitor, but I don't feel like these will be helpful if the network traffic is not the result of clients' queries. I can go as low-level as wireshark, but I don't think I'll be able to discern what the traffic is related to from the packets.

Some things we've already ruled out:

  • Heavy client query load on Saturdays
  • Scheduled jobs on Saturdays
  • 1
    What about other suspects, i.e. Backup secondary to a network share? DBCC and reindex on primary? – donger Aug 28 at 18:23
1

If you have high outbound network activity on the primary server, and high inbound network activity on the secondary server, it is very likely that there is an increase in logged (write) actions in the availability group databases during this period on Saturdays. these are getting synchronized to the secondary, resulting in a lot of network activity.

One option for catching this would be to log sp_WhoIsActive to a table during this time period. You can come back and review these logs to see what queries were running to produce all of this extra AG sync activity.

You mentioned you ruled out scheduled jobs, but this sounds suspiciously like a maintenance activity - rebuilding indexes is something often done on weekends, and it produces tons of traffic in AGs.

  • As it turns out, it was indeed an index optimization job. Thank you! – AlexMayle Oct 22 at 17:52

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.