My apologies for the repost... but this has been ongoing sine last night. I
have revised as Ive done more.

Since late last night, the 5 web servers which are load balanced and
connected to my db have been dying. In an effort to troubleshoot, I have:

1; Verified no deadlocking/ blocking.
2; Verified no high cpu/ memory/ or disk activity.
3; Nothing in the Event View or SQL Error Logs.
4: (Already posted this one, but it may be relevant:)

Im looking at the duration in a particluar proc in profiler and Im looking
at 40 - 70 seconds calling stored procs. I take the line from profiler, pop
it into query
analyzer, clear the cache, run it, and it returns in 0 -1 seconds. I can do
this 100% of the time.

5; I've now rebooted the box.

So, how do I rule out (or in) the DB and Server?

sql2k sp3a
TIA, ChrisR

ChrisR wrote:
[..]
> profiler, pop it into query
> analyzer, clear the cache, run it, and it returns in 0 -1 seconds. I
> can do this 100% of the time.
>
> 5; I've now rebooted the box.
>
> So, how do I rule out (or in) the DB and Server?
>
> sql2k sp3a
> TIA, ChrisR

I think we were trying to help you with this problem yesterday
(apologies if I'm thinking of another post). Simply pulling out a SQL
statement from an application and executing in QA is not a valid
comparison unless you are using static SQL in both cases (i.e. no
parameters, stored procedures, bind parameters, local variables, etc.)

You're probably experiencing a parameter sniffing issue. SQL Server
cannot easily optimize SQL using variables because those variable values
are not known at compile time. It's best to use a stored procedure and
pass it parameters that are not changed in the procedure.

You need to examine the execution plan for the query and figure out what
is causing it to take so long. Once you find the likely table
scan/clustered index scan operations in the query, determine if the
arguments you are using in the query have corresponding indexes that can
be used. I suspect there are indexes available given your description of
the query working fast in QA but not in the application.

A long duration can also be a result of slow fetching on a client. Is
the client fetching all results from the query immediately?

I think Im over the long running queries thing... probably not going to kill
my connections. I do appreciate your help, I probably just shouldnt have
bothered to post that part and stuck to the bigger issue which is loss of
connections. (Guess I freaked out and shoudnt have thrown that in there.)
Any thoughts on the connections?



"David Gugick" <david.gugick-nospam> wrote in message
news:3300
[..]

Ive also got lots of these errors. Dont know if this helps.


"ChrisR" <noemail> wrote in message
news:3648
[..]

ChrisR wrote:
> Ive also got lots of these errors. Dont know if this helps.
>


You don't have to worry about many 208 errors. Many are reported for
temp table access in procedures even though the temp table is there
(it's just not there at compile time is what I think triggers it).
However, the 208 error could mean that an object that should be there is
missing. You'll need to check the statement executed just prior to the
error to see what object is being referenced.

Not sure about your lost connections. Are they just breaking randomly?
Or are statements not completing but the connection remains active?

One web server will suddenly start losing connections randomly. Once
restarted, its good for maybe 20 minutes.


"David Gugick" <david.gugick-nospam> wrote in message
news:3020
[..]

David I just reread.... after talking to the web admin, its probably closer
to say that statements just arent completing but the connection is staying
open.



"David Gugick" <david.gugick-nospam> wrote in message
news:3020
[..]