Performance Tuning

1. Approach — measure first, diagnose with evidence, fix, re-measure

For this phase, I deliberately followed Brent Ozar's performance tuning philosophy — I'm a big fan of his approach and wanted to apply it properly rather than just accepting whatever SQL Server's built-in suggestions told me. His core principle is: measure first with real data, never guess, and treat the "missing index" DMV recommendations as a hint, not a to-do list to blindly execute. Every index has a real, ongoing cost (slower writes, more storage, more maintenance overhead) — so indexing should be based on evidence of real need, not speculation.

2. Captured a real wait-stats baseline

Before touching anything, I checked server-level wait statistics to see what SQL Server was actually spending time waiting on.

Wait stats baseline before indexing

3. Confirmed the heap state with real evidence

Before creating anything, I confirmed climate.daily_observations was genuinely a heap with zero index usage. Result: HEAP, 0 seeks, 7 scans — every query so far had done a full table scan.

Heap confirmed before indexing

4. Designed the indexing strategy around the real access pattern

Phase 1 surfaced two separate missing-index suggestions from SQL Server — one for station_id alone, one for obs_date alone. Rather than blindly creating both, I checked the real expected access pattern first: queries will always filter by station_id and obs_date together, not either column in isolation.

That changes the design: a single composite index serves this pattern far better than two separate single-column indexes, since SQL Server can only efficiently use one nonclustered index per query — with a composite index, it can seek directly using both conditions at once.

Column order: I put station_id first, obs_date second — station_id has 132,501 distinct values (high cardinality, more selective), while obs_date only has ~1,095 possible values across 3 years. Leading with the more selective column gives a better seek target.

Made it the clustered index (not just nonclustered), since the table was a heap and needed real physical row ordering — a stronger fix than layering a nonclustered index on top of an unordered heap.

CREATE CLUSTERED INDEX CIX_daily_observations_station_date
ON climate.daily_observations (station_id, obs_date);

Building this on 113.5 million rows took real time and rewrote the entire table's physical storage.

Clustered index created successfully

5. Measured real impact — Query 1 (station_id filter)

I re-ran Phase 1's exact baseline query to get a clean before/after comparison:

SELECT * FROM climate.daily_observations WHERE station_id = 'USW00094728';

Query 1 statistics after indexing

The execution plan confirmed a single Clustered Index Seek at 100% cost, replacing the old Table Scan + Parallelism plan entirely:

Query 1 execution plan after indexing

6. Measured real impact — Query 2 (obs_date range filter) — an honest non-improvement

I re-ran Phase 1's second baseline query — this one filters only by obs_date, with no station_id at all:

SELECT * FROM climate.daily_observations WHERE obs_date BETWEEN '20230701' AND '20230731';

Query 2 statistics after indexing

The execution plan confirmed why: still a Clustered Index Scan, not a Seek — because obs_date is the second key in the composite index, a query filtering on obs_date alone can't seek directly to matching rows. SQL Server's own missing-index suggestion here recommended a separate nonclustered index on obs_date alone (70.89% estimated impact).

Query 2 execution plan after indexing

7. Design decision: declined the supplementary obs_date index

SQL Server's missing-index suggestion for a standalone obs_date index was tempting to just apply — but I decided against it, channeling Brent Ozar's actual public stance on this kind of situation: the missing-index DMVs are a hint, not a to-do list. They don't know the real workload, and they don't account for the ongoing write-cost penalty every additional index adds to every future insert, forever.

The real expected access pattern (confirmed directly) is always station_id combined with obs_date — Phase 1's isolated date-range query was a synthetic baseline test I built specifically to establish an honest "before" state, not a representative real-world query. Indexing for a pattern that isn't part of the actual workload is exactly the kind of speculative over-indexing his methodology warns against.

Decision: no supplementary index added. If an isolated date-only access pattern turns out to matter in production, Phase 10's monitoring work should reveal it with real, repeated evidence — at which point it'd be a justified addition, not a guess.

Summary

What's Next

With a real indexing strategy in place, validated against the actual expected workload rather than blind application of every suggestion, Phase 5 moves into backup and recovery — full/differential/log backup strategy design and a real point-in-time restore drill.