Heavier Options When the Query Is Genuinely Expensive

Sometimes the query is well-written, the indexes are right, the plan is stable, and it still takes ninety seconds — because aggregating forty million rows takes ninety seconds. At that point you stop tuning the query and change the structure.

These options are ordered from least to most disruptive. Reach for them in that order.

Indexed views

A materialized view: SQL Server stores the result and maintains it automatically as the base tables change.

CREATE VIEW dbo.vw_MonthlySalesByRegion
WITH SCHEMABINDING                       -- required
AS
SELECT  o.RegionId,
        DATEFROMPARTS(YEAR(o.OrderDate), MONTH(o.OrderDate), 1) AS MonthStart,
        SUM(o.Total)  AS TotalRevenue,
        COUNT_BIG(*)  AS OrderCount      -- COUNT_BIG(*) is required
FROM    dbo.Orders AS o                  -- placeholder table; two-part name required
GROUP BY o.RegionId, DATEFROMPARTS(YEAR(o.OrderDate), MONTH(o.OrderDate), 1);
GO

-- The view becomes materialized when you create a unique clustered index on it
CREATE UNIQUE CLUSTERED INDEX CX_vw_MonthlySalesByRegion
    ON dbo.vw_MonthlySalesByRegion (RegionId, MonthStart);

The aggregation is now computed incrementally at write time. The report reads pre-aggregated rows.

The requirements are strict: SCHEMABINDING, two-part object names, COUNT_BIG(*) if the view aggregates, no outer joins, no subqueries, no DISTINCT, no TOP, no non-deterministic functions, and specific SET options at creation and at every write to the base tables.

The cost: every insert, update and delete on dbo.Orders now also maintains the view. On a write-heavy table that is a real tax on the OLTP workload, and it is paid by users who never look at the report. Measure the write impact, not just the read improvement.

Edition note: on Enterprise Edition the optimizer can use an indexed view automatically even when the query does not name it. On Standard Edition you must reference the view explicitly and add WITH (NOEXPAND):

SELECT RegionId, MonthStart, TotalRevenue
FROM   dbo.vw_MonthlySalesByRegion WITH (NOEXPAND)
WHERE  MonthStart >= @From;

Adding NOEXPAND is good practice on any edition — it guarantees the materialized data is used rather than the view being expanded back into the base query.

Pre-aggregated reporting tables

The unglamorous option that works everywhere and is easy to reason about: a real table containing the summarized data, refreshed on a schedule.

CREATE TABLE dbo.RptMonthlySalesByRegion (
    RegionId      int   NOT NULL,
    MonthStart    date  NOT NULL,
    TotalRevenue  decimal(19,2) NOT NULL,
    OrderCount    bigint NOT NULL,
    RefreshedUtc  datetime2(0)  NOT NULL CONSTRAINT DF_Rpt_Refreshed DEFAULT SYSUTCDATETIME(),
    CONSTRAINT PK_RptMonthlySalesByRegion PRIMARY KEY CLUSTERED (RegionId, MonthStart)
);

-- Refresh procedure, run by SQL Agent nightly
CREATE OR ALTER PROCEDURE dbo.usp_RefreshMonthlySales
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRAN;

    DELETE FROM dbo.RptMonthlySalesByRegion
    WHERE MonthStart >= DATEADD(MONTH, -3, DATEFROMPARTS(YEAR(SYSDATETIME()), MONTH(SYSDATETIME()), 1));

    INSERT INTO dbo.RptMonthlySalesByRegion (RegionId, MonthStart, TotalRevenue, OrderCount)
    SELECT  o.RegionId,
            DATEFROMPARTS(YEAR(o.OrderDate), MONTH(o.OrderDate), 1),
            SUM(o.Total),
            COUNT_BIG(*)
    FROM    dbo.Orders AS o
    WHERE   o.OrderDate >= DATEADD(MONTH, -3, DATEFROMPARTS(YEAR(SYSDATETIME()), MONTH(SYSDATETIME()), 1))
    GROUP BY o.RegionId, DATEFROMPARTS(YEAR(o.OrderDate), MONTH(o.OrderDate), 1);

    COMMIT;
END;

Note the pattern: refresh only a recent window rather than rebuilding history every night. Closed months do not change.

Advantages over an indexed view: no write-path tax on the OLTP tables, no SET-option restrictions, arbitrary complexity in the refresh query, and the refresh runs when you choose. The report reads a small table with a clustered index sized exactly for it.

The trade: the data is as fresh as the last refresh, and you own the refresh job. Expose RefreshedUtc on the report so users know what they are looking at — this prevents a whole category of confused support tickets.

This is usually the right answer for a scheduled or dashboard-style report. It is boring, robust, and easy for the next developer to understand.

