August 10, 2016 at 3:14 pm
Hi Experts,
Is there a way to capture Timeout errors using Extended Events.
Just incase if anyone had already worked on such issues, pl share an example on how to do that.
Errors : Unable to load data from Source table to Target table Timeout expired.The timeout period elapsed prior to completion of the operation or the server is not responding.
Thanks,
Sam
August 10, 2016 at 7:13 pm
http://www.scarydba.com/2015/03/12/monitoring-timeouts/[/url]
-- Itzik Ben-Gan 2001
August 11, 2016 at 1:19 pm
Thanks Alan.
I have one more question, how to handle / avoid query timeouts?
August 11, 2016 at 1:55 pm
Make sure your queries run fast enough that the app won't hit its timeout while waiting.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 11, 2016 at 2:22 pm
vsamantha35 (8/11/2016)
Thanks Alan.I have one more question, how to handle / avoid query timeouts?
What Gail said really sums it up. 😉
-- Itzik Ben-Gan 2001
August 11, 2016 at 3:10 pm
Hi Gail,
You mean create indexes and do some query tuning to speed up queries?
FYI we are pulling large datasets from different source systems and load data into a datawarehouse sql instance. It is like 4.9 million records ..
August 15, 2016 at 2:38 pm
Is there a way using dmvs / trace to get know the the timeout set for a query? I can ask the application team but I am curious to know if there is any DMV I could get this?
I am suspecting there could some long term blocking happening in our system.
August 15, 2016 at 3:06 pm
No, because timeouts are not a database setting.
A timeout is the application deciding it's waited too long and notifying SQL to stop the query. It is not SQL deciding to stop running the query, SQL Server itself has no concept of timeouts for queries that it's running locally.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 15, 2016 at 3:07 pm
vsamantha35 (8/11/2016)
You mean create indexes and do some query tuning to speed up queries?FYI we are pulling large datasets from different source systems and load data into a datawarehouse sql instance. It is like 4.9 million records ..
Yup.
You have two options for dealing with timeouts. Tune the queries so that they run within the timeout the application sets. Change the application to have a higher timeout.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 16, 2016 at 7:06 am
vsamantha35 (8/15/2016)
Is there a way using dmvs / trace to get know the the timeout set for a query? I can ask the application team but I am curious to know if there is any DMV I could get this?I am suspecting there could some long term blocking happening in our system.
You can leverage Dynamic Management Functions/Views to track your longest running queries, most frequent, queries using the most CPU, IOWA, memory, etc. Note this book (which you can download for free).
Performance Tuning With SQL Server Dynamic Management Views
Edit: posted link incorrectly.
-- Itzik Ben-Gan 2001
August 16, 2016 at 11:21 am
Thanks Gail and Alan. Many thanks.
September 2, 2016 at 11:30 pm
Hi All,
I was doing some home work on query timeouts. Came across the interesting blog from Jonathan kehayias. He demonstrated a nice way of capturing the queries which are getting timed out.Found it really useful. however, I want to check why the tsql_stack and statement is not getting displayed as output using xml parsing query. However,when I see the event data in GUI i was able to see the tsql_stack & statement causing the timeout.Can anyone help me in fixing the issue in the query.
--- Jonathan kehayias query
-- Create XML variable to hold Target Data
DECLARE @target_data XML
SELECT @target_data =
CAST(target_data AS XML)
FROM sys.dm_xe_sessions AS s
JOIN sys.dm_xe_session_targets AS t
ON t.event_session_address = s.address
WHERE s.name = 'SpStatementTimeouts'
AND t.target_name = 'pair_matching'
-- Query XML variable to get Target Execution information
--SELECT
-- @target_data.value('(PairingTarget/@orphanCount)[1]', 'int') AS orphanCount,
-- @target_data.value('(PairingTarget/@matchedCount)[1]', 'int') AS matchedCount,
-- @target_data.value('(PairingTarget/@memoryPressureDroppedCount)[1]', 'int') AS memoryPressureDroppedCount
-- Query the XML variable to get the Target Data
SELECT
n.value('(event/action[@name="session_id"]/value)[1]', 'int') as session_id,
n.value('(event/@name)[1]', 'varchar(50)') AS event_name,
n.value('(event/@package)[1]', 'varchar(50)') AS package_name,
n.value('(event/@id)[1]', 'int') AS id,
n.value('(event/@version)[1]', 'int') AS version,
n.value('(event/data[@name="source_database_id"]/value)[1]', 'int') as [source_database_id],
n.value('(event/data[@name="object_id"]/value)[1]', 'int') as [object_id],
n.value('(event/data[@name="object_type"]/value)[1]', 'varchar(60)') as [object_type],
n.value('(event/data[@name="state"]/text)[1]', 'varchar(50)') as [state],
n.value('(event/data[@name="offset"]/value)[1]', 'int') as [offset],
n.value('(event/data[@name="offset_end"]/value)[1]', 'int') as [offset_end],
n.value('(event/data[@name="nest_level"]/value)[1]', 'int') as [nest_level],
n.value('(event/action[@name="attach_activity_id"]/value)[1]', 'varchar(50)') as activity_id,
--- none of the columns are getting displayed in the output
DATEADD(hh,DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP),n.value('(event/@timestamp)[1]', 'datetime2')) AS [timestamp], -- it is showing incorrect time part. but the date part is showing up correctly
SUBSTRING (n.value ('(action[@name=''plan_handle'']/value)[1]', 'VARCHAR(100)'), 15, 50) AS [PlanHandle], ---
n.value ('(action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS [sql_Text],
n.value ('(action[@name="statement"]/value)[1]', 'nvarchar(max)') AS [sql_statement],
n.value('(event/action[@name="tsql_stack"]/value)[1]', 'nvarchar(max)') as tsql_stack
FROM
( SELECT td.query('.') as n
FROM @target_data.nodes('PairingTarget/event') AS q(td)
) as tab
ORDER BY session_id, activity_id
GO
Secondly, for sp_statement_starting event there is no duration column. How can come to a conclusion that after how much elapsed time, the query got timed out. This info will be useful in determining to what value I can set it for a query timeout inside the front application. Is there a way to capture/log duration for such queries???
SQL Server version
==================
Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
Oct 19 2012 13:38:57
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
Thanks,
Sam
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply