Designing MS-SQL Indexes for Mixed OLTP and Reporting Workloads

The Core Tension: OLTP vs Reporting

Mixed workloads typically have:

• OLTP characteristics

  • Short, parameterised queries
  • Heavy inserts/updates/deletes
  • Strict latency expectations

• Reporting characteristics

  • Long-running, set-based queries
  • Aggregations over large ranges
  • Complex joins and filters

Indexing for one can easily hurt the other. The goal is not a perfect compromise, but a deliberate split:

1. Protect critical OLTP paths first.
2. Add targeted reporting indexes that minimise write overhead.
3. Use alternative patterns (materialised views, staging tables) where indexes alone are not enough.

Pattern 1: Separate “Transaction” and “Reporting” Indexes

Start by explicitly classifying indexes:

• Transaction-focused indexes

  • Support primary keys and critical lookups
  • Narrow, highly selective
  • Minimal includes

• Reporting-focused indexes

  • Support common aggregations and filters
  • Wider, often with INCLUDE columns
  • Carefully chosen to limit write cost

Example: Orders Table

Assume a typical Orders table:

CREATE TABLE dbo.Orders
(
    OrderID        INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    CustomerID     INT NOT NULL,
    OrderDate      DATETIME2(0) NOT NULL,
    Status         VARCHAR(20) NOT NULL,
    TotalAmount    DECIMAL(18,2) NOT NULL,
    CreatedAt      DATETIME2(0) NOT NULL,
    ModifiedAt     DATETIME2(0) NULL
);

Transaction index (OLTP) – fast lookup by OrderID and CustomerID:

CREATE NONCLUSTERED INDEX IX_Orders_CustomerID_OrderID
ON dbo.Orders (CustomerID, OrderID);

Reporting index – common reporting query:

SELECT  CustomerID,
        YEAR(OrderDate) AS OrderYear,
        SUM(TotalAmount) AS TotalSales
FROM    dbo.Orders
WHERE   OrderDate >= DATEADD(YEAR, -1, SYSUTCDATETIME())
GROUP BY CustomerID, YEAR(OrderDate);

Matching index:

CREATE NONCLUSTERED INDEX IX_Orders_OrderDate_CustomerID
ON dbo.Orders (OrderDate, CustomerID)
INCLUDE (TotalAmount);

• OrderDate first: supports range filter
• CustomerID second: supports grouping
• TotalAmount as INCLUDE: avoids key lookups

Pattern: create explicitly named reporting indexes and document their target queries. If you can’t name the query, don’t create the index.

Pattern 2: Use Covering Indexes Sparingly but Deliberately

Covering indexes (using INCLUDE) are ideal for mixed workloads when:

• The query runs very frequently.
• The table is large.
• The additional columns are read often but rarely updated.

Example: Covering Index for a Dashboard Query

Dashboard query:

SELECT  o.CustomerID,
        c.CustomerName,
        o.Status,
        o.OrderDate,
        o.TotalAmount
FROM    dbo.Orders o
JOIN    dbo.Customers c ON c.CustomerID = o.CustomerID
WHERE   o.Status = 'Completed'
AND     o.OrderDate >= DATEADD(DAY, -30, SYSUTCDATETIME());

Possible index:

CREATE NONCLUSTERED INDEX IX_Orders_Status_OrderDate
ON dbo.Orders (Status, OrderDate)
INCLUDE (CustomerID, TotalAmount);

This reduces IO for the reporting query but adds write overhead on Orders. Before adding it, check:

• Update frequency of Status and OrderDate.
• Number of existing indexes on Orders.
• Actual execution plan: does the query already perform well with existing indexes?

Pattern: cover your top N reporting queries (the ones that matter to users) and leave the rest to scan/seek combinations.

Pattern 3: Clustered Index Choice for Mixed Workloads

Clustered index choice is critical for mixed workloads.

Good candidates

• Monotonically increasing keys

  • Identity integers
  • Sequential GUIDs
  • Avoids page splits and fragmentation

• Business keys that match most joins

  • If almost every query joins on CustomerID, clustering on it can be beneficial.

Example: Fact-like Table in OLTP

For a high-volume OrderLines table:

CREATE TABLE dbo.OrderLines
(
    OrderLineID INT IDENTITY(1,1) NOT NULL,
    OrderID     INT NOT NULL,
    ProductID   INT NOT NULL,
    Quantity    INT NOT NULL,
    UnitPrice   DECIMAL(18,2) NOT NULL,
    LineTotal   AS Quantity * UnitPrice PERSISTED
);

CREATE CLUSTERED INDEX CX_OrderLines_OrderLineID
ON dbo.OrderLines (OrderLineID);

Then add nonclustered indexes for reporting:

CREATE NONCLUSTERED INDEX IX_OrderLines_OrderID_ProductID
ON dbo.OrderLines (OrderID, ProductID)
INCLUDE (Quantity, UnitPrice, LineTotal);

Pattern: choose a clustered index that minimises fragmentation and supports the most critical access path. Use nonclustered indexes for reporting needs.

Pattern 4: Filtered Indexes for Reporting Slices

Filtered indexes can give reporting queries a big boost without indexing the entire table.

Typical use cases

• Recent data (last 90 days).
• Active records (Status = 'Active').
• Non-null values for specific columns.

Example: Recent Orders Only

Reporting query:

SELECT  CustomerID,
        COUNT(*) AS RecentOrders
FROM    dbo.Orders
WHERE   OrderDate >= DATEADD(DAY, -90, SYSUTCDATETIME())
GROUP BY CustomerID;

