Useful SQL Scripts

View query_plan for the just-running query

DECLARE @SearchForSql AS NVARCHAR(MAX) = 'Company'

SELECT UseCounts, Cacheobjtype, Objtype, TEXT, query_plan
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE Text LIKE CONCAT('%',@SearchForSql,'%')
    AND Text NOT LIKE '%-- Self Reference Marker --%'

Row counts of all tables

SELECT o.NAME,
  i.rowcnt 
FROM sysindexes AS i
  INNER JOIN sysobjects AS o ON i.id = o.id 
WHERE i.indid < 2  AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0
ORDER BY 2 desc

Find the timeout in Query Store :

SELECT TOP 50 
 q.query_hash, 
 qt.query_sql_text,
 cast(p.query_plan as xml) query_plan,
 rs.execution_type,
 rs.execution_type_desc,
 rs.count_executions,
 rs.last_execution_time,
 (rs.avg_duration / 1000000.0) avg_duration_seconds, 
 (rs.min_duration / 1000000.0) min_duration_seconds, 
 (rs.max_duration / 1000000.0) max_duration_seconds, 
 (rs.last_duration / 1000000.0) last_duration_seconds
FROM sys.query_store_query AS q
JOIN sys.query_store_plan AS p on q.query_id = p.query_id
JOIN sys.query_store_query_text AS qt on q.query_text_id = qt.query_text_id
JOIN sys.query_store_runtime_stats rs on p.plan_id = rs.plan_id
WHERE rs.execution_type = 3 
ORDER BY rs.last_execution_time DESC;