In SQL Server 2016, is there an optimal/correct way to JOIN sys.query_store_query_text and sys.query_store_query (joinable using query_text_id) with sys.query_store_runtime_stats? I don't see any obvious common ID columns and INFORMATION_SCHEMA.COLUMNS doesn't help either. I'm trying to beef up a simple querystore text search query to include usage and performance detail from the runtime_stats view.
Here's what I'm using currently, which works fine to identify queries containing a search string:
DECLARE @SearchString NVARCHAR(100)=''
SELECT q.query_id, q.query_text_id, t.query_sql_text, object_name(q.object_id) AS parent_object, q.last_execution_time
FROM sys.query_store_query_text t JOIN sys.query_store_query q
ON t.query_text_id = q.query_text_id
WHERE t.query_sql_text LIKE '%' + @SearchString + '%'
I want to add a handful of the stats from sys.query_store_runtime_stats to this, if it's doable.
Best Answer
A "query" one or more "plans", linked by sys.query_store_plan. Each "plan" has multiple rows in sys.query_store_runtime_stats. If you turn on Profiler while running the built-in Query Store reports you can see lots of examples. EG