2

This is a question that haunts me for ages.

every job I got I see plenty of people complaining for the database administrators about "timeout" errors:

System.Data.SqlClient.SqlException: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Since it's a timeout I can never track why it happened. I can see high CPU sometimes, sometimes intensive queries but I really would like to points the exact cause of Timeouts.

Racer SQL
  • 7,386
  • 14
  • 69
  • 128
  • 2
    A timeout only happens when some intermediary in a data stream is no longer prepared to wait for an answer. This intermediary can sometimes be a database server when it has to retrieve information from somewhere else. If you "can never track why it happened", there is a problem with your error handling and reporting. Most database servers can log slow SQL. – Gerard H. Pille Oct 13 '21 at 11:19
  • 1
    Take a look at my answer here for details around what needs to be modified in your client app(s) for this "error" to be handled. – Hannah Vernon Oct 13 '21 at 12:33

1 Answers1

2

Just to clarify a minor thing, SQL Server doesn't usually create timeouts, rather the calling client is what creates the timeout when it exceeds its wait time threshold while waiting for a response from SQL Server. This threshold is configured by the calling client itself, and can vary from one application to another. The only exception to this, is when SQL Server is accessing data remotely across a Linked Server. Then SQL Server is the calling client in that case, and can issue a timeout if you configured your Linked Server to have a timeout threshold.

That being said, it sounds like you're just looking for general ways to debug long running query issues, especially with step one of identifying what those queries are and why they are running slowly.

One of the best tools for this, in my opinion, is Adam Machanic's sp_WhoIsActive. This procedure tells you every query running on your server at any point in time, with information on how long it's been running for, if it's being blocked by another query, what the last wait type was, what the CPU usage is, the query text itself, and more.

This is basically my first step whenever I need to look into slow running queries. I actually log the results of this procedure every 10 seconds to a Table, so I can retroactively look into performance issues as well.

Another awesome and free tool is Brent Ozar's sp_Blitz (and the correlating "Blitz" procedure suite) which gives a lot more in depth information on potential issues with your server, your queries, and any configuration issues. This is a more advanced tool, so I'll use it secondarily for answering the question "why is a query running slowly?" after using the aforementioned sp_WhoIsActive.

And finally, a third tool I'll recommend that I've recently added to my arsenal, is Erik Darling's sp_PressureDetector. This tool is great for quickly identifying CPU and Memory pressure issues on the spot. This provides more high level information which can help you determine if your server is under-provisioned or if you're having serious issues with the CPU or Memory which can correlate to query performance problems.

J.D.
  • 37,483
  • 8
  • 54
  • 121