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.


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

exec sp_executesql N'SELECT TOP (@results_row_count)
    p.query_id query_id,
    q.object_id object_id,
    ISNULL(OBJECT_NAME(q.object_id),'''') object_name,
    qt.query_sql_text query_sql_text,
    ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions))*0.001,2) total_duration,
    SUM(rs.count_executions) count_executions,
    COUNT(distinct p.plan_id) num_plans
FROM sys.query_store_runtime_stats rs
    JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id
    JOIN sys.query_store_query q ON q.query_id = p.query_id
    JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE NOT (rs.first_execution_time > @interval_end_time OR rs.last_execution_time < @interval_start_time)
GROUP BY p.query_id, qt.query_sql_text, q.object_id
HAVING COUNT(distinct p.plan_id) >= 1
ORDER BY total_duration DESC',N'@results_row_count int,@interval_start_time datetimeoffset(7),@interval_end_time datetimeoffset(7)',@results_row_count=25,@interval_start_time='2021-04-16 10:13:43.0578184 -05:00',@interval_end_time='2021-04-16 11:13:43.0578184 -05:00'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.