Posted by Erland Sommarskog on 06/29/05 00:28

(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/techinfo/productdoc/2000/books.asp

[Back to original message]