Question

A developer reports that a specific stored procedure in an Azure SQL Database intermittently runs slowly, but by the time you investigate the session has already completed. You want to examine the actual runtime statistics (rows read, actual row counts, and operator-level execution time) for the slow executions without adding trace overhead or asking the developer to reproduce the problem. Query Store is already enabled. What should you do?

Accepted Answer

Use the Query Store 'Top Resource Consuming Queries' view to locate the query, then inspect the collected execution plans and their captured runtime statistics for the slow intervals.

Answer

Enable the Live Query Statistics feature in SQL Server Management Studio and wait for the next slow execution to capture the plan in real time.

Answer

Query sys.dm_exec_requests joined with sys.dm_exec_query_plan to retrieve the currently executing plan for the procedure.

Answer

Create an Extended Events session filtered on the procedure to capture the actual execution plan on every run.