Hi, all.
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:Gen eral
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&poo ls 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.
Bjørn
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.
4  4689  bj************@ gmail.com (bj************ @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:Gen eral
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&poo ls 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_connec tion, then
at least connection pooling is in order. (sp_reset_conne ction is
executed when a connection is reused from the pool.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
Erland Sommarskog wrote:
bj************@ gmail.com (bj************ @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:Gen eral
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&poo ls 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_connec tion, then
at least connection pooling is in order. (sp_reset_conne ction 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_connec tion, 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 bj************@ gmail.com (bj************ @gmail.com) writes:
I just sampled all statements for one hour, and we had ~450.000 calls
to sp_reset_connec tion, 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!
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, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
Erland Sommarskog wrote:
bj************@ gmail.com (bj************ @gmail.com) writes:
I just sampled all statements for one hour, and we had ~450.000 calls
to sp_reset_connec tion, 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.
Bjørn
[snip] This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Dave Kirby |
last post by:
I am working on a network management program written in python that has
multiple threads (typically 20+) spawning subprocesses which are used
to communicate with other systems on the network. This runs fine for a
while, but eventually slows down to a crawl. Running sar shows that
when it is running slowly there is an exceptionally large number of
minor page faults - there are continuously 14000 faults/sec, with a
variation of about...
|
by: tbatwork828 |
last post by:
I've PerfMon-ed our application for several days now and it
consistently averages 2000 Page Faults/sec, and accumulates on average
about 4 mill page faults during 35 mins. During the same monitoring
time of 35 mins, there is never any sustained occurence of hard page
faults -as a matter of fact, almost on avg 98+ % of the entire time the
app is experiencing soft page faults. The processor rarely ever gets
over 70-80% for a sustained...
|
by: tbatwork828 |
last post by:
Related to my other post on Graphics.FillRectangle and a lot of page
faults caused by this call...
We determine that when Control.DoubleBuffer=true to avoid the flicker
effect, Graphics.FillRectangle causes a lot of soft page faults - order
of 700/sec and more... When Control.DoubleBuffer=false, we have no page
faults at all - 0/sec.
Has anyone seen this behavior and how did they resolve it...? What are
our options...? Does...
|
by: Crirus |
last post by:
I made a simple test... loaded an image from file and draw it on a form
In mouse move, I just refresh the form.... that cause 1 000 000 page faults
(as task manager shows) in less than a minute.. what is that?
--
Ceers,
Crirus
------------------------------
If work were a good thing, the boss would take it all from you
|
by: David Morgan |
last post by:
Hi
Have 4Gb of RAM and plenty of free disk.
Those page faults are for DLLHOST.EXE using ~370Mb RAM. inetinfo.exe has
403,106,036 page faults at the time of writing and is using ~145Mb RAM.
Why so many page faults. System uptime ~500 hours.
The ASP-based site does interface with a SQL Server instance on a completely
| | |
by: Simon Gorski |
last post by:
I have a large problem, and I believe there is not yet a way to solve this
using IIS and ASP.NET. I hope someone has a solution which we couldn't
find.
The current situation
When a user logs in to our website, we implement a single login for multiple
services (let's call them services A-D). This means, that on the backend,
while sampleuser logs in to our service with only one username and password,
he is automatically logged in to...
|
by: scotp |
last post by:
Does anyone know what would cause excessive page faults
running the js function below? The most common browser used
is IE 6. The page has records that include text & checkbox
inputs. Each record also has a hidden input named
"questions", whose value is an id that is used in the name
of inputs to be disabled. The number of page faults
increases dramatically as records increase. I know it is
the for loop that is taking the extra time,...
|
by: bob66k88 |
last post by:
What is the easiest way to move SQL Server 2005 databases from one old
server to a new server?
|
by: none |
last post by:
I have an ASP.NET application, hosted on two web servers. I am looking
for advice on what should be an acceptable level of page faults on
these production servers. If the acceptable level is zero, then where
should I begin reducing page faults in code? What should I look for in
code to reduce page faults?
Having to reduce page faults is something I have not had been tasked
with before, so I am not sure what is acceptable and where to...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| | |
