SQL Performance Intelligence
SQL PerformanceIntelligence™
Use Cases/Waits

From PAGEIOLATCH Waits to a Targeted Index Fix

A growing dashboard query pushed IO waits to the top of the instance. Wait analysis pointed at storage reads, and Index Advisor turned that signal into one focused, low-risk index.

6 min readSQL Server 2019Reporting-heavy workloadQuery Store ONSSD storage

Scenario

A customer-facing dashboard got slower every week as data grew. The instance was not CPU-bound, but query latency kept climbing and users complained the dashboard "felt heavy" by mid-quarter.

Symptoms

  • Dashboard latency rising steadily with data volume, not with user count.
  • CPU comfortable; the bottleneck was clearly elsewhere.
  • A handful of read-heavy queries dominated logical and physical reads.

How We Analyzed It

Each step maps a concrete question to the module and signal used to answer it — all read-only against production.

  1. 1
    Wait Statistics

    Established a baseline and compared snapshots across two weeks.

    Signal: PAGEIOLATCH_SH dominated total wait time and was trending upward.

    Wait Statistics: PAGEIOLATCH_SH dominating the wait profile. (Placeholder image.)
  2. 2
    Query Statistics

    Identified which statements generated the most physical reads.

    Signal: One dashboard aggregation query scanned a large fact table on every load.

  3. 3
    Index Advisor

    Evaluated a covering index and checked drop-safety / overlap with existing indexes.

    Signal: A narrow covering index would convert the scan into a seek with no redundant index conflict.

    Index Advisor: covering index recommendation with safety checks. (Placeholder image.)

Evidence

Evidence — Dominant wait
PAGEIOLATCH_SH accounted for the majority of accumulated wait time and grew ~18% week over week — pointing at data-page reads from storage, not locking or CPU.
Evidence — Hot statement
A single aggregation query produced the bulk of physical reads, repeatedly scanning the fact table because no supporting index covered its predicate and output columns.
Watch out
Index Advisor flags overlap and write-amplification risk before you add an index — review the impact on insert-heavy tables.

Recommendation

Add one narrow covering index that matches the dashboard query predicate and included columns, eliminating the repeated table scan and the IO waits it generated.

  • Create the covering index suggested by Index Advisor (predicate keys + included output columns).
  • Validate the new seek plan in Query Statistics after the next dashboard load.
  • Re-baseline Wait Statistics to confirm PAGEIOLATCH falls and stays down.
-- Review Index Advisor output before applying; size and write impact matter.
CREATE NONCLUSTERED INDEX IX_FactSales_DashboardCover
ON dbo.FactSales (ProductKey, OrderDateKey)
INCLUDE (SalesAmount, Quantity)
WITH (ONLINE = ON, DATA_COMPRESSION = PAGE);
Note
The application never applies changes automatically. Every script above is an example to review and run under your own change-control process.

Outcome

The covering index turned the scan into a seek. PAGEIOLATCH waits dropped sharply and dashboard latency stabilized even as data kept growing.

PAGEIOLATCH_SH share of waits
Top wait→Negligible
Dashboard query logical reads
~2.1 M→~3.4 K
p95 dashboard load
8.4 s→1.1 s

Modules used in this case

Wait StatisticsIndex AdvisorQuery Statistics
Back to Use CasesDownload