(SQL) Tip of the Day: List failing Queries using Query Store

  • Article

Today’s Tip…

With modern programming systems such as Entity Framework or LINQ we are able to query databases for information quickly but we do not know the TSQL on the backend. This becomes more of a problem when these queries start failing while they are running. Running the query below will get you the full plan of the query and the query_id which will let you dig in further via Query Store views.

 

select

s.query_id

, r.plan_id

, r.execution_type

, r.first_execution_time

, r.last_execution_time

, r.count_executions

, r.avg_duration

, s.query_plan

from sys.query_store_runtime_stats as r INNER JOIN sys.query_store_plan as s on r.plan_id = s.plan_id where r.execution_type <> '0'

ORDER BY r.last_execution_time