Running SQL Server 2008 R2. We have some queries that typically take less than one second to complete, but, occasionally, they are taking longer than 30 seconds which causes a client timeout.
Unfortunately, these queries are not showing up in our slow query trace because there is no sp_statement_completed or sql_statement_completed event -- it seems they are not completing on the server side.
Is there a way, preferably using extended events, to trace queries that start, but never complete? I don't want to trace everything and generate a huge amount of logs because this is something I want to leave running for a while since the problem is so intermittent.
Update
I just noticed Event Pairing targets which I think could work. If I read correctly, it would capture any start event that doesn't have a matching end event. Before I mark that as an answer, does anyone have experience tracking this? Is there a lot of overhead?
User Error Message Event
with filter onerror <> 0, error <> 1, severity <> 10
. Also Grant blogged about using Event Paring to find out application timeout here. Hope that helps you.