Posted by bjorn.augestad@gmail.com on 07/27/06 08:54

Erland Sommarskog wrote:
> bjorn.augestad@gmail.com (bjorn.augestad@gmail.com) writes:
> > We have a couple of pathological sql servers that have lots and lots of
> > page faults per second, up to 4000. Our client programs are written in
> > C#/.NET 1.1 and utilizes connection pooling.
> >
> > Some of the client programs seems to log in hundred of times per
> > second, as reported by perfmon->.SQLServer:General
> > Statistics->Logins/sec. Stopping the client programs reduces that
> > number significantly.
> >
> > We've done code reviews of the client programs and they look OK.
> > Monitoring .NET connections&pools does not show anything suspicicous.
> >
> > We're currently rewriting the clients to use one db connection instead
> > of the pools, but that takes some time and may introduce bugs. Does
> > anyone know why we have these problems and/or why logins/sec is so
> > high? I'm thinking "bugs in the .NET client", but really have no
> > idea...
>
> I would use Profiler to see what these clients are up to. If they are
> generating tons of Audit:Login events, there is something fishy. Either
> they don't use pooling, or misbehave so that pooling is cannot be used.
> I think a common error is to fail to close the connections.
>
> If on the other hand they generate a lot of sp_reset_connection, then
> at least connection pooling is in order. (sp_reset_connection is
> executed when a connection is reused from the pool.)
>


I just sampled all statements for one hour, and we had ~450.000 calls
to sp_reset_connection, or 125 calls per second. Seems way too high to
me, even if the documentation describes it as lightweight.

I guess we'll continue to rewrite the clients to use just one
connection wherever possible. The changes made so far has improved the
situation a lot. :-)

Thanks.
Bjørn

[Back to original message]