This is something that comes up time and time again. A developer or member of the service desk comes up to me and says “SQL’s timing out, you see what the query is?”.
Well I’ll tell you now that SQL’s not timing out your query, SQL will happily run a query for a year and a day if it has to (OK it might not be happy doing so but it will), the timeout will be coming from you application layer, possibly from within .NET or IIS.
The error that’s being raised is going to probably be something along the lines of..
System.Exception: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Not very helpful, right? And unless you’ve got some sort of monitoring in place, for example you can find these in SQL Sentry by looking for aborted queries, or you’re picking these up in your applicaiton logging, you’re going to have a hard time tracking down what query actually tripped the timeout. As you’re reading this post, I’m going to assume that you haven’t got monitoring.
So how do we go about figuring this out?
One thing I like to do is setup and extended events session to catch aborted queries. I’m looking for aborted queries because SQL has no way of telling if a query was aborted by a user or if it was timed out by an application, to SQL Server both will look the same.
Let’s get on with setting up this Extended Events session, in SSMS, expand Management>Extended Events.
Right Click on Sessions and choose ‘New Session…’
You’re going to want to give your session a name, and I’ll usually check both the ‘Start the event sessions at server startup’ and ‘Start the event session immediately after session creation’ boxes.
Move on to the ‘Events’ screen, the event class that we’re looking for is ‘attention’ so pop ‘attention’ into the Event Library box, you should now see it appear in the box below. Select the attention event class and hit the > to move it to selected events.
Hit the ‘Configure’ button. We now need to choose what other fields we’re going to collect. Hunt through and check all that interest you. I usually choose….
- database name
- nt username
- query hash
- sql text
- username
Next we need to configure where we’re going to catch this info. Head to the ‘Data Storage’ screen, from here you can choose how you want to store the data. I usually got for ‘event_file’. Once you’ve chosen event file, you can give your file a name, max size, enable roll over if you want to do that and configure how many files SQL will keep hold of.
That’s pretty much all we need to do, click ok and our XEvent session will be created and start recording.
Now in SSMS, under Extended Events we should be able to see the new session that we’ve just created. Expand that and right clicking on ‘View Target Data…’ will let us see any aborted queries that we may have captured.
With this data you should be able to track down exactly which queries are causing your timeout headaches.
Thanks for reading and I hope you’ve found this post useful.