A sales report should not make every current order wait for its totals. Real-time operational analytics can use a filtered columnstore index on closed orders while active orders keep their rowstore access path.
Separate Active Work for Real-Time Operational Analytics
An orders table serves different workloads at once. The application changes individual orders. Reports group substantial portions of their history. One rowstore access path does not automatically serve both patterns efficiently.
A nonclustered columnstore index keeps selected columns in a format suited to analytical scans and aggregates. The underlying table remains a rowstore. A filter can exclude hot rows that do not yet belong to the reporting population.
I start by defining what closed means in the application. An index filter must follow a stable business rule. A status label that changes meaning between teams creates a reporting problem before it creates an indexing problem.
The demonstration uses SQL Server 2016 or later for an updateable filtered nonclustered columnstore index and COMPRESSION_DELAY. Its data generator requires SQL Server 2022 with compatibility level 160 or higher. Check both prerequisites on the test database.
Closed rows are still maintained when their indexed values change. The filter reduces the population, but it does not make included rows read-only. Updates moving rows across the filter boundary also require index maintenance.
Create an Orders Table With a Closed Population
Run this setup in a disposable database. The requested data volume is illustrative input. It is not a measured count, elapsed time, or compression result from an actual server run.
CREATE TABLE dbo.OperationalOrders
(
OrderID int NOT NULL PRIMARY KEY,
OrderDate date NOT NULL,
StatusCode tinyint NOT NULL,
ProductID int NOT NULL,
Amount decimal(12,2) NOT NULL
);
INSERT dbo.OperationalOrders
SELECT value, DATEADD(day, value % 365, CONVERT(date, '20250101')),
CASE WHEN value % 10 = 0 THEN 1 ELSE 2 END,
1 + value % 100, CONVERT(decimal(12,2), value % 500)
FROM GENERATE_SERIES(1, 300000);
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_OperationalOrders_Closed
ON dbo.OperationalOrders(OrderDate, StatusCode, ProductID, Amount)
WHERE StatusCode = 2
WITH (COMPRESSION_DELAY = 60);StatusCode 2 represents closed orders in this example. StatusCode 1 represents active orders. Those values are a demonstration convention, so replace them with your application's actual and documented rule.
Include the columns the analytical query needs. Adding every wide text column increases storage and maintenance without guaranteeing a useful scan. Review data types and columnstore restrictions before applying the same definition to a real table.
The rowstore primary key continues to support an individual order lookup. You can also retain suitable rowstore indexes for transactional queries. The columnstore adds another path, rather than replacing the underlying table's layout.
Give the Report a Matching Predicate
Enable the actual execution plan and run an aggregate over closed orders. Its WHERE clause makes the filtered population explicit. That gives the optimizer a valid opportunity to use the filtered index.
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT ProductID, SUM(Amount) AS ClosedSales,
COUNT_BIG(*) AS ClosedOrderTotal
FROM dbo.OperationalOrders
WHERE StatusCode = 2
AND OrderDate >= '20250701'
AND OrderDate < '20251001'
GROUP BY ProductID;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;Inspect the access operator and its Actual Execution Mode. A columnstore scan supports batch-mode work, but read the actual properties before claiming the plan used it. Other operators and costing decisions influence the final shape.
The report intentionally excludes active orders. If users need all orders, this filtered index alone does not represent the complete answer. Combining active and closed populations requires a query that preserves both without double counting.
Parameterized status filters need attention. A reusable plan cannot always assume the parameter matches the filtered index condition. Check the application's actual query form and plan rather than testing only a hard-coded status in SSMS.
An index cannot settle an argument about which orders count as sales. Confirm the reporting definition with the people reading the totals. The predicate should be a visible rule, not a performance trick that changes the answer.
Inspect Rowgroups Instead of Assuming Compression
Columnstore rows enter rowgroups with different states. Small or changing populations can reside in delta stores before compression. Read the rowgroup metadata to understand the index's present layout.
SELECT index_id, partition_number, row_group_id,
state_desc, total_rows, deleted_rows,
size_in_bytes, trim_reason_desc
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID(N'dbo.OperationalOrders')
ORDER BY index_id, partition_number, row_group_id;Compare state_desc with the report plan and workload pattern. A healthy definition does not guarantee every row is already in a compressed group. The current rowgroup population determines how much work the scan performs.
COMPRESSION_DELAY postpones compression of eligible closed delta rowgroups. It can reduce repeated work when recently changed rows continue changing. It does not delay index visibility for an hour, and it does not make reports ignore recent qualifying writes.
Look at deleted_rows in compressed groups when updates and deletes are frequent. Those entries affect scan efficiency until maintenance handles them. Review rowgroup quality over time instead of taking one snapshot as permanent evidence.
Measure the Extra Work on Writes
A qualifying insert or update maintains another index. A status transition can move a row into or out of the filtered population. Measure representative operations with the index present before deciding the reporting improvement pays for that cost.
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
BEGIN TRANSACTION;
UPDATE dbo.OperationalOrders
SET StatusCode = 2
WHERE OrderID BETWEEN 10 AND 100 AND StatusCode = 1;
ROLLBACK TRANSACTION;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;This rollback keeps the sample business values unchanged, but the transaction still performs work and takes locks. Run it only on the disposable test. A rollback is not permission to generate heavy production activity.
I compare write behavior before recommending the additional index. A report that gets faster while the order-entry path gets slower needs a business decision. Read and write costs belong in the same review.
Also inspect the transaction log and concurrency during a representative load. Columnstore maintenance competes for memory and CPU. The absence of an application code change does not make the index operationally free.
When Real-Time Operational Analytics Needs a Reporting Copy
Can the primary database carry both workloads during its busiest period? A filtered index helps when the analytical population is suitable and write overhead remains acceptable. It does not create additional hardware capacity.
A heavy transactional table still needs careful testing of analytical scans. Large reports can compete for memory, CPU, and storage even when their plan uses columnstore. Batch mode makes work more efficient without removing those shared resources.
If reporting regularly interferes with writes, evaluate a separate reporting copy with a defined freshness requirement. That introduces its own synchronization and operational work. Choose it because the workload needs isolation, not because the filtered-index example was convenient.
Keep the report predicate, rowgroup snapshots, read plans, and write measurements together. Those records explain whether the chosen closed population remains a useful boundary as the application evolves.
Real-time operational analytics joins reporting needs to a changing operational table. Measure both scans and writes before declaring real-time operational analytics successful for that workload.
Related reading on this blog: Columnstore Rowgroup Health: Finding Small and Open Rowgroups and Creating Clustered ColumnStore with InMemory OLTP Tables: Operational Analytics.
A filtered columnstore is not free reporting capacity, it is another access path whose write cost must earn its place.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
