SSRS Report Performance Monitoring

Automated nightly performance tests for eSHARS SSRS reports. Catches regressions before users do.

---

What We Measure

Each test mimics the full user render flow end-to-end:

Login → POST report form → poll for viewer URL → ScriptManager async PostBack → TTFB

The ScriptManager PostBack is the slow step — this is when SSRS actually executes the stored proc and renders the report. Wall-clock time from form submission to that final response is what we record.

In addition to wall-clock time, we query ReportServer.dbo.ExecutionLogStorage for the SSRS-internal phase breakdown:

Phase	What it measures
TimeDataRetrieval	Time SQL spent executing the report stored proc
TimeProcessing	Time SSRS spent processing the result set
TimeRendering	Time SSRS spent rendering to HTML

A slow TimeDataRetrieval points to a SQL problem (missing index, bad plan, data volume). Slow TimeProcessing/TimeRendering points to SSRS configuration or report complexity.

---

Test Matrix

Tier	Reports	Combos	Total Cases
Critical	6	5	30
Standard	8	2	16

Critical reports (Activity Tracking, Caseload Summary, Service Log, District ARD, Billing Summary, Eligibility Status) are tested with five parameter combinations: Narrow, Broad-Date, Broad-Campus, Broad-Service, and Worst-Case.

Standard reports are tested with Narrow and Worst-Case only.

---

Nightly Pipeline

The pipeline runs at 3 AM UTC (9 PM CT) — after nightly batch jobs complete — against QA → UAT → Prod sequentially.

• If QA regresses, UAT and Prod are skipped
• Results CSV published as a pipeline artifact per environment
• Pipeline fails (red) when any report exceeds 1.5× its baseline p95
• A 1.2× warning is noted but does not fail the pipeline

Pipeline: eshars-ops → pipelines/ssrs-performance-test.yml

---

Reading the Results

Results CSV columns:

Column	Meaning
Run 1 Time (ms), Run 2 Time (ms)	Individual wall-clock times
Avg Time (ms)	Average across measured runs
TimeDataRetrieval	SSRS log — SQL execution time
TimeProcessing	SSRS log — result set processing
TimeRendering	SSRS log — HTML render time
SSRS TotalMs	Sum of all three phases (SSRS-internal only)
Status	OK / WARNING / REGRESSION
Ratio	Avg / BaselineP95

Wall-clock vs SSRS total

Wall-clock time includes app server round-trips, DB parameter lookup, and network. SSRS TotalMs is purely server-side render time. A large gap between them usually means app server or network overhead.

---

Baselines

Baseline files live in eshars-ops/testing/ssrs-perf/baselines/. Each stores the p95 and average from a known-good run per report per environment.

Refresh baselines when:

• A SQL query optimisation lands (new index, SP rewrite)
• Infrastructure changes (App Gateway, VNet, SQL tier upgrade)
• Monthly — data volume grows over the school year

cd eshars-ops/testing/ssrs-perf

.\Update-SsrsBaseline.ps1 `
    -Environment QA `
    -AppUsername 'your@email.com' `
    -AppPassword (Read-Host -AsSecureString) `
    -SqlUser 'ssrs-readonly' `
    -SqlPassword (Read-Host -AsSecureString)

git add baselines/ && git commit -m "Refresh SSRS baselines after <reason>"

---

Running Manually

cd d:\dev.v2\eshars\eshars-ops\testing\ssrs-perf

# Critical reports against QA, compare against baseline
.\Invoke-SsrsPerformanceTest.ps1 `
    -Environment QA `
    -Tier Critical `
    -AppUsername 'your@email.com' `
    -AppPassword (Read-Host -AsSecureString) `
    -SqlUser 'ssrs-readonly' `
    -SqlPassword (Read-Host -AsSecureString)

Results CSV is written to results-{Environment}-{timestamp}.csv next to the script.

---

Investigating a Regression

1. Check the results CSV — which reports regressed? What is the ratio?
2. Check ExecutionLogStorage using the baseline SQL script in eshars-ops/testing/ssrs-execution-log-baseline.sql:

   -- Regression detection: reports that got 50%+ slower vs last 30 days
   -- Run the "Regression Detection" query from ssrs-execution-log-baseline.sql

3. Identify the slow phase:
   • TimeDataRetrieval high → SQL problem. Check query plans, indexes, data growth.
   • TimeProcessing high → Result set too large, or SSRS memory pressure.
   • TimeRendering high → Report layout complexity, or SSRS worker overloaded.
4. Check for recent changes — did a new index drop, a migration run, or a batch job run longer than usual last night?
5. Check VM health — SSRS runs on vm-ssrs16-prod. CPU/memory spikes can inflate all phases.

Known slow reports

Activity Tracking (worst-case: district-wide, 12-month) has a confirmed ~1.8 min render time. This is a data volume issue, not an infrastructure issue. Its baseline is set accordingly.

---

SLA Thresholds

Tier	Target p95	Warning	Regression
Critical	< 60 s	> 72 s (1.2×)	> 90 s (1.5×)
Standard	< 30 s	> 36 s (1.2×)	> 45 s (1.5×)

These are relative to each report’s own baseline, not absolute. A report with a 90 s baseline only fails at 135 s.

---

Setup Reference

See eshars-ops/testing/ssrs-perf/README.md for:

• Filling in config/environments.json (SQL queries to get test IDs)
• Seeding baselines for the first time
• ADO variable group eshars-perf-test-credentials setup
• Pipeline registration in Azure DevOps