2

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?

|improve this question
  • 3
    In server side trace, you can use User Error Message Event with filter on error <> 0, error <> 1, severity <> 10. Also Grant blogged about using Event Paring to find out application timeout here. Hope that helps you. – Kin Shah Apr 4 '15 at 18:46
  • I knew someone had written about using XEvents to do it, just couldn't remember. – Shawn Melton Apr 4 '15 at 20:16
  • @Kin upvote for Grant's post. I was coming here to link to that! – Mark Sinkinson Apr 4 '15 at 22:27
  • This is good stuff. One thing I forgot to ask is how to get other relevant information when this timeout happens. I already know the timeout happened from my SQLException in the application logs, but I need to find out what other stuff was running at that time...a sort of snapshot of the server during that time period. Almost like an sp_who for the duration of the running query. Is that possible? – Rob Apr 5 '15 at 19:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.