sqlskills-logo-2015-white.png

Finding Timeout-Prone Code with max_elapsed_time

Consider a scenario where your application timeout setting is 30 seconds and you’ve been asked to proactively start tuning workloads that are at risk of timing out.  There are several different places you can start, and I want to demonstrate an additional option today.

To demonstrate timeout settings, I can configure it in SQL Server Management Studio via the “Execution time-out” setting:

image

Next – let’s create two procedures that have two different WAITFOR thresholds (one which doesn’t time out and another that will):

CREATE PROCEDURE [dbo].[TimeOutProne]
AS
WAITFOR DELAY '00:00:29';
GO

CREATE PROCEDURE [dbo].[TimeOutProneV2]
AS
WAITFOR DELAY '00:00:30';
GO

Execute both of these (starting with the one that succeeds and the one that times out next):

EXEC [dbo].[TimeOutProne];
GO

EXEC [dbo].[TimeOutProneV2];
GO

The first execute succeeds and the second times out with the following error message:

Msg -2, Level 11, State 0, Line 13
Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Now execute the following query against dm_exec_procedure_stats:

SELECT  [database_id] ,
OBJECT_NAME([object_id]) AS [object_name] ,
[max_elapsed_time],
[plan_handle],
[sql_handle]
FROM [sys].[dm_exec_procedure_stats]
WHERE [max_elapsed_time] > 28000000;  -- microseconds, "but only accurate to milliseconds"

Interestingly enough – even though the second query timed out – we still have accumulated max_elapsed_time stats for it prior to timeout:

image

I included plan_handle and sql_handle for later hook-ins – just keeping things minimal for demonstration purposes.

So if you’re seeing timeouts and want to start narrowing down workloads that are getting near or exceeding the timeout, both sys.dm_exec_procedure_stats and sys.sys.dm_exec_query_stats max_elapsed_time can be a helpful piece of information to work with.

2 thoughts on “Finding Timeout-Prone Code with max_elapsed_time

  1. Our developers experience the same.Is there any switch for configuring execution time- out from the application side?Also I have remote query time out set to 0,We are using a express edition of sql server r2 ,and the data file is around 2gb ,I know the limitation of buffer in 1 gb ,but is there any way to prove that the time out is caused due to express edition or some thing else?

    1. Hi Biju,

      Regarding the first question, yes, first check how they are connecting. Connection libraries support query timeout setting – so find out what they are using and then you can narrow it down accordingly.

      Attention events will indicate that a query was canceled by a client either due to an actual cancellation or a query timeout – so that is one method. Regarding the root-cause, there could be a number of reasons – but if you suspect memory pressure or large memory grants, there are associated methods to explore this via DMVs. And if you have the query stats available (like this post demonstrates), you could at minimum look at the memory requirements of the estimated plan.

      Big topic, but a few links that are dated, but still applicable to the subject:
      http://blogs.msdn.com/b/sqlqueryprocessing/archive/2006/11/14/query-execution-timeouts-in-sql-server-part-1-of-2.aspx
      http://blogs.msdn.com/b/sqlqueryprocessing/archive/2006/11/22/query-execution-timeouts-in-sql-server-part-2-of-2.aspx
      http://blogs.msdn.com/b/khen1234/archive/2005/10/20/483015.aspx

      HTH,

      Joe

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.