Filtered indexes for SQL Server queues and active records

A work table may contain millions of completed records while workers repeatedly ask for a few hundred pending items. An index covering every historical record can help, but it still consumes space and participates in maintenance. A filtered index offers a more direct model: store only the subset the active query needs.

The important quantity is the active working set, not the table size. A queue with 50 million rows and 200 pending items differs fundamentally from one with 20 million pending items after an outage. Design for both normal operation and recovery. An index that looks perfect while the queue is empty can behave differently when a backlog dominates the table.

Match the business predicate exactly

The example models zero as pending and one as completed. The filtered index orders only pending records by creation time and ID. It includes CustomerId because the reader needs it but does not search or sort by it. Running the sample should return records 2 and 3. The tiny dataset demonstrates correctness; it does not provide a meaningful performance benchmark.

CREATE TABLE #Work
(
    WorkId bigint NOT NULL PRIMARY KEY,
    Status tinyint NOT NULL,
    CreatedAt datetime2(0) NOT NULL,
    CustomerId int NOT NULL
);
INSERT #Work VALUES
(1,1,'2024-01-01',10),(2,0,'2024-01-02',20),
(3,0,'2024-01-03',10),(4,1,'2024-01-04',30);

CREATE INDEX IX_Work_Pending
ON #Work(CreatedAt, WorkId)
INCLUDE(CustomerId)
WHERE Status = 0;

SELECT TOP (20) WorkId, CreatedAt, CustomerId
FROM #Work
WHERE Status = 0
ORDER BY CreatedAt, WorkId;

SELECT i.name, p.row_count, p.used_page_count
FROM tempdb.sys.indexes AS i
JOIN tempdb.sys.dm_db_partition_stats AS p
  ON p.object_id=i.object_id AND p.index_id=i.index_id
WHERE i.object_id=OBJECT_ID('tempdb..#Work');

DROP TABLE #Work;

For a real workload, compare index page counts, logical reads, and rows read at typical and maximum queue depths. An index on a status flag alone may still leave a sort or many lookups. Put the ordering columns in the key and include a restrained set of output columns. A large JSON payload usually belongs in a second, bounded fetch after identifying the small set of work IDs.

Required SET options matter when creating and modifying tables with filtered indexes. Standard settings include ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_WARNINGS, ANSI_PADDING, CONCAT_NULL_YIELDS_NULL, and ARITHABORT enabled, with NUMERIC_ROUNDABORT disabled. Confirm the application's connection settings when an index works in an administrator's session but writes fail from the application.

Understand why a useful index is sometimes ignored

The optimizer must prove the index contains every possible qualifying row. A reusable query using WHERE Status = @Status cannot generally rely on an index containing only Status = 0, because the same plan might execute for completed items. Supplying zero during one test does not make a permanently reusable plan safe for every parameter.

A dedicated pending-work query with the literal predicate is often the clearest solution. Another option is statement recompilation when the compile cost is justified. Avoid forcing an index as the first response: an unsafe filtered-index hint can cause plan-generation failures. Verify behavior with the application's actual parameterization and SET options, including forced parameterization if configured.

Filtered statistics describe the subset, which can improve estimates. They still need attention when a small active population changes rapidly. Examine modification patterns and actual-versus-estimated rows before assuming that statistics for the entire table adequately represent pending work. For an IS NULL filter, also check whether the filtered column must be included to obtain the intended access path.

Separate speed from queue correctness

Moving a row from pending to completed removes its filtered-index entry. Reopening it inserts that entry again. The index reduces historical storage but is not free during state transitions. Measure write latency and page contention when many workers update the same oldest range.

The SELECT shown here does not claim work atomically. Two workers can read the same IDs before either changes their status. A production queue needs a transactional claim operation, ownership or lease information, retries, and a rule for workers that crash after claiming. READPAST and update locks require deliberate isolation-level analysis; adding a filtered index does not make those decisions for you.

Review growth during an outage, poison messages that remain pending, and the rate at which completed work leaves the active subset. The operational question is whether the queue drains fast enough without starving normal database traffic. A successful filtered index makes the intended subset cheap to find while leaving claim semantics, recovery, and retention explicit.

Technical references: Microsoft Learn: Filtered indexes · Microsoft Learn: Index design.