Hello,
We have an ADO.NET application using .NET version 1.1.4322 SP1. It is
calling stored procedures in a database that it never written to. The
only thing the stored procedures do is a select statement on a few
tables that are joined. In the last few weeks we have experienced
issues where, two or three of these stored procedures consistently
timeout. When we recompile the stored procedures, the problem goes
away for a period of time (anywhere between a few hours and a few
days), then it reoccurs with the same two or three stored procedures.
We have tried running the stored procedures with the same parameters
against the same server using query analyzer with the same user and
connection settings while we are having the timeout issues, and they
performed normally (in the 5 second range). We have also put the
database into "read only" mode, which has improved performance
during normal times, but the issue continues to occur.
Our trace data shows that during the timeouts periods, the stored
procedures perform the same number of reads and writes, and using the
same amount of CPU as during normal times, but the duration increases
from 5-6 seconds to 30 seconds (when the server receives the timeout
request).
We have found no blocking on any of the tables (they are, after all,
read only).
Finally, we compared execution plans for the stored procedures when we
had timeouts to right after we recompile and alleviate the issue, and
the plans are identical.
What could be causing this problem? Does recompiling a stored
procedure affect the Sql Server .Net Data provider?
Thanks in advance