MS SQL Server - Fast SP is timing out

Author

Fast SP is timing out

cmay

2005-06-29, 8:23 pm

I have an SP that is called on every page load for our web application
(asp.net).

The SP is basically like this:

SELECT * FROM Messages
WHERE StartDate < GETDATE() and EndDate > GETDATE()

Messages has like 15 rows in it, so this is NOT a slow SP.

This SP normally executes in like 1/1000 of a second. On my laptop I can
run a loop of 10,000 times executing this SP and it finishes without error
after less than 10 seconds.

We have PLENTY of long running SPs that do a ton of work but all day today I
have been getting timeouts for this one SP that should be able the fastest SP
we have in our entire system.

To access the SP I am using the Microsoft Data Access Application Blocks
SqlHelper class's ExecuteDataset method.

Now... I have read that some people suggest that the solution to this is to
increase the timeout of the command object. This would be the right answer
for long running SPs, that need 30+ seconds to run, but this SP should need
0.001 seconds, so I don't think that is the problem.

Also... I have read other problems where people say that while using the
DAAB they get errors in some instances, but it seems like those are related
to 1) calling ExecuteREADER not ExecuteDataset, and 2) the underlying problem
they report is that the connection is not closed, but our website only has 3
connections to the database right now, so we are not leaking connections.

Can anyone shed some light on this, or give me some ideas about how to track
this down? This code has been working w/o problem from the first day I put
it into production and it just started to fail today for no apparent reason.

Here is the stack trace:

Message: Timeout expired. The timeout period elapsed prior to completion of
the operation or the server is not responding.
Stack: at System.Data.SqlClient.SqlCommand. ExecuteReader(Comman
dBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand. ExecuteReader(Comman
dBehavior behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand. ExecuteReader(Comman
dBehavior behavior)
at System.Data.Common.DbDataAdapter. FillFromCommand(Obje
ct data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at
Microsoft.ApplicationBlocks.Data.SqlHelper. ExecuteDataset(SqlCo
nnection
connection, CommandType commandType, String commandText, SqlParameter[]
commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper. ExecuteDataset(Strin
g
connectionString, CommandType commandType, String commandText, SqlParameter[]
commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper. ExecuteDataset(Strin
g
connectionString, CommandType commandType, String commandText)

Narayana Vyas Kondreddi

2005-06-29, 8:23 pm

See if this table is locked by some process. Use sp_who to determine
blocking.

See if the following help:

http://vyaskn.tripod.com/ sql_odbc_...
ired.htm
http://vyaskn.tripod.com/watch_your_timeouts.htm
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/

"cmay" <cmay@discussions.microsoft.com> wrote in message
news:E631DCBD-5D6D-461E-8820- 062AE37F86F3@microso
ft.com...
> I have an SP that is called on every page load for our web application
> (asp.net).
>
> The SP is basically like this:
>
> SELECT * FROM Messages
> WHERE StartDate < GETDATE() and EndDate > GETDATE()
>
> Messages has like 15 rows in it, so this is NOT a slow SP.
>
> This SP normally executes in like 1/1000 of a second. On my laptop I can
> run a loop of 10,000 times executing this SP and it finishes without error
> after less than 10 seconds.
>
> We have PLENTY of long running SPs that do a ton of work but all day today
I
> have been getting timeouts for this one SP that should be able the fastest
SP
> we have in our entire system.
>
> To access the SP I am using the Microsoft Data Access Application Blocks
> SqlHelper class's ExecuteDataset method.
>
> Now... I have read that some people suggest that the solution to this is
to
> increase the timeout of the command object. This would be the right
answer
> for long running SPs, that need 30+ seconds to run, but this SP should
need
> 0.001 seconds, so I don't think that is the problem.
>
> Also... I have read other problems where people say that while using the
> DAAB they get errors in some instances, but it seems like those are
related
> to 1) calling ExecuteREADER not ExecuteDataset, and 2) the underlying
problem
> they report is that the connection is not closed, but our website only has
3
> connections to the database right now, so we are not leaking connections.
>
>
> Can anyone shed some light on this, or give me some ideas about how to
track

> this down? This code has been working w/o problem from the first day I
put
> it into production and it just started to fail today for no apparent
reason.
>
>
> Here is the stack trace:
>
>
> Message: Timeout expired. The timeout period elapsed prior to completion
of
> the operation or the server is not responding.
> Stack: at System.Data.SqlClient.SqlCommand. ExecuteReader(Comman
dBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand. ExecuteReader(Comman
dBehavior
behavior)
> at
>
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand. ExecuteReader(Comman

dBehavior behavior)
> at System.Data.Common.DbDataAdapter. FillFromCommand(Obje
ct data, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
> at
> Microsoft.ApplicationBlocks.Data.SqlHelper. ExecuteDataset(SqlCo
nnection
> connection, CommandType commandType, String commandText, SqlParameter[]
> commandParameters)
> at Microsoft.ApplicationBlocks.Data.SqlHelper. ExecuteDataset(Strin
g
> connectionString, CommandType commandType, String commandText,
SqlParameter& #91;]

> commandParameters)
> at Microsoft.ApplicationBlocks.Data.SqlHelper. ExecuteDataset(Strin
g
> connectionString, CommandType commandType, String commandText)
>

cmay

2005-06-30, 1:23 pm

Vyas,

I changed the SP so that it is no longer reading any data from the
database. Now, instead of reading from the table I am just creating a
table variable and returning it, and i am still getting the timeout
errors.

I think this eliminates the locking issue b/c there is no longer any
data being read.
I think also that we can eliminate performance, as this has to be the
fastest SP in our entire database.

What else could be going on?

Chris

Sponsored Links