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
Best Answer
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_WhoIsActiveto 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.