How to Exclude Specific Queries from Query Store in SQL Server

by

SQL Server’s Query Store captures all queries by default (when using QUERY_CAPTURE_MODE = ALL) or resource-intensive queries (when using AUTO). However, you may want to exclude certain queries from being tracked. These might include monitoring queries, maintenance scripts, or queries from specific applications that add noise to your performance data.

SQL Server doesn’t provide a built-in mechanism to exclude specific queries from Query Store by query text or pattern. But you can achieve similar results through several approaches.

Using Query Capture Mode to Limit What’s Captured

The most straightforward way to reduce unwanted queries is to use QUERY_CAPTURE_MODE = AUTO instead of ALL:

ALTER DATABASE YourDatabaseName
SET QUERY_STORE (QUERY_CAPTURE_MODE = AUTO);

In AUTO mode, Query Store only captures queries that consume significant resources, automatically filtering out trivial queries like simple lookups or monitoring checks.

Using Custom Query Capture Mode

For more control, use QUERY_CAPTURE_MODE = CUSTOM with specific thresholds:

ALTER DATABASE YourDatabaseName
SET QUERY_STORE = ON
(
    QUERY_CAPTURE_MODE = CUSTOM,
    QUERY_CAPTURE_POLICY = (
        EXECUTION_COUNT = 50,
        TOTAL_COMPILE_CPU_TIME_MS = 100,
        TOTAL_EXECUTION_CPU_TIME_MS = 500,
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS
    )
);

This only captures queries that meet specific criteria. In this example, queries that have executed at least 50 times and consumed meaningful CPU time. Adjust these thresholds to exclude the types of queries you don’t want tracked.

Removing Specific Queries After Capture

If specific queries are already captured and you want to remove them, use sp_query_store_remove_query to remove them based on their query ID.

First, you’ll need to find the ID of the query you want to remove:

SELECT 
    q.query_id,
    qt.query_sql_text
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE qt.query_sql_text LIKE '%your_monitoring_query%';

Then you can remove it based on its ID:

EXEC sp_query_store_remove_query @query_id = 123; -- Replace with your query ID

This deletes the query and all its associated plans and statistics from Query Store.

Removing Multiple Queries in Bulk

To remove several queries matching a pattern:

DECLARE @query_id INT;

DECLARE query_cursor CURSOR FOR
SELECT q.query_id
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE qt.query_sql_text LIKE '%sys.query_store%'  -- Monitoring queries
   OR qt.query_sql_text LIKE '%sp_server_diagnostics%'  -- System procedures
   OR OBJECT_NAME(q.object_id) = 'YourMonitoringProc';  -- Specific stored procedure

OPEN query_cursor;
FETCH NEXT FROM query_cursor INTO @query_id;

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_query_store_remove_query @query_id = @query_id;
    FETCH NEXT FROM query_cursor INTO @query_id;
END

CLOSE query_cursor;
DEALLOCATE query_cursor;

This removes all queries matching your criteria in a single operation.

If certain queries continually appear despite your capture settings, you can schedule this as a SQL Server Agent job to run daily or weekly, keeping your Query Store data clean and focused on queries that matter.

Preventing Queries from Stored Procedures

If you want to exclude all queries from a specific stored procedure, you can’t selectively disable Query Store for just that procedure. Here are some options:

  • Remove the procedure’s queries after they’re captured using the bulk removal approach shown above
  • Move the procedure to a different database that doesn’t have Query Store enabled, if architecturally feasible
  • Use QUERY_CAPTURE_MODE = NONE temporarily when running specific maintenance operations, then re-enable capture afterward:
-- Disable capture temporarily
ALTER DATABASE YourDatabaseName
SET QUERY_STORE (QUERY_CAPTURE_MODE = NONE);

-- Run your maintenance or monitoring queries
EXEC YourMaintenanceProcedure;

-- Re-enable capture
ALTER DATABASE YourDatabaseName
SET QUERY_STORE (QUERY_CAPTURE_MODE = AUTO);

This approach works for scheduled operations where you control the timing.