‹ All interview questions

Performance

In production, a critical executive dashboard report is timing out consistently during peak hours (8-10 AM).

Senior · ServiceNow · Jan 2026

The question

In production, a critical executive dashboard report is timing out consistently during peak hours (8-10 AM). The report aggregates incident data from the past 90 days across 5 assignment groups (500K+ records total), grouping by CI, Priority, and State with a pivot table showing MTTR calculations. Users report 'Report timeout after 120 seconds'. Logs show high CPU on the reporting node and slow DB queries. Walk us through your diagnosis approach, propose 3+ solution options with trade-offs, and explain production rollout considerations. What metrics would you monitor post-implementation? Follow-up: Show a scripted filter example that could cause this issue vs. an optimized version.

Why This Comes Up in Interviews

A complex report timing out in production typically stems from inefficient database queries scanning massive datasets without proper indexing, combined with peak-hour resource contention. With 500K+ incident records over 90 days, the pivot aggregation by CI, Priority, and State triggers full table scans, Cartesian products from unoptimized joins, and heavy CPU for MTTR calculations—all exacerbated by dashboard multi-threading limits and reporting node saturation.

Diagnosis Approach (Senior Expectation): Don't jump to code fixes. First, reproduce in sub-prod with similar data volume. Check System Logs & Transaction Logs for query plans (enable SQL trace). Use System Diagnostics > Slow Queries to identify the culprit. Monitor Node CPU/Memory via Stats.do and DB execution plans via JDBC probes. Validate indexes on incident.sys_created_on, cmdb_ci, priority, state. Great answers quantify: "Query scans 2M rows, 80% CPU spike, no CI index hit."

The Solution

Problem: Inefficient Scripted Filter (causes full scan)

var problematicFilter = function() {
  var gr = new GlideRecord('incident');
  gr.addQuery('sys_created_on', 'STARTSWITH', gs.beginningOfLast90Days());
  gr.addJoinQuery('cmdb_ci', 'cmdb_ci', 'sys_id', 'cmdb_ci');
  gr.addAggregate('COUNT', 'number');
  gr.addAggregate('AVG', 'business_duration');
  gr.query(); // Scans ALL records, no indexes, pivot explodes
  while(gr.next()) { /* heavy processing */ }
  return 'sys_idIN' + getAllSysIds(gr);
};

Solution: Optimized Scripted Filter with Indexing & Limits

var optimizedFilter = function() {
  var gr = new GlideRecord('incident');
  gr.addQuery('sys_created_on', '>=', gs.beginningOfLast90Days());
  gr.addQuery('assignment_group', 'IN', groupSysIds.join(','));
  gr.orderBy('sys_created_on');
  gr.setLimit(500000); // Prevent OOM
  gr.query(); // Leverages compound index
  var ciMap = {};
  while(gr.next()) {
    var ci = gr.getValue('cmdb_ci');
    ciMap[ci] = (ciMap[ci] || 0) + 1;
  }
  return 'sys_idIN' + Object.keys(ciMap).join(',');
};

Senior Considerations

  • Three Solution Options w/ Trade-offs: 1) Quick Fix - Add Indexes & Row Limits: Index incident.sys_created_on,assignment_group,cmdb_ci (fastest ROI, but doesn't scale beyond 1M rows). 2) Mid-term - Scheduled Reports + PA Indicators: Offload to Performance Analytics with breakdowns (reduces live load 90%, but 15-min latency vs. real-time). 3) Strategic - Data Warehouse Export: Nightly ETL to external BI (scales infinitely, but $cost + data staleness).
  • Production Rollout: Test in DEV & PERF with 110% data volume. Stagger: Indexes first (no downtime), then report config. Use Update Sets for scripted filters. Monitor with APM/Splunk for 48hrs post-deploy.
  • Great Answer Separators: Quantifies impact ("reduces query time 85% via index hit ratio"), discusses edge cases (holiday data spikes, null CI handling), failure modes (index fragmentation), and holistic metrics: Report load time <30s, Node CPU <70%, User abandonment rate=0%.
  • Why This Matters: Good candidates fix syntax. Seniors prevent recurrence via root cause (missing indexes on high-cardinality fields) and balance user experience with platform stability.