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

To demonstrate timeout settings, I can configure it in SQL Server Management Studio via the \u201cExecution time-out\u201d setting:<\/p>\n

<\/a><\/p>\n

Next \u2013 let\u2019s create two procedures that have two different WAITFOR thresholds (one which doesn\u2019t time out and another that will):<\/p>\n

\r\nCREATE PROCEDURE [dbo].[TimeOutProne]\r\nAS\r\nWAITFOR DELAY '00:00:29';\r\nGO\r\n\r\nCREATE PROCEDURE [dbo].[TimeOutProneV2]\r\nAS\r\nWAITFOR DELAY '00:00:30';\r\nGO\r\n<\/pre>\n
Execute both of these (starting with the one that succeeds and the one that times out next):<\/p>\n
\r\nEXEC [dbo].[TimeOutProne];\r\nGO\r\n\r\nEXEC [dbo].[TimeOutProneV2];\r\nGO\r\n<\/pre>\n
The first execute succeeds and the second times out with the following error message:<\/p>\n
Msg -2, Level 11, State 0, Line 13
\nTimeout expired.\u00a0 The timeout period elapsed prior to completion of the operation or the server is not responding.<\/p><\/blockquote>\n
Now execute the following query against dm_exec_procedure_stats:<\/p>\n
\r\nSELECT\u00a0 [database_id] ,\r\nOBJECT_NAME([object_id]) AS [object_name] ,\r\n[max_elapsed_time],\r\n[plan_handle],\r\n[sql_handle]\r\nFROM [sys].[dm_exec_procedure_stats]\r\nWHERE [max_elapsed_time] > 28000000;\u00a0 -- microseconds, "but only accurate to milliseconds"\r\n<\/pre>\n
Interestingly enough \u2013 even though the second query timed out \u2013 we still have accumulated max_elapsed_time stats for it prior to timeout:<\/p>\n
<\/a><\/p>\n
I included plan_handle and sql_handle for later hook-ins \u2013 just keeping things minimal for demonstration purposes.<\/p>\n
So if you\u2019re 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.<\/p>\n","protected":false},"excerpt":{"rendered":"
Consider a scenario where your application timeout setting is 30 seconds and you\u2019ve been asked to proactively start tuning workloads that are at risk of timing out.\u00a0 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 […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28],"tags":[],"class_list":["post-1180","post","type-post","status-publish","format-standard","hentry","category-performance"],"yoast_head":"\n