Why can the same Query Text in the Query Store have multiple Query_IDs?

In Query Store, query_id is not just based on the query text. It is derived from a broader concept of a query signature, which includes additional attributes beyond the literal SQL text.

That’s why you can see the same query_sql_text mapped to multiple query_ids.

✅ Core Reason

A query_id is generated based on:

• Normalized query text (parameterized form)
• Context settings (set_options)
• Database settings / environment affecting compilation

So even if the raw text looks identical, SQL Server treats them as different queries internally.

🔍 Main Cause

1. Different SET Options (set_options)

SELECT DB_NAME(), query_id, OBJECT_NAME(Q.object_id), T.query_sql_text, q.last_execution_time, T.query_text_id, Q.context_settings_id

FROM sys.query_store_query Q

JOIN sys.query_store_query_text T ON Q.query_text_id = T.query_text_id

WHERE Q.object_id = OBJECT_ID('<Stored Procedure/Trigger name>')

AND T.query_sql_text LIKE '%<query_text>%'

ORDER BY q.last_execution_time DESC;

GO

SELECT GETUTCDATE();

SELECT * FROM sys.query_context_settings WHERE context_settings_id IN (1, 6);

DECLARE @set_options INT = 4347; -- change value

SELECT name, value

FROM (VALUES

    ('ANSI_PADDING', 1),

    ('Parallel Plan', 2),

    ('FORCEPLAN', 4),

    ('CONCAT_NULL_YIELDS_NULL', 8),

    ('ANSI_WARNINGS', 16),

    ('ANSI_NULLS', 32),

    ('QUOTED_IDENTIFIER', 64),

    ('ANSI_NULL_DFLT_ON', 128),

    ('ANSI_NULL_DFLT_OFF', 256),

    ('NO_BROWSETABLE', 512),

    ('ARITHABORT', 1024),

    ('NUMERIC_ROUNDABORT', 2048),

    ('DATEFIRST', 4096),

    ('DATEFORMAT', 8192),

    ('LANGUAGE', 16384)

) AS t(name, value)

WHERE (0x000010FB & value) = value;

SELECT name, value

FROM (VALUES

    ('ANSI_PADDING', 1),

    ('Parallel Plan', 2),

    ('FORCEPLAN', 4),

    ('CONCAT_NULL_YIELDS_NULL', 8),

    ('ANSI_WARNINGS', 16),

    ('ANSI_NULLS', 32),

    ('QUOTED_IDENTIFIER', 64),

    ('ANSI_NULL_DFLT_ON', 128),

    ('ANSI_NULL_DFLT_OFF', 256),

    ('NO_BROWSETABLE', 512),

    ('ARITHABORT', 1024),

    ('NUMERIC_ROUNDABORT', 2048),

    ('DATEFIRST', 4096),

    ('DATEFORMAT', 8192),

    ('LANGUAGE', 16384)

) AS t(name, value)

WHERE (0x000000FB & value) = value;

The outcome presented below indicates that the values of query_text_id and the object_name for these two query_store entries are identical, which implies that these two query_store entries essentially refer to the same query within the same object (stored procedure/trigger).

The reason there are two query_ids for the same query is that, as indicated by the context_settings_id value, the two query_store entries are executed under different context settings (SET OPTIONs).