How to optimize SQL query execution plans for sub-second responses on multi-million row tables?

Use a combination of proper indexing, predicate push‑down, and query‑plan hints to keep the optimizer on a fast, index‑seek path.

Step‑by‑step

1. Profile the workload – run SET STATISTICS IO, TIME ON; and capture elapsed time and logical reads.
2. Identify the hot predicates – focus on columns with > 90 % selectivity on the fact table.
3. Create covering indexes that include all filtered and projected columns, e.g.:

CREATE INDEX IX_Fact_Sales_DateProd
ON dbo.FactSales (SaleDate, ProductKey)
INCLUDE (Quantity, NetAmount);

4. Enable filtered indexes for sparse values:

CREATE INDEX IX_Fact_Sales_Active
ON dbo.FactSales (IsActive)
WHERE IsActive = 1;

5. Force a parallel plan only when row count > 1 M and server has ≥ 8 cores:

OPTION (MAXDOP 8);

6. Use query‑plan hints sparingly to lock in a hash‑aggregate or merge‑join when statistics are stale:

SELECT … FROM dbo.FactSales
WHERE SaleDate BETWEEN @Start AND @End
OPTION (HASH JOIN, MERGE UNION);

7. Refresh statistics after bulk loads: UPDATE STATISTICS dbo.FactSales WITH FULLSCAN, ALL;

Index type comparison

| Index | Seek cost | Update cost | Typical use |
|-------|-----------|-------------|-------------|
| B‑Tree (non‑clustered) | Low | Medium | Point lookups, range filters |
| Columnstore | Very low for scans | High | Large aggregations, analytics |
| Filtered | Low | Low‑Medium | Sparse predicates |

Follow the checklist: profiling → selective columns → covering/filtered indexes → appropriate MAXDOP → targeted hints → stats refresh. This pipeline consistently yields sub‑second response on tables with tens of millions of rows.