Microsoft SQL Server forum - timeout issues solved by recompilation

Author

timeout issues solved by recompilation

levtoma@yahoo.com

2005-06-28, 1:23 pm

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

Erland Sommarskog

2005-06-28, 8:23 pm

(levtoma@yahoo.com) writes:
> 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,

That is, you had SET ARITHABORT OFF in Query Analyzer? (Or you bave it
on, in the ADO .Net code.)

> 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?

I will have to admit that I don't have very many ideas to offer. The
basic scenario is common, but you appears to already to have examined
the things that I usually suggest people who run into this. (That is
slow performance in client, good in QA.)

What I would do is to look at these processes in sysprocesses. There
are a couple of columns related to wait states, for instance waittype
and last waittype. When I do this myself, I use aba_lockinfo a routine
of my own making which collects various lock and process information,
including current statement (on SQL 2000 SP3 and later). Look at
http://www.sommarskog.se/sqlutil/aba_lockinfo.html. There are also
some waitypes listed on that page, and links to a few more.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Sponsored Links