Filtered index:

CREATE NONCLUSTERED INDEX IX_Orders_Recent_OrderDate_CustomerID
ON dbo.Orders (OrderDate, CustomerID)
WHERE OrderDate >= DATEADD(DAY, -90, SYSUTCDATETIME());

Notes:

• The filter must be deterministic and sargable (avoid GETDATE() inside the definition; use a static boundary and adjust periodically if needed).
• Filtered indexes shine when the filtered subset is much smaller than the full table.

Pattern: use filtered indexes for hot reporting slices instead of indexing the full history.

Pattern 5: Aggregation-Friendly Indexes for Reporting

Reporting queries often aggregate over large ranges. A good index can:

• Reduce IO by supporting ordered scans.
• Enable stream aggregates rather than hash aggregates.

Example: Daily Sales Summary

Query:

SELECT  CAST(OrderDate AS DATE) AS OrderDay,
        SUM(TotalAmount) AS TotalSales
FROM    dbo.Orders
GROUP BY CAST(OrderDate AS DATE);

Helpful index:

CREATE NONCLUSTERED INDEX IX_Orders_OrderDate_TotalAmount
ON dbo.Orders (OrderDate)
INCLUDE (TotalAmount);

If you regularly aggregate by CustomerID and day:

SELECT  CustomerID,
        CAST(OrderDate AS DATE) AS OrderDay,
        SUM(TotalAmount) AS TotalSales
FROM    dbo.Orders
GROUP BY CustomerID, CAST(OrderDate AS DATE);

Then consider:

CREATE NONCLUSTERED INDEX IX_Orders_CustomerID_OrderDate
ON dbo.Orders (CustomerID, OrderDate)
INCLUDE (TotalAmount);

Pattern: align index key order with GROUP BY and WHERE clauses, not just the columns involved.

Anti-Pattern 1: Indexing Every Column “Just in Case”

Common symptoms:

• Dozens of indexes per table.
• Multiple overlapping indexes with slight variations.
• Slow inserts/updates and heavy lock contention.

Why it hurts:

• Every write updates all relevant indexes.
• Query optimiser has too many similar options, leading to suboptimal plans.

How to avoid it

• Limit each table to a small set of well-justified indexes.
• Remove indexes that are almost never used.
• Consolidate overlapping indexes into a single, better-designed one.

Use DMVs to check usage:

SELECT  DB_NAME(database_id) AS DatabaseName,
        OBJECT_NAME(i.object_id) AS TableName,
        i.name AS IndexName,
        s.user_seeks,
        s.user_scans,
        s.user_lookups,
        s.user_updates
FROM    sys.dm_db_index_usage_stats s
JOIN    sys.indexes i
        ON s.object_id = i.object_id
       AND s.index_id = i.index_id
WHERE   s.database_id = DB_ID();

Look for indexes with high user_updates and very low user_seeks/scans.

Anti-Pattern 2: Ignoring Write Cost of Reporting Indexes

A reporting index that speeds up one dashboard but slows down all OLTP writes is a bad trade.

Watch out for:

• Wide indexes with many key columns.
• Indexes on frequently updated columns.
• Multiple indexes that share the same leading key.

Practical checks before adding an index

• Estimate write amplification: how many indexes will be touched per insert/update?
• Check if the reporting query could be handled by:
  • A slightly modified existing index.
  • A filtered index on a subset.
  • A materialised summary table refreshed on a schedule.

Anti-pattern: adding a new index for every slow report instead of redesigning the report or consolidating indexes.

Anti-Pattern 3: Misaligned Index Keys and Query Patterns

Indexes are often created with the right columns but wrong order.

Typical mistakes:

• Leading key is low-selectivity (e.g. Status) with a highly selective column second.
• Index doesn’t match the actual filter pattern.

Example

Query:

SELECT  *
FROM    dbo.Orders
WHERE   CustomerID = @CustomerID
AND     OrderDate >= @FromDate
AND     OrderDate < @ToDate;

Bad index:

CREATE NONCLUSTERED INDEX IX_Orders_Status_CustomerID_OrderDate
ON dbo.Orders (Status, CustomerID, OrderDate);

Better index:

CREATE NONCLUSTERED INDEX IX_Orders_CustomerID_OrderDate
ON dbo.Orders (CustomerID, OrderDate);

Anti-pattern: designing indexes around “business meaning” of columns instead of how queries actually filter and join.

Practical Workflow: Designing Indexes for a Mixed Workload

A simple workflow that works well in real projects:

1. Identify critical OLTP queries

   • Login flows, main data-entry screens, key API endpoints.
   • Ensure they have efficient seeks and minimal key lookups.

2. Identify top reporting queries

   • BI dashboards, scheduled reports, ad-hoc queries that run daily.
   • Focus on the few that dominate load.

3. For each query, map to an index

   • List tables involved.
   • List WHERE, JOIN, GROUP BY, ORDER BY columns.
   • Propose a single index per table that can support multiple queries.

4. Validate with actual execution plans

   • Check index usage.
   • Confirm reduced IO and CPU, not just different operators.

5. Monitor and iterate

   • Use DMVs to track index usage and missing index suggestions.
   • Revisit once workload changes (new features, new reports).

One Concrete Takeaway

Pick one heavily used reporting query today, capture its actual execution plan, and design exactly one index that supports both that query and at least one OLTP query on the same table. Deploy it, measure the impact on both read and write performance, and only then decide if you truly need more indexes for that table.