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
4
  • 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, 2015 at 18:46
  • I knew someone had written about using XEvents to do it, just couldn't remember.
    – user507
    Apr 4, 2015 at 20:16
  • @Kin upvote for Grant's post. I was coming here to link to that!
    – Mark Sinkinson
    Apr 4, 2015 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, 2015 at 19:46

0

Reset to default

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.