SQL Performance & Data Modeling

01 Problem — example scenario

A SQL Server–based healthcare claims system suffers from slow queries, inconsistent business logic across reports, and nightly ETL jobs that miss their SLA windows. Analysts wait minutes for results, and the same metric returns different values depending on which report they open.

02 Constraints

• The platform is SQL Server end-to-end — no Oracle, and no migration to a new engine.
• Dimension history must be preserved (SCD Type 2) for point-in-time claims accuracy.
• Nightly ETL must finish inside a fixed maintenance window.
• Business logic must be centralized so self-service reports stay consistent.

03 Diagnosis — SQL Profiler & execution plans

The first move is measurement, not guessing: use SQL Profiler to capture slow queries, deadlocks, and missing-index events, then analyze execution plans to pinpoint key lookups, table scans, and join inefficiencies.

Sample capture. Click a column header to sort; click a query to see its plan.

04 Dimensional model — star schema

Two conformed fact tables share the same dimensions, so claims and encounters reconcile against one set of members, providers, and dates. The design keeps a star schema for query speed rather than a normalized snowflake, and folds natural roll-ups — Provider → Group → Network, Date → Month → Quarter → Year — into dimension hierarchies the semantic model can drill.

05 Solution demonstration

The redesign centers on set-based T-SQL, targeted indexing, and partitioning:

• Rewritten stored procedures using window functions and MERGE for SCD Type 2 loads.
• Filtered indexes & partitioning on large facts, aligned to service-date access patterns.
• Materialized/ indexed views for heavy recurring joins.
• Governed SQL views that centralize business logic for Power BI self-service.
• Portable SQL — the same patterns carry to Oracle (PL/SQL), PostgreSQL, and MySQL when sources span engines.

-- SCD Type 2 upsert with MERGE: close old rows, insert new versions
MERGE dbo.DimProvider AS tgt
USING stg.Provider AS src
    ON  tgt.ProviderBK = src.ProviderBK
    AND tgt.IsCurrent   = 1
WHEN MATCHED AND tgt.HashDiff <> src.HashDiff THEN
    UPDATE SET tgt.IsCurrent = 0, tgt.EndDate = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProviderBK, ProviderName, HashDiff, StartDate, IsCurrent)
    VALUES (src.ProviderBK, src.ProviderName, src.HashDiff, GETDATE(), 1);

-- a partition-switch load for FactClaims by ServiceDate follows the same shape

06 Before / after — execution plan comparison

The comparison shows expensive scans and key lookups collapsing into seeks after indexing and model changes.

07 Outcome — demonstration

This example shows how SQL tuning and modeling can, in practice:

• Reduce query latency by turning scans and key lookups into seeks.
• Improve ETL reliability so nightly loads finish inside the SLA window.
• Enable self-service analytics on a clean, conformed dimensional model.
• Provide consistent, validated business logic through governed SQL views.