MS SQL Server - Sporadic Timeout expired Issue

Author

Sporadic Timeout expired Issue

Sunshine

2006-11-28, 7:14 pm

Hello all and thank you for your time.

I am a vb.net developer in a small shop with no DBA so I've had to
learn quite a bit. I'm experiencing a timeout error every now and
again. It mostly happens on Inserts but has happened on select
statements as well. For large report querys, I have set the
CommandTimeout = 0 which has solved the problem for those but these
insert are mainly small and should not be going pass the 30 seconds
(takes a second in QA - only one record on one table). The users are
spread out over the state. They use Citrix to run the application.
The citrix box, web box, and sql box are all on the same network so
connection should not be a problem. When they do bomb, it's after the
30 seconds but I can't understand why they are taking that long. The
users mostly just try again and then it works. It's happening on
different stored procedures at different times and not all the time.

System:
SQL Server 2000
Framework 1.1
Microsoft Windows Server 2003

I've check:
Locking - no blocks going on
Using (NOLOCK) on select statements
SQL Server box - CPU Performance - normally around 10 - 20 %. See more
problems when spiking higher than those.
SQL Server box - PF - 2.14 GB
Insert and deletes use indexed keys

Problem also appears when the users are experiencing a slow internet
connection (we don't have control over that).

Is there a better way to debug this (keep in mind I'm not a DBA but I'm
trying)? Any suggestions? What am I missing?

Here is the full error:

Server Error in '/Guardian' Application.
--------------------------------------------------------------------------------

Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding.
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.

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

Source Error:

An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of
the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException: Timeout expired. The timeout period elapsed prior to
completion of the operation or the server is not responding.]
System.Data.SqlClient.SqlCommand. ExecuteReader(Comman
dBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +742
System.Data.SqlClient.SqlCommand. ExecuteReader(Comman
dBehavior
behavior) +44

System.Data.SqlClient.SqlCommand.System.Data.IDbCommand. ExecuteReader(Comman
dBehavior
behavior) +5
System.Data.Common.DbDataAdapter. FillFromCommand(Obje
ct data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String
srcTable) +36
Guardian. VolunteerActivityDet
ail. VolunteerActivityDet
ail() +842
Guardian. VolunteerActivityDet
ail.Page_Load(Object sender, EventArgs
e) +21
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page. ProcessRequestMain()
+750

--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.2300;
ASP.NET Version:1.1.4322.2300

Thanks,
Sunshine

Chris Hoare

2006-11-30, 7:14 pm

Hi,

Do you know what code is running slowly on the server ? Im guessing you do
if you are logging the errors in .net but if not try using profiler on a
query for slow running procedures ( you will have to get a feel for this in
your aplication byt say > 5 seconds) which will let you see what is running
slowly and see if it is a code issue or a system issue. If it is one or two
procedures you can than run profiler on them and see how things are running
over a time period which can help; also run them in query analyser/
management studio with show actual exectuion plan select; this will show you
what part of your query is running slowly ( though it isnt 100% accurate it
is a great help at times.)

If you arent maintaining your database have a look at creating a maintenance
plan that optimizes the databases indexs and defrags the data (or look at
dbcc indexdefrag in books on line (sql's online help) though dont run it when
people on using the system

Chris

"Sunshine" wrote:

> Hello all and thank you for your time.
>
> I am a vb.net developer in a small shop with no DBA so I've had to
> learn quite a bit. I'm experiencing a timeout error every now and
> again. It mostly happens on Inserts but has happened on select
> statements as well. For large report querys, I have set the
> CommandTimeout = 0 which has solved the problem for those but these
> insert are mainly small and should not be going pass the 30 seconds
> (takes a second in QA - only one record on one table). The users are
> spread out over the state. They use Citrix to run the application.
> The citrix box, web box, and sql box are all on the same network so
> connection should not be a problem. When they do bomb, it's after the
> 30 seconds but I can't understand why they are taking that long. The
> users mostly just try again and then it works. It's happening on
> different stored procedures at different times and not all the time.
>
> System:
> SQL Server 2000
> Framework 1.1
> Microsoft Windows Server 2003
>
> I've check:
> Locking - no blocks going on
> Using (NOLOCK) on select statements
> SQL Server box - CPU Performance - normally around 10 - 20 %. See more
> problems when spiking higher than those.
> SQL Server box - PF - 2.14 GB
> Insert and deletes use indexed keys
>
> Problem also appears when the users are experiencing a slow internet
> connection (we don't have control over that).
>
> Is there a better way to debug this (keep in mind I'm not a DBA but I'm
> trying)? Any suggestions? What am I missing?
>
>
> Here is the full error:
>
> Server Error in '/Guardian' Application.
> --------------------------------------------------------------------------------
>
> Timeout expired. The timeout period elapsed prior to completion of the
> operation or the server is not responding.
> Description: An unhandled exception occurred during the execution of
> the current web request. Please review the stack trace for more
> information about the error and where it originated in the code.
>
> Exception Details: System.Data.SqlClient.SqlException: Timeout expired.
> The timeout period elapsed prior to completion of the operation or the
> server is not responding.
>
> Source Error:
>
> An unhandled exception was generated during the execution of the
> current web request. Information regarding the origin and location of
> the exception can be identified using the exception stack trace below.
>
>
> Stack Trace:
>
> [SqlException: Timeout expired. The timeout period elapsed prior to
> completion of the operation or the server is not responding.]
> System.Data.SqlClient.SqlCommand. ExecuteReader(Comman
dBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +742
> System.Data.SqlClient.SqlCommand. ExecuteReader(Comman
dBehavior
> behavior) +44
>
> System.Data.SqlClient.SqlCommand.System.Data.IDbCommand. ExecuteReader(Comman
dBehavior
> behavior) +5
> System.Data.Common.DbDataAdapter. FillFromCommand(Obje
ct data, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior) +304
> System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior) +77
> System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String
> srcTable) +36
> Guardian. VolunteerActivityDet
ail. VolunteerActivityDet
ail() +842
> Guardian. VolunteerActivityDet
ail.Page_Load(Object sender, EventArgs
> e) +21
> System.Web.UI.Control.OnLoad(EventArgs e) +67
> System.Web.UI.Control.LoadRecursive() +35
> System.Web.UI.Page. ProcessRequestMain()
+750
>
>
>
>
> --------------------------------------------------------------------------------
> Version Information: Microsoft .NET Framework Version:1.1.4322.2300;
> ASP.NET Version:1.1.4322.2300
>
>
> Thanks,
> Sunshine
>
>

Sponsored Links