Query Store Capture Modes: ALL, AUTO and CUSTOM

Query Store fills with statements that nobody will investigate. A suitable capture mode keeps useful evidence without collecting every disposable query shape. Check what the store is doing before changing its policy.

Inspect the Current State and Policy

I read the actual Query Store state before interpreting missing history. Requested settings and current behavior can differ. A store that reached a limit can stop accepting new evidence.

Run the following query in the database you are investigating. It shows requested and actual state beside the policy. The storage figures provide context for the capture decision. On my SQL Server 2025 instance, a brand new database already reported READ_WRITE with AUTO capture.

CUSTOM policy columns exist in recent SQL Server versions supporting that mode. Use that supported version for the complete query. An older installation needs a narrower inspection query.

Don't begin by clearing Query Store to make space. Existing plans and runtime history are valuable evidence. Review retention and capture policy before removing that evidence.

The capture mode decides which query shapes enter the store. It isn't the same as the runtime statistics aggregation interval. Keep those configuration decisions separate.

SELECT desired_state_desc, actual_state_desc, readonly_reason,
       query_capture_mode_desc, current_storage_size_mb, max_storage_size_mb,
       stale_query_threshold_days, interval_length_minutes,
       capture_policy_stale_threshold_hours, capture_policy_execution_count,
       capture_policy_total_compile_cpu_time_ms,
       capture_policy_total_execution_cpu_time_ms
FROM sys.database_query_store_options;

Use the ALL Capture Mode for Investigations

ALL captures every eligible query submitted to the database. It helps when rare statements matter to the investigation. It also collects short-lived shapes produced by literal-heavy application text.

That additional evidence consumes storage and collection work. A busy application with many unique statements deserves particular caution. More captured text doesn't automatically mean more useful diagnosis.

ALL is a reasonable temporary choice for a focused test. Record when you enabled it and why. Set an end point for returning to the normal workload policy.

A capture change doesn't guarantee every historical statement appears retroactively. Query Store starts collecting according to its current rules. It cannot reconstruct queries that weren't captured yesterday.

The command below belongs in a disposable test database or an approved configuration change. It enables read-write collection and chooses ALL. Inspect the actual state afterward rather than assuming the request succeeded operationally.

ALTER DATABASE CURRENT SET QUERY_STORE = ON
( OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = ALL );
SELECT actual_state_desc, query_capture_mode_desc FROM sys.database_query_store_options;

Prefer the AUTO Capture Mode for OLTP

AUTO selects relevant queries based on execution frequency and resource use. It filters some insignificant one-off statements. That makes it a sensible starting policy for a busy OLTP database.

The selection is automatic rather than your own explicit threshold contract. If a particular rare query must be investigated, verify that it is captured. A missing entry doesn't prove the application never sent it.

I start with AUTO when the team hasn't identified a reason for custom thresholds. Then I inspect useful query coverage and storage growth. The workload supplies the next decision.

AUTO doesn't correct an application that generates excessive unique text. Parameterization is still an application and query design concern. Capture policy controls evidence collection, not the original query submission.

Use the next command to select AUTO in the test database. Keep the current retention and size settings unless those are part of the test. Changing several limits at once makes the effect harder to explain.

ALTER DATABASE CURRENT SET QUERY_STORE
( QUERY_CAPTURE_MODE = AUTO );

Use CUSTOM for an Explicit Entry Rule

CUSTOM lets you set an evaluation window and relevance thresholds. Execution count is one criterion. Total compilation CPU and total execution CPU provide resource-based criteria.

The policy evaluates these criteria over its stale capture threshold window. Reaching a qualifying criterion can make a statement eligible. It isn't a rule requiring every threshold to be exceeded together.

The sample uses a twenty-four-hour window and explicit starter values. These are configuration inputs, not measured results or a universal recommendation. Adjust them only after reviewing your own coverage.

Compilation and execution CPU are different costs. A rare statement with expensive compilation still deserves evidence. A cheap statement repeated heavily deserves attention through its count.

A capture mode becomes useful when its thresholds reflect your investigation needs. Document why each custom value was chosen. A copied number isn't an operational policy.

ALTER DATABASE CURRENT SET QUERY_STORE
(
    QUERY_CAPTURE_MODE = CUSTOM,
    QUERY_CAPTURE_POLICY =
    (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
    )
);

Distinguish Evaluation From Retention

STALE_CAPTURE_POLICY_THRESHOLD controls policy evaluation for uncaptured statements. STALE_QUERY_THRESHOLD_DAYS controls retention cleanup for stored query information. Similar names don't make those settings interchangeable.

MAX_STORAGE_SIZE_MB limits the store's allocation budget. Size-based cleanup decides whether automatic cleanup acts near that limit. Neither setting defines which rare query matters to your business.

Read current_storage_size_mb repeatedly to observe growth over time. One snapshot doesn't establish a growth rate. Save its collection time when comparing intervals.

Also examine the number of query shapes entering the store. A deployment that starts generating literal text can change growth quickly. The right response includes the application team.

Which rare statement would you regret missing during an outage? Test that statement against the policy. Capture settings should serve the investigation rather than merely keep a size chart flat.

Understand What Capture Mode NONE Leaves Behind

NONE stops capturing new query shapes. Query Store can continue collecting statistics for queries already captured. It isn't the same as setting the whole store OFF.

That makes NONE useful only for a deliberate collection policy. It can leave the team blind to a newly introduced query. Don't use it as an unexplained emergency space fix.

Existing records remain available according to retention and cleanup rules. They don't become permanent merely because entry is closed. A later investigation still needs the correct time coverage.

The following command demonstrates the mode on the test database. Return to the recorded prior capture setting afterward. Keep the store's actual state in the readback.

A read-only store is another condition entirely. It stops writing because of its state or limits. Inspect readonly_reason rather than blaming the capture policy alone.

ALTER DATABASE CURRENT SET QUERY_STORE ( QUERY_CAPTURE_MODE = NONE );
SELECT desired_state_desc, actual_state_desc, query_capture_mode_desc, readonly_reason
FROM sys.database_query_store_options;

Review Coverage After the Change

Compare normal business hours with the same reporting interval after the change. Look for required query identities and their runtime history. Also inspect storage growth and collection state.

I make small threshold changes when CUSTOM needs adjustment. That makes lost coverage easier to detect and reverse. A drastic jump can remove precisely the occasional expensive statement you needed.

A sensible capture mode balances evidence and collection cost. Keep AUTO as the starting candidate for ordinary busy OLTP work. Use ALL or CUSTOM when a documented investigation requirement justifies the difference.

Related reading on this blog: Finding the Worst Query in Five Minutes With Query Store and Query Store Wait Stats: Why a Query Was Slow, Not Just That It Was.

A capture policy is not a storage shortcut alone, it is a decision about which future queries leave evidence.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.