Hello,
I am having trouble with a production db server that likes to gobble
up memory. It seems to be a slow burn (maxing out over about an 18
hour time frame, before pegging both procs on the server and bringing
everything to a standstill). After viewing the trace logs, it appears
that all the SPIDs are being recycled - does this assert that
connections are being properly closed when the need for them has
ended? The code base is huge and quite messy, so it's difficult to
discern where the problem is just by looking at code, and we can't
seem to nail it down by looking at it, and I'm not sure what to look
for in the trace logs or perfmon.
Does anyone have any suggestions about what else might cause such a
problem?
Ryan 6 6473
Ryan (ry******@excit e.com) writes: I am having trouble with a production db server that likes to gobble up memory. It seems to be a slow burn (maxing out over about an 18 hour time frame, before pegging both procs on the server and bringing everything to a standstill). After viewing the trace logs, it appears that all the SPIDs are being recycled - does this assert that connections are being properly closed when the need for them has ended? The code base is huge and quite messy, so it's difficult to discern where the problem is just by looking at code, and we can't seem to nail it down by looking at it, and I'm not sure what to look for in the trace logs or perfmon.
SQL Server likes to gobble up memory. In fact this is by design. The
more data SQL Server can hold in cache, the more queries it can
respond to without disk access. So normally SQL Server expands to
get all avilable memory. But if there are other processes in need of
memory, SQL Server will yield. It may not yield fast enough, though,
and you can configure SQL Server to use only part of the memory.
So the perceived memory leak is not a problem, but since you talk about
standstill, it seems that you have a problem. And since you talk about
pegging the processors on the server, it seems that you have a query in
need of rewrite somewhere. Or a in need of a better index. So while that
code base may be big and ugly, and you prefer not to look at it, it is
most likely there you find the solution.
The Profiler is a good tool. Filter for Duration greeater than, say,
1000 ms. Then again, if you start tracing when that bad query starts
running, you will not see the query until it is completed. One
alternative is aba_lockinfo, which is on my home page, http://www.sommarskog.se/sqlutil/aba_lockinfo.html. That procedure
is really intended for lock monitoring, but you get all active processes
and what they are doing. And since "standstill " often includes blocking
as well, it may be interesting. aba_lockinfo gives you a snapshot, but
can still reveal something about what is going on. One word of caution
though: aba_lockinfo can take some time to return on a busy system. I
have identiefied a few weaknesses in terms of performance, but I have
not came around to fix them yet.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
"Erland Sommarskog" <so****@algonet .se> wrote in message
news:Xn******** **************@ 127.0.0.1... Ryan (ry******@excit e.com) writes:
<snip>
SQL Server likes to gobble up memory. In fact this is by design. The more data SQL Server can hold in cache, the more queries it can respond to without disk access. So normally SQL Server expands to get all avilable memory. But if there are other processes in need of memory, SQL Server will yield. It may not yield fast enough, though, and you can configure SQL Server to use only part of the memory.
So the perceived memory leak is not a problem, but since you talk about standstill, it seems that you have a problem. And since you talk about pegging the processors on the server, it seems that you have a query in need of rewrite somewhere. Or a in need of a better index. So while that code base may be big and ugly, and you prefer not to look at it, it is most likely there you find the solution.
The Profiler is a good tool. Filter for Duration greeater than, say, 1000 ms. Then again, if you start tracing when that bad query starts running, you will not see the query until it is completed. One alternative is aba_lockinfo, which is on my home page, http://www.sommarskog.se/sqlutil/aba_lockinfo.html. That procedure is really intended for lock monitoring, but you get all active processes and what they are doing. And since "standstill " often includes blocking as well, it may be interesting. aba_lockinfo gives you a snapshot, but can still reveal something about what is going on. One word of caution though: aba_lockinfo can take some time to return on a busy system. I have identiefied a few weaknesses in terms of performance, but I have not came around to fix them yet.
-- Erland Sommarskog, SQL Server MVP, so****@algonet. se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
I agree with every thing above... in the intrim you may want to limit the
amout no memory that MS SQL can have. This helps especially if you are
running other programs on the machine that compete for memory. The downside
is that MSSQL has less to work with and will possibly take longer. Upside is
that MSSQL will not gobble up all the memory bringing everything to a halt.
-p
Absolutely. You should limit the database memory even if you have no other
applications running on the machine. Strangely enough, SQL Server can choke
the operating system by leaving too little memory for the OS to function at
optimum.
Hope this helps,
Chuck Conover www.TechnicalVideos.net "Pippen" <12*@hotmail.co m> wrote in message
news:bw0Rb.1529 62$na.259030@at tbi_s04... I agree with every thing below... in the intrim you may want to limit the amout no memory that MS SQL can have. This helps especially if you are running other programs on the machine that compete for memory. The
downside is that MSSQL has less to work with and will possibly take longer. Upside
is that MSSQL will not gobble up all the memory bringing everything to a
halt. -p "Erland Sommarskog" <so****@algonet .se> wrote in message news:Xn******** **************@ 127.0.0.1... Ryan (ry******@excit e.com) writes:
<snip>
SQL Server likes to gobble up memory. In fact this is by design. The more data SQL Server can hold in cache, the more queries it can respond to without disk access. So normally SQL Server expands to get all avilable memory. But if there are other processes in need of memory, SQL Server will yield. It may not yield fast enough, though, and you can configure SQL Server to use only part of the memory.
So the perceived memory leak is not a problem, but since you talk about standstill, it seems that you have a problem. And since you talk about pegging the processors on the server, it seems that you have a query in need of rewrite somewhere. Or a in need of a better index. So while that code base may be big and ugly, and you prefer not to look at it, it is most likely there you find the solution.
The Profiler is a good tool. Filter for Duration greeater than, say, 1000 ms. Then again, if you start tracing when that bad query starts running, you will not see the query until it is completed. One alternative is aba_lockinfo, which is on my home page, http://www.sommarskog.se/sqlutil/aba_lockinfo.html. That procedure is really intended for lock monitoring, but you get all active processes and what they are doing. And since "standstill " often includes blocking as well, it may be interesting. aba_lockinfo gives you a snapshot, but can still reveal something about what is going on. One word of caution though: aba_lockinfo can take some time to return on a busy system. I have identiefied a few weaknesses in terms of performance, but I have not came around to fix them yet.
-- Erland Sommarskog, SQL Server MVP, so****@algonet. se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Chuck Conover (cc******@comms peed.net) writes: Absolutely. You should limit the database memory even if you have no other applications running on the machine. Strangely enough, SQL Server can choke the operating system by leaving too little memory for the OS to function at optimum.
No, for a machine that only runs SQL Server, there is no reason to configure
the memory. The most likely result is that when you buy more memory, you
cannot understand why it does not pay off, because you had forgotten that
you had constrained the memory.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
"Chuck Conover" <cc******@comms peed.net> wrote in message
news:10******** *******@news.co mmspeed.net... Absolutely. You should limit the database memory even if you have no
other applications running on the machine. Strangely enough, SQL Server can
choke the operating system by leaving too little memory for the OS to function
at optimum.
I'd have to disagree. I've never seen this be an issue.
Greg,
No problem to disagree. It is possible that I came to the wrong
conclusion. However, I did see a situation just recently whereby there were
several views (written very badly) that required the database to bring back
several million rows. The I/O was astronomical. It appeared that even
after the view had come back, the whole machine was incredibly slow, and our
diagnostics showed that the database had eaten almost all 2GB of memory on
the machine. So our assumption was that the OS did not have enough memory
to function optimally. Rebooting the machine was the short-term fix. Being
a production server, we made 3 fixes simultaneously to get the machine
working properly as quickly as possible. Correcting the views, adding
another 2GB of memory, and limiting the DB memory fixed the problem, but we
aren't sure which one of our fixes corrected the problem.
Thanks for the input. It is possible that the views did not ever finish
completely considering the I/O required. That could have been the reason
for the slowdown of the machine.
Best regards,
Chuck Conover www.TechnicalVideos.net
"Greg D. Moore (Strider)" <mo************ ****@greenms.co m> wrote in message
news:Sg******** ***@twister.nyr oc.rr.com... "Chuck Conover" <cc******@comms peed.net> wrote in message news:10******** *******@news.co mmspeed.net... Absolutely. You should limit the database memory even if you have no other applications running on the machine. Strangely enough, SQL Server can choke the operating system by leaving too little memory for the OS to function at optimum.
I'd have to disagree. I've never seen this be an issue. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Morten Aune Lyrstad |
last post by:
Ok, now I'm officially confused. I have a large project going, which
uses a win32 ui library I am developing myself. And I'm getting weird
memory leaks. I don't know if I can explain what is going on, but I
really need some help on this one.
Ok, so I have this class defined (written by Randy Charles Morin,
www.kbcafe.com) which detects memory leaks. It creates a memory check
point in the constructor, and another in the destructor, and...
|
by: José Joye |
last post by:
Hi,
I have implemented a Service that is responsible for getting messages from a
MS MQ located on a remote machine. I'm getting memory leak from time to time
(???). In some situation, it is easier to reproduce (e.g.: remote machine
not available). After about 1 day, I get a usage of 300MB of memory.
I have used .NET Memory Profiler tool to try to see where the leak is
located. For all the leaky instances, I can see the following (I...
|
by: Jon Davis |
last post by:
OK I have a web app that I built that makes MANY calls to the DB in each
request. The app wasn't tuned for scalability so this wasn't a problem, but
time is too short to redesign how the database is accessed because the data
that's being stored is time relevant and the web app will be thrown out in a
few months. Since I try to separate the OleDb stuff from the business logic,
I just create new database connections and trust that those...
|
by: JCauble |
last post by:
We have a large Asp.net application that is currently crashing our production
servers. What we are seeing is the aspnet_wp eat up a bunch of memory and
then stop unexpectedly. Does not recycle.
Example:
After about 5 hours with a concurrent user base of about 150 users the
application raises the aspnet_wp memory usage by almost 500MB. If our server
guys modify the web.config this data is released and the workerprocess goes
back to a...
|
by: Anton |
last post by:
{Willy Skjveland} Hi, how can I trace a Memory leak in aspnet_wp.exe?
{Rheena} One moment please while I search it for you. It may take me a few
moments
{Willy Skjveland} I need to find out which application and which dll and
asmx page that cause the problem.
{Rheena} May I know what operating system you are using?
| |
by: Charlotte |
last post by:
Hello,
We are doing a stability test on our application to prepare for Microsoft
SQL Server 2000 certification, and we found a memory leak, but we cannot find
its source and fix it.
We use an ASP page written in JScript that connects to Analysis Services
2000 using ADOMD and retrieves data from it (this is a very simplified
version of our real application that we created just to test the memory
leak), see the code below.
|
by: Florin |
last post by:
Hi all,
I have a problem related to memory grow on a server application
which is basically stateless (I have some static info loaded). The
client accesses this server using remoting and it has worked for about
2 years without problems.
The problem I have encountered lately is that the memory on server
side started to grow and not to be released. I have checked first new
functionalities but after isolating these on separate server, the...
|
by: Salvador |
last post by:
Hi,
I am using WMI to gather information about different computers (using win2K
and win 2K3), checking common classes and also WMI load balance. My
application runs every 1 minute and reports the status of the machines.
Upon we follow the .NET object lifetime recommendations the application is
constantly consuming more memory! The problem is on the
ManagementObjectSearch, upon we Dispose the object it seems that is not
releasing the...
|
by: Lauren the Ravishing |
last post by:
Hi,
In ASP, is it absolutely necessary to set an object to Nothing after
being used?
set myObj = server.createObject("myDLL.myClass")
call myObj.useClass
set myObj = Nothing <--- can I omit this?
I'm dealing with a large number of files with nested includes. There
|
by: Jay |
last post by:
I have a web app running on the windows CE device. In one of the asp.net
pages - it has javascript code. That seems to have a memory leak.
When I run the web app - in about one hour, the app hangs. I looked at the
memory and it seems to be full.
I removed all the javascript code - and the app seems to be have no leaks.
As soon as I include my javascript code - the memory consumption gradually
increases.
Whether I actually invoke the...
|
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: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
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: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |