Posted by Erland Sommarskog on 07/25/06 21:26

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.)

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]