.NET application, timeout period elapsed
-
We are having an issue with a .NET application getting 'The timeout period elapsed prior to completion of the operation or the server is not responding' errors. When the application is trying to execute a particular SP it times out if the results are not returned within 30 seconds. The errors are intermittent and started in December but are getting increasingly worse to the point where the application is unusable. The timeout setting for both the database server and connection to the server is set to 600 seconds. I checked the database server for overall performance and blocking, but do not see any issues that would identify the problem. Does anyone have any ideas on how I can troubleshoot this issue?
TIA
Michelle
-
- start sql-profiler to capture a sample load and examine it for table and or index scans.
- do you have db-maintenance-jobs in place ? to reduce fragmentation
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I have started profiler and am capturing the execution plan. Yes, the optimization job runs weekly against the database in question and I verified it has been running successfully. I ran the SP that is causing the issue in QA and it ran from 4 to 25 seconds depending on the parameters passed to it. There is a index seek in the execution plan that is taking the longest amount of time.
-
Here is the execution plan from profiler:
Execution Tree
--------------
Sort(DISTINCT ORDER BY
[FLIGHT].[ActArvlDttm] ASC, [Expr1006] ASC, [Expr1007] ASC))|--Compute Scalar(DEFINE
[Expr1006]=[FLIGHT].[FlightID]+' - '+Convert([FLIGHT].[ActArvlDttm]), [Expr1007]=rtrim(Convert([FLIGHT].[FlightID]))+' - '+Convert([FLIGHT].[ActArvlDttm])))|--Nested Loops(Inner Join, OUTER REFERENCES
[TA].[ProcDate], [TA].[TagID]))|--Nested Loops(Inner Join, OUTER REFERENCES
[CONTAINERS].[SetupDate], [CONTAINERS].[CntnrID]))| |--Nested Loops(Left Anti Semi Join, OUTER REFERENCES
[CONTAINERS].[SetupDate], [CONTAINERS].[CntnrID]))| | |--Nested Loops(Inner Join, OUTER REFERENCES
[FLIGHT].[OrigLocCode], [FLIGHT].[ActDprtDttm], [FLIGHT].[PlannedDprtDttm], [FLIGHT].[FlightID]))| | | |--Clustered Index Seek(OBJECT
[CAIR_INTL].[dbo].[Flight_Depart].[PK__Flight_Depart__239E4DCF] AS [FLIGHT]), SEEK[FLIGHT].[CustCode]=[@CustCode]), WHERE[FLIGHT].[DestLocCode]=[@Loc]) ORDERED FORWARD)| | | |--Nested Loops(Inner Join, OUTER REFERENCES
[Expr1011], [Expr1012], [Expr1013]))| | | |--Merge Interval
| | | | |--Sort(TOP 2, ORDER BY
[Expr1014] DESC, [Expr1015] ASC, [Expr1011] ASC, [Expr1016] DESC))| | | | |--Compute Scalar(DEFINE
[Expr1014]=(4&[Expr1013])=4 AND NULL=[Expr1011], [Expr1015]=4&[Expr1013], [Expr1016]=16&[Expr1013]))| | | | |--Concatenation
| | | | |--Compute Scalar(DEFINE
[FLIGHT].[PlannedDprtDttm]=[FLIGHT].[PlannedDprtDttm], [FLIGHT].[PlannedDprtDttm]=[FLIGHT].[PlannedDprtDttm], [Expr1010]=62))| | | | | |--Constant Scan
| | | | |--Compute Scalar(DEFINE
[FLIGHT].[ActDprtDttm]=[FLIGHT].[ActDprtDttm], [FLIGHT].[ActDprtDttm]=[FLIGHT].[ActDprtDttm], [Expr1013]=62))| | | | |--Constant Scan
| | | |--Index Seek(OBJECT
[CAIR_INTL].[dbo].[Container_Activity].[Container_Activity_ind] AS [CONTAINERS]), SEEK[CONTAINERS].[FlightID]=[FLIGHT].[FlightID] AND [CONTAINERS].[ActDprtDttm] > [Expr1011] AND [CONTAINERS].[ActDprtDttm] < [Expr1012]), WHERE(([CONTAINERS].[StatusCode]=3 AND [CONTAINERS].[ScanLocCode]=[FLIGHT].[OrigLocCode]) AND [CONTAINERS].[CustCode]=[@CustCode]) AND NOT(like([CONTAINERS].[CntnrID], 'ZTRAND%', NULL))) ORDERED FORWARD)| | |--Clustered Index Seek(OBJECT
[CAIR_INTL].[dbo].[Container_Activity].[PK_Container_Activity] AS [CA]), SEEK[CA].[CntnrID]=[CONTAINERS].[CntnrID]), WHERE([CA].[SetupDate]=[CONTAINERS].[SetupDate] AND [CONTAINERS].[CntnrID]=[CA].[CntnrID]) AND ([CA].[StatusCode]=6 OR [CA].[StatusCode]=5)) ORDERED FORWARD)| |--Clustered Index Seek(OBJECT
[CAIR_INTL].[dbo].[Tag_Assign].[PK_Tag_Assign] AS [TA]), SEEK[TA].[CntnrID]=[CONTAINERS].[CntnrID]), WHERE[CONTAINERS].[SetupDate]=[TA].[SetupDate] AND [TA].[IsInactiveInd]='N') ORDERED FORWARD)|--Row Count Spool
|--Nested Loops(Left Anti Semi Join, OUTER REFERENCES
[TE].[ProcDate], [TE].[TagID]))|--Index Seek(OBJECT
[CAIR_INTL].[dbo].[Tag_Event].[Tag_Event_ind2] AS [TE]), SEEK[TE].[TagID]=[TA].[TagID] AND [TE].[ProcDate]=[TA].[ProcDate]), WHERE[TE].[StatusCode]=3) ORDERED FORWARD)|--Row Count Spool
|--Top(1)
|--Index Seek(OBJECT
[CAIR_INTL].[dbo].[Tag_Event].[Tag_Event_ind2] AS [TEE]), SEEK[TEE].[TagID]=[TE].[TagID] AND [TEE].[ProcDate]=[TE].[ProcDate]), WHERE[TEE].[StatusCode]=6 OR [TEE].[StatusCode]=5) ORDERED FORWARD) -
can you also post the query ?
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
The SP code is below:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].[csp_Flights]
@Loc CHAR(3) ,
@CustCode CHAR(2)
AS
SET NOCOUNT ON
BEGIN
DECLARE @LDPLSYS INTEGER
SET @LDPLSYS = 0
select @LDPLSYS = COUNT(1) FROM CAIR..System_parameters
where Parameter_code = @CustCode + 'LDPLSYSI'
AND Parameter_Value = 'Y'
IF @LDPLSYS > 0
BEGIN
IF @CustCode = '5X'
SELECT DISTINCT FLIGHT.FlightID + ' - ' + Convert(Varchar(30),FLIGHT.ActArvlDttm, 21) as FlightID, RTRIM(FLIGHT.FlightID) + ' - ' + Convert(varchar(30),FLIGHT.ActArvlDttm,107) as Flight,FLIGHT.ActArvlDttm
FROM Container_Activity CONTAINERS INNER JOIN FLIGHT_DEPART FLIGHT
ON FLIGHT.FlightID = CONTAINERS.FlightID
AND FLIGHT.CustCode = CONTAINERS.CustCode
AND Flight.OrigLocCode = CONTAINERS.ScanLocCode
INNER JOIN TAG_ASSIGN TA ON CONTAINERS.CntnrID = TA.CntnrID
AND CONTAINERS.SetUpDate = TA.SetUpDate
AND TA.IsInactiveInd = 'N'
INNER JOIN TAG_EVENT TE ON TA.TAGID = TE.TagID
AND TA.ProcDate = TE.ProcDate
AND TE.StatusCode = '3'
AND NOT EXISTS (SELECT TEE.TAGID FROM TAG_EVENT TEE
WHERE TE.TagID = TEE.TagID AND TE.ProcDate = TEE.ProcDate AND TEE.StatusCode IN ('5', '6' ))
WHERE
FLIGHT.CustCode = @CustCode
AND FLIGHT.DestLocCode = @Loc
AND (FLIGHT.PlannedDprtDttm = CONTAINERS.ActDprtDttm OR FLIGHT.ActDprtDttm = CONTAINERS.ActDprtDttm)
AND FLIGHT.ActDprtDttm >= '2006-04-01'
AND CONTAINERS.CntnrID NOT LIKE 'ZTRAND%'
AND (CONTAINERS.StatusCode = '3' AND CONTAINERS.CntnrID NOT IN
(SELECT CA.CntnrID FROM Container_Activity CA WHERE CA.CntnrID = CONTAINERS.CntnrID AND CA.SetUpDate = CONTAINERS.SetUpDate AND CA.StatusCode IN ('5', '6' )))
Order by FLIGHT.ActArvlDttm , FLIGHT.FlightID
ELSE
SELECT DISTINCT FLIGHT.FlightID + ' - ' + Convert(Varchar(30),FLIGHT.ActArvlDttm, 21) as FlightID, RTRIM(FLIGHT.FlightID) + ' - ' + Convert(varchar(30),FLIGHT.ActArvlDttm,107) as Flight,FLIGHT.ActArvlDttm
FROM Container_Activity CONTAINERS INNER JOIN FLIGHT_DEPART FLIGHT
ON FLIGHT.FlightID = CONTAINERS.FlightID
AND FLIGHT.CustCode = CONTAINERS.CustCode
AND Flight.OrigLocCode = CONTAINERS.ScanLocCode
INNER JOIN TAG_ASSIGN TA ON CONTAINERS.CntnrID = TA.CntnrID
AND CONTAINERS.SetUpDate = TA.SetUpDate
AND TA.IsInactiveInd = 'N'
INNER JOIN TAG_EVENT TE ON TA.TAGID = TE.TagID
AND TA.ProcDate = TE.ProcDate
AND TE.StatusCode = '3'
AND NOT EXISTS (SELECT TEE.TAGID FROM TAG_EVENT TEE
WHERE TE.TagID = TEE.TagID AND TE.ProcDate = TEE.ProcDate AND TEE.StatusCode IN ('5', '6' ))
WHERE
FLIGHT.CustCode = @CustCode
AND FLIGHT.DestLocCode = @Loc
AND (FLIGHT.PlannedDprtDttm = CONTAINERS.ActDprtDttm OR FLIGHT.ActDprtDttm = CONTAINERS.ActDprtDttm)
AND CONTAINERS.CntnrID NOT LIKE 'ZTRAND%'
AND (CONTAINERS.StatusCode = '3' AND CONTAINERS.CntnrID NOT IN
(SELECT CA.CntnrID FROM Container_Activity CA WHERE CA.CntnrID = CONTAINERS.CntnrID AND CA.SetUpDate = CONTAINERS.SetUpDate AND CA.StatusCode IN ('5', '6' )))
Order by FLIGHT.ActArvlDttm , FLIGHT.FlightID
END
ELSE
BEGIN
SELECT DISTINCT FLIGHT.FlightID + ' - ' + Convert(Varchar(30),ArvlDttm, 21) as FlightID, RTRIM(FLIGHT.FlightID) + ' - ' + Convert(varchar(30),FLIGHT.ArvlDttm,107) as Flight,FLIGHT.ArvlDttm
FROM Container_Activity CONTAINERS INNER JOIN INTL_Flight_Info FLIGHT
ON FLIGHT.FlightID = CONTAINERS.FlightID
AND FLIGHT.CustCode = CONTAINERS.CustCode
AND Flight.OrigLocCode = CONTAINERS.ScanLocCode
INNER JOIN TAG_ASSIGN TA ON CONTAINERS.CntnrID = TA.CntnrID
AND CONTAINERS.SetUpDate = TA.SetUpDate
AND TA.IsInactiveInd = 'N'
INNER JOIN TAG_EVENT TE ON TA.TAGID = TE.TagID
AND TA.ProcDate = TE.ProcDate
AND TE.StatusCode = '3'
AND NOT EXISTS (SELECT TEE.TAGID FROM TAG_EVENT TEE
WHERE TE.TagID = TEE.TagID AND TE.ProcDate = TEE.ProcDate AND TEE.StatusCode IN ('5', '6' ))
WHERE
FLIGHT.CustCode = @CustCode
AND FLIGHT.DestLocCode = @Loc
--AND FLIGHT.DprtDttm = CONTAINERS.ActDprtDttm
AND CONTAINERS.CntnrID NOT LIKE 'ZTRAND%'
AND (CONTAINERS.StatusCode = '3' AND CONTAINERS.CntnrID NOT IN
(SELECT CA.CntnrID FROM Container_Activity CA WHERE CA.CntnrID = CONTAINERS.CntnrID AND CA.SetUpDate = CONTAINERS.SetUpDate AND CA.StatusCode IN ('5', '6' )))
Order by FLIGHT.ArvlDttm , FLIGHT.FlightID
END
SELECT DstUtcOfSt as UtcOfSt
FROM CAIR_NRI..Airport_Time A
WHERE AirportCode = @Loc AND GETDATE() BETWEEN DstStartDttm AND DstEndDttm
UNION
SELECT UtcOfSt
FROM CAIR_NRI..Airport_Time A
WHERE AirportCode = @Loc AND GETDATE() NOT BETWEEN DstStartDttm AND DstEndDttm
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
My main question is why is this timing out at 30 seconds when both the database server and the connection string has a timeout of 600 seconds?
-
if you use a command-object, it has its own timeout (default at 30seconds)
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Yes, upon research I realized that and asked that they set CommandTimeout property to greater than 30 seconds. Unfortunately, the developers said that they cannot do that because they are using DATA ACCESS APPLICATION BLOCK (DAAB) for connecting to the SQL SERVER and it is Microsoft code that manipulates all the timeouts so they cannot change it.
-
Indeed, it's the dba's daily job to do miracles and salvations
Dev-teams are first of all afraid to admit flaws, and then to alter their design-issues with "propriatary" farmeworks or DAAB's are things they are very terrified of ! (And they should be .... That's why there is a designphase in a project)
Many dev-teams nowadays build "general" modules to handle their db-stuff. Loosing the tiny bits that make it manageble.
I'd bet all sqlserver connections show ".Net SqlClient Data Provider"
in stead of showing a usefull programm-information.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
You guessed it, that is what the sqlserver connections are showing. We are now trying to tune the SP so that it will always run under 30 seconds, but pending that is there a way to set a commandtimeout for DAAB?
-
With ado.net there is a sqlcommand.CommandTimeout that you can set.
also check out
http://msdn2.microsoft.com/en-us/library/aa479373.aspx#spoil_topic4
http://msdn2.microsoft.com/en-us/library/aa479373.aspx#spoil_topic5
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thanks for your help with this. Forgive me, but I'm not that .NET savvy. Does that mean the developers would need to replace DAAB with SPOIL in order to set the CommandTimeout property?
-
I guess not, because the .commandtimeout is available with the sqlclient.sqlcommand.
I would be estonished if that wasn't integrated in DAAB.
They've found it for the connection, so they 'll be able to find it for the sql-commandobject.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply