Stored Proc Time out

[Srimar]

Hello,

I am having this strange time out issue with some of our stored procs. We
have an SQL server on which we run our databases. Some times all of a sudden
a stored proc that is running fine from ASP.NET will start timing out. The
same query will run fine in the query anlyzer.

Initially we could not figure out why this is happening and then I ran
update statistics on the database. This fixed the time out issue with the
query from ASP.NET. What is puzzling me is that if the table stats are
messed up or there is page fragmentation it should affect both ASP.NET and
Query Analyzer, right?

I have verified that there are no locks. The time out has been observed with
procs that use tables from a different database on the same server. This
database is transactionally replicated. These procs contain joins to the
tables that are in the database that is getting transactionally replicated.

Can some body tell me what's going on?

Thanks

 

[William Ryan]

Are you sure that it's a command timeout and not a connection timeout?

<<What is puzzling me is that if the table stats are
messed up or there is page fragmentation it should affect both ASP.NET and
Query Analyzer, right?>> Yes, but I'd definitely run dbcc showcontig and
verify if I had fragmentation even if I wasn't having this problem. Keeping
Stats fresh, dropping and recreating indices are two things that you
definitely want to stay on top of .

Is this behavior totally random or does it happen at set times? You mention
transaction replication....have you seen some sort of correlation with it ?

 

[Srimar]

Yes, it's a command timeout. Our connection string has been set for a time
out of 120 seconds. We are using MS data access application block and the
defalut command timeout is 30 seconds. The timeout happens after 30 seconds.