Columnstore indexes

Columnstore stores data by column rather than by row, compressed. For analytic queries — scan a large table, touch a few columns, aggregate — it is often 10x faster or better, because it reads only the columns referenced, compresses heavily, and enables batch mode execution.

-- Nonclustered columnstore alongside the existing rowstore structure:
-- the OLTP workload keeps its seeks, the report gets columnstore scans.
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Orders_Reporting
    ON dbo.Orders (OrderDate, RegionId, CustomerId, ProductId, Quantity, Total);

-- Or, for a dedicated reporting/archive table, replace the rowstore entirely:
-- CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSales ON dbo.FactSales;

Good for: large tables (millions of rows minimum — below that the overhead is not worth it), scan-and-aggregate access patterns, few columns referenced per query.

Poor for: single-row lookups by key, frequent small updates, tables under a million rows.

The nonclustered columnstore on an OLTP table is the interesting middle ground: transactional queries continue using the rowstore indexes, reports use the columnstore, and both are maintained. There is a write cost, but it is generally lighter than an indexed view's.

Check compression and health:

SELECT  OBJECT_NAME(rg.object_id) AS table_name,
        i.name                    AS index_name,
        rg.state_desc,            -- OPEN / CLOSED / COMPRESSED
        COUNT(*)                  AS rowgroup_count,
        SUM(rg.total_rows)        AS total_rows,
        SUM(rg.deleted_rows)      AS deleted_rows,
        SUM(rg.size_in_bytes)/1024/1024 AS size_mb
FROM sys.dm_db_column_store_row_group_physical_stats AS rg
JOIN sys.indexes AS i ON i.object_id = rg.object_id AND i.index_id = rg.index_id
GROUP BY rg.object_id, i.name, rg.state_desc
ORDER BY table_name;

Many small OPEN row groups mean data is arriving in small batches and not compressing well — columnstore wants bulk loads of 102,400+ rows per group to compress optimally. A high deleted_rows proportion means the index needs reorganizing.

Separating reporting from OLTP

The most complete answer: run reports somewhere other than the transactional database. No locks, no buffer pool competition, no memory grant contention, and you can index the reporting copy for reporting without any write cost on the primary.

Readable secondary (Always On availability group). Near-real-time, automatically maintained, queries route there via ApplicationIntent=ReadOnly in the connection string. Requires Enterprise Edition and a licence for the secondary if it is used for reads.

Server=sqlha-listener;Database=Sales;ApplicationIntent=ReadOnly;...

One thing to know: secondaries run under snapshot isolation for reads, and long-running report queries on the secondary can delay redo of the primary's log. Watch for that if reports are long.

Database snapshot. A point-in-time, read-only, copy-on-write view of the database. Cheap to create, and a good fit for a report that must be internally consistent as of a moment.

CREATE DATABASE Sales_Snapshot_20260818 ON
    (NAME = Sales_Data, FILENAME = 'D:\Snapshots\Sales_20260818.ss')
AS SNAPSHOT OF Sales;

-- Reports run against Sales_Snapshot_20260818, then:
-- DROP DATABASE Sales_Snapshot_20260818;

The snapshot grows as the source database changes, since modified pages are copied into it — so drop and recreate it on a schedule rather than keeping one indefinitely.

Log shipping / replication to a reporting server. Older, simpler, works on Standard Edition. Latency of minutes, and you can add reporting-specific indexes on the target that do not exist on the primary — which is often the real prize.

A dedicated reporting database or warehouse. ETL on a schedule into a schema shaped for reporting (star schema, columnstore fact tables, pre-aggregated summaries). The most work, and the right answer when reporting is a significant part of the system rather than an afterthought.

Choosing

| Situation | Option | |---|---| | One expensive aggregate, moderate write volume | Indexed view | | Scheduled/dashboard report, nightly freshness fine | Pre-aggregated table | | Large fact table, scan-and-aggregate queries | Columnstore index | | Reports interfering with the transactional workload | Readable secondary or snapshot | | Reporting is a major, ongoing part of the system | Separate reporting database |

Work through Modules 1–5 first. These options all carry ongoing cost — storage, a refresh job, licensing, operational complexity — and it is a poor outcome to build a data warehouse for a report that was slow because of an NVARCHAR parameter.

In practice

When the query is genuinely expensive rather than badly written, look at pre-computation. An indexed view maintains the aggregate automatically but taxes every write to the base table, so weigh that against a pre-aggregated table refreshed nightly, which costs the OLTP workload nothing. For scan-and-aggregate over a large fact table, a nonclustered columnstore lets reports use batch mode while transactional queries keep their rowstore seeks. And if reports are interfering with the application, moving them to a readable secondary removes the contention entirely rather than managing it.