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...
One thought I had was that the Page Faults reported for sqlsrv.exe is
related to memory mapped IO and therefore can be ignored. Right or
wrong?
Any thoughs/pointers/ideas, even wild guesses, are most welcome.
Bjrn
PS: The server memory is fixed at 1.5GB out of 2GB physical ram,
clients run on the same machine and use TCP/IP comm.(I know...) The
host itself is not paging.bjorn.augestad@.gmail.com (bjorn.augestad@.gmail.com) writes:
Quote:
Originally Posted by
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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:
Quote:
Originally Posted by
bjorn.augestad@.gmail.com (bjorn.augestad@.gmail.com) writes:
Quote:
Originally Posted by
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.
Bjrn|||bjorn.augestad@.gmail.com (bjorn.augestad@.gmail.com) writes:
Quote:
Originally Posted by
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.
Al least connection pooling is working!
Quote:
Originally Posted by
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. :-)
Yes, with that connection rate, he disconnected model is not very good.
Connection pooling is particular useful in things like web applications,
when you have many users that connect through the same middleware (the
web server), and that run queries very infrequently.
The model is usually fine for Windows applications too. But if you have some
service-type of thing which does not have to wait for a user that goes for a
coffee, it may be better to stay connected.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:
Quote:
Originally Posted by
bjorn.augestad@.gmail.com (bjorn.augestad@.gmail.com) writes:
Quote:
Originally Posted by
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.
>
Al least connection pooling is working!
More or less.
We also have a tiny ASP app that writes one row to the db for each HTTP
request it receives. The ASP app used to use connection pools with
default settings. After changing Min and Max pool size to 300,
everything is suddenly more normal, now the server is so fast my
profiler traces has to be changed to make them report rows at all :-)
Stuff that used to take between 1200 and 7000ms now finishes in 1ms, so
I guess the problem has been solved.
Have a great weekend.
Bjrn
[snip]
No comments:
Post a Comment