Monitoring a Multi-Site Environment After a Performance Complaint
Adventure Works has a main data center and two regional offices. Users in Region B report that a line-of-business web application is slow every morning between 08:00 and 10:00. The application runs on two web servers behind a load balancer and a SQL Server backend in the data center. Region B connects via a 20 Mbps link with 60 ms latency. There is no centralized monitoring; each team checks its own servers ad hoc. The network team suspects the link; the application team suspects SQL. Recent changes include a new backup job on the SQL server and a firmware update on the load balancer.
1. Describe how you would establish monitoring and data collection to determine whether the bottleneck is the WAN link, the load balancer, or SQL, without relying on any single team's assumption.
Read the worked answer
Establish a single source of truth with correlated data:
- Network: enable SNMP/flow monitoring on the Region B router and the WAN link; collect interface utilization, errors, and latency (e.g., via SNMP counters and synthetic probes). A 20 Mbps link at 60 ms is a prime suspect if utilization approaches saturation during 08:00-10:00.
- Load balancer: collect its logs and health-check metrics; correlate the firmware update date with the symptom onset.
- SQL Server: use Performance Monitor counters (batch requests/sec, page life expectancy, disk latency, CPU) and SQL Profiler/Extended Events to capture slow queries during the window.
- Application: enable IIS/application logs and end-to-end response time measurement.
- Correlate timestamps across all sources in a common time zone and dashboard.
This lets you test each hypothesis with evidence rather than opinion.
2. Assuming the data shows the WAN link is saturated only during the morning window, what are the likely causes and what would you do?
Read the worked answer
Likely causes:
- Backup or replication traffic overlapping the morning window: the new SQL backup job may be writing to a remote target across the WAN. Check backup destination and schedule.
- User logon/application startup storms: many users opening the app at once pulls large content (reports, attachments) over the link.
- Chatty application protocol: the app may make many small round trips that are amplified by 60 ms latency.
Actions:
- Move the SQL backup to a local target or schedule it outside business hours; verify with backup job logs.
- Enable QoS/traffic shaping to prioritize interactive application traffic over bulk transfers.
- Consider a local cache or terminal/Citrix-style access for Region B to reduce round trips, or deploy a WAN optimizer if justified.
- Validate with before/after link utilization and application response time measurements.
3. What ongoing monitoring and alerting should be put in place so this class of problem is detected before users complain?
Read the worked answer
- Threshold alerts on WAN link utilization (e.g., >70% for 5 minutes), latency, and packet loss.
- Synthetic transactions: a probe from each region that logs into the app and measures response time, alerting on regression.
- SQL baseline alerts: page life expectancy, CPU, disk latency, and long-running queries.
- Load balancer health and error-rate alerts.
- Correlated dashboard with a single time zone and a documented escalation path.
- Change correlation: tag alerts with recent change tickets (backup job, firmware) so responders can quickly see likely causes.
Review alert thresholds monthly and after any major change to avoid alert fatigue.