Question

You manage an Azure SQL Database and users report intermittent slowness during business hours. You need to capture the exact T-SQL text and duration of statements that run longer than 5 seconds, storing the collected data for later analysis with minimal performance overhead. Which approach should you use?

Accepted Answer

Create an Extended Events session filtering the sql_statement_completed event on duration and targeting an event_file on blob storage

Answer

Enable the SQL Server Profiler trace and leave it running throughout business hours

Answer

Query sys.dm_exec_requests repeatedly in a scheduled job to log any request with a high elapsed time

Answer

Turn on Query Store in Read-Write mode and rely on its default capture policy