Timeouts from a very fast SP?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object 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(SqlConnection
connection, CommandType commandType, String commandText, SqlParameter[]
commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
connectionString, CommandType commandType, String commandText, SqlParameter[]
commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
connectionString, CommandType commandType, String commandText)
 
Turn on the profiler and see what's actually getting executed.
I suspect you also need to look at SP_Lock to see if the processes are
competing for the table.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

cmay said:
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(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior
behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object 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(SqlConnection
connection, CommandType commandType, String commandText, SqlParameter[]
commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
connectionString, CommandType commandType, String commandText,
SqlParameter[]
commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
connectionString, CommandType commandType, String commandText)
Click to expand...
 
I didn't see any locking going on.

I even changed the sp to just declare a table variable and return it,
so it isn't even reading data from a table anymore and the SP is still
timing out.

This has to be something other than the SP, something network or
connection related.

Could something be happening like... there is a connection in the pool
that is "open" and the code makes a call using this connection, but the
network fails at that moment or a firewall blocks it or..... god I am
grasping at straws at this point.
 
What did the profiler trace see? (Incidentally, when you chop off the
thread, I don't see a context when I reply).

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Bill,

I ran a variety of traces, and nothing caught my eye. I tried running
one agains the entire database server looking for for Lock Timeout and
Deadlocks. There were quite a few lock timeouts and 1 deadlock, but
they were not from my application and were not taking place against the
database I am using (different catalog).

When ever I ran sp_who or sp_who2 it would never show any blocking
going on.
From each webserver there were between 3 and 7 connections to the
Click to expand...
database, which seemed about right. If it were 1 I would think maybe
it was not pooling, and if it were 100 I would figure that connections
were not being closed.

I ran a trace of just the SQL SP in question and it showed it being
executed over and over and over w/ a duration of between 0 and 30
milliseconds (w/ only a few over 30 ms).
Yet at the same time this trace was running, I got the error report
from my web application that this SP had timed out.

I didn't get to run sp_lock b/c it looked like you owuld have to first
identify a lock using that method, which I was never able to find.

I am really at a loss.



What did the profiler trace see? (Incidentally, when you chop off the
thread, I don't see a context when I reply).


--
______________________________­______



William (Bill) Vaughn
 
What's the Command Timeout set to?
I've seen queries timeout when the DB has to add more space to the database.
This happens every few minutes in a heavily used system as the DB is
growing.
Is the system being used with Reporting Services or as a print server?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Bill,

I ran a variety of traces, and nothing caught my eye. I tried running
one agains the entire database server looking for for Lock Timeout and
Deadlocks. There were quite a few lock timeouts and 1 deadlock, but
they were not from my application and were not taking place against the
database I am using (different catalog).

When ever I ran sp_who or sp_who2 it would never show any blocking
going on.
From each webserver there were between 3 and 7 connections to the
Click to expand...
database, which seemed about right. If it were 1 I would think maybe
it was not pooling, and if it were 100 I would figure that connections
were not being closed.

I ran a trace of just the SQL SP in question and it showed it being
executed over and over and over w/ a duration of between 0 and 30
milliseconds (w/ only a few over 30 ms).
Yet at the same time this trace was running, I got the error report
from my web application that this SP had timed out.

I didn't get to run sp_lock b/c it looked like you owuld have to first
identify a lock using that method, which I was never able to find.

I am really at a loss.



What did the profiler trace see? (Incidentally, when you chop off the
thread, I don't see a context when I reply).


--
______________________________­______



William (Bill) Vaughn
 
Bill,

Thanks for you continued help on this issue.

Out of frustration we rebooted the SQL machine this weekend and have
not had a single occurance of this weird timeout issue since.

Part of me is glad that it appears to be working, but part of me is
worried b/c I am not sure why this happened in the first place and we
don't want to be in a position where we have to reboot our SQL box
often.

If this issue pops up again I will be posting again :) thanks!

Chris
 
Back
Top