On occasion you will have a sql_handle, plan_handle, query_hash, or query_plan_hash and need to know the query store ID related to it.

One such example is with a deadlock.  Deadlocks contain sql_handles.  A key part of diagnosing deadlocks is viewing the execution plan.  Query store is a good place to view the execution plan and you might need a query ID to find it.

Show me the query

This query performs a lookup by sql_handle.

DECLARE @sql_handle VARBINARY(64)
SET @sql_handle = 0x020000007300341F45B41174F761C557C340C5372CDFE2C50000000000000000000000000000000000000000
SELECT eqs.query_hash, qsp.query_plan_hash, eqs.last_compile_batch_sql_handle,
qsp.query_id, qsp.plan_id
FROM sys.query_store_query eqs INNER JOIN sys.query_store_plan qsp
ON eqs.query_id = qsp.query_id
WHERE eqs.last_compile_batch_sql_handle = @sql_handle

This query performs a lookup by plan_handle. Note this query has a join to sys.dm_exec_query_stats. This is in order to do a lookup by plan_handle. This also means if the plan is currently no in the cache it won’t be found.

DECLARE @plan_handle VARBINARY(64)
SET @plan_handle = 0x03000700DD9310047CD0FF00EAAA000001000000000000000000000000000000000000000000000000000000
SELECT eqs.query_hash, eqs.query_plan_hash, eqs.sql_handle, eqs.plan_handle,
qsp.query_id, qsp.plan_id
FROM sys.dm_exec_query_stats eqs INNER JOIN sys.query_store_plan qsp
ON eqs.query_plan_hash = qsp.query_plan_hash
WHERE eqs.plan_handle = @plan_handle

This query performs a lookup by query_hash.

DECLARE @query_hash BINARY(8)
SET @query_hash = 0x30ECF73F3925D426
SELECT eqs.query_hash, qsp.query_plan_hash, eqs.last_compile_batch_sql_handle,
qsp.query_id, qsp.plan_id
FROM sys.query_store_query eqs INNER JOIN sys.query_store_plan qsp
ON eqs.query_id = qsp.query_id
WHERE eqs.query_hash = @query_hash

This query performs a lookup by query_plan_hash.

DECLARE @query_plan_hash BINARY(8)
SET @query_plan_hash = 0X975B62DF8AF85AD0
SELECT qsp.query_plan_hash,
qsp.query_id, qsp.plan_id
FROM sys.query_store_plan qsp
WHERE qsp.query_plan_hash = @query_plan_hash

Good luck tuning!