I have an application which is inserting/updating upwards of hundreds of
thousands of records into a SqlServer table. I am using ADO, make my ADO
connection, then use ADO commands to insert/update each record as
appropriate. At the end of the insert/update I release the connection. I
then proceed to repeat the processing using a different table of the
same database.

The memory usage of Sql Server 2000 running under Win2k SP4 shows
approximately 675 MB after inserting a few hundred thousand records,
while the memory of the application stays very reasonable. This seems
excessive even under today's systems and my 1 GB of real memory. Why is
the Sql Server 2000 memory consumption building up so greatly ? I can
expect a certain hit as a connection to a SqlServer database is
established, and as each command is issued I can expect a certain hit,
but I would think that memory is being returned after each command is
executed, and after the connection is closed. Yet the memory keeps
building to a great amount. Even after my application is finished, and
exits, and all connections are closed, Task Manager shows the 675 MB of
memory still being used by Sql Server 2000. Any ideas on keeping this
memory to a reasonable amount, or why such a huge amount of memory is
being used by Sql Server 2000, would be appreciated.

By default SQL memory allocation is set to dynamic. This means SQL Server
will take as much memory as neccessary. Its more efficient for SQL to read
data from memory than disk.

If other applications request more memory SQL will relinquish what it is
holding.

You can change this dynamic memory setting if you wish and "cap" the amount
of memory SQL consumes.

Show quoteHide quote

Ryan wrote:
How does one cap the amount of memory which it consumes, programatically
or otherwise ? is there really no way to tell Sql Server,
programatically, to release whatever memory has been allocated for a
given connection when that conncection has closed ? I find it very odd
that Sql Server simply holds on to dynamic memory allocated for a
particular connection, after that connection is closed.

In EnterPrise Manager Right Click the ServerName
    Properties
    Memory Tab
You will see x2 radio buttons, dynamic and fixed memory with a slide rule.

OR in TSQL the same can be achevied via sp_configure. The setting is the
value in MB, so 2048 for 2 gb etc.

You will need to restart the SQL Service for this to take effect if you are
on SQL2000 or earlier, don't think that's the case for 2005 but i'm not
sure.

Show quoteHide quote

Ryan wrote:
Thanks for the information.

Egads, have you even OPENED Enterprise Manager?  Maybe a picture would help?
http://www.aspfaq.com/img/memory_tab.gif

Do you know what Books Online is?
http://www.aspfaq.com/2229
http://msdn.microsoft.com/library/en-us/optimsql/odp_tun_1a_6rn7.asp
http://msdn.microsoft.com/library/en-us/adminsql/ad_config_9zfy.asp

How about Google?
http://tinyurl.com/dzl96     (find the third instance of "release" on the
page)
http://www.sql-server-performance.com/q&a115.asp

And Google Groups?
http://tinyurl.com/bjttb

And the Knowledge Base?
http://support.microsoft.com/kb/321363/
http://support.microsoft.com/kb/319942/

Why on earth do you think that the memory is used only for that connection?
Isn't it even remotely possible that five minutes (or five seconds) from
now, some other connection might query the same data pages and require the
same amount of memory?  Why make SQL Server start from scratch, instead of
allowing it to keep the most frequently accessed data pages in memory,
speeding up the majority of your queries?  Limiting SQL Server's memory is
like cutting off your left foot while lying on the couch.  You don't need it
right now, so what good is it, right?

This belongs on a T-shirt. :)


ML

---
http://milambda.blogspot.com/

We'd probably see a lot of headless people walking around too.
Sadly though, most of them would still have their mouth.

Does it not seem to you that this is already true?


ML

---
http://milambda.blogspot.com/

once upon a time I was troubleshooting a complaint: SQL Server
responding very slowly. The reason was very simple: a fancy screen
saver was using up almost all the CPU. Took some time to figure it out,
as whenever I came to the server and logged in, everything looked good
;)

StripSaver, maybe? :)


ML

---
http://milambda.blogspot.com/

SQL Server consumes memory as required, but doesn't give it back unless the
os or other applications need it.  The assumption is, I suppose, that if you
need the memory now, you'll probably need it again in five minutes, so why
go through the hassle of releasing it just to grab it again, if no other
apps are demanding it.

Are you seeing performance issues?  Do you have other apps on the box that
need the memory?  Or are you just watching task manager?  Typically, in this
kind of scenario, you wouldn't have other apps or services on the box that
require memory at levels that compete with SQL Server.

As for "keeping this memory to a reasonable amount" yes it is possible in
the server properties to limit the upper bound or to even fix the amount of
memory used by SQL Server.  Of course, this will affect the performance of
the server when it actually would have used more than what *you* consider to
be a "reasonable amount" so I don't recommend this unless you absolutely
need to reserve the memory for other processes.

A







Show quoteHide quote

Aaron Bertrand [SQL Server MVP] wrote:
That is an odd use of memory. Most modules return dynamic memory after
it is no longer being used. This makes it much easier for the Windows
memory management to find needed memory for other modules.

When it builds up to 675 MB it definitely is slowing anything else that
is running, including the module(s) which are using Sql Server for
database access.

See comment above.

No, there are no other modules which consume 675 MB of memory.

I need for Sql Server to not slow down the system when it gets up to
using 675 MB. How does one limit the maximum amount of dynamic memory
Sql Server uses ? Is there really no way to just tell it,
programatically, to release the dynamic memory it has accumulated for a
given connection when that connection closes ? Frankly I am surprised at
this "hoggish" behavior of Sql Server to just hold on to all the memory
it has accumulated for a given connection even when that connection has
closed. This appears to mean to me that as long as Sql Server is running
in a particular Windows session on a machine it will never use less than
the maximum amount of memory it has allocated for that session.

Show quoteHide quote

Maybe to you... it sounds like you have much more experience in runtime
applications than with services.

SQL Server expects to be the only major application/service on a server.
That is why we have terms like "database server" and "application server."
I understand that budgets sometimes dictate they are one and the same, but
when you have fewer servers, expect tradeoffs.

What else is running *on the same box*?  I can't imagine a scenario where
some program needs to be running on the same box, and the only time it needs
675 MB to operate just happens to coincide with the times when SQL Server
*does not* need 675 MB.  Does this magical app have anything to do with the
database at all?  Have you tried running the app on a different server?

How have you proven that it is SQL Server that is slowing down the system?
Do you have any actual metrics  Unless your app is using the other 500MB and
then some and is paging to disk... in which case I would look at the app
before looking at the database.  SQL Server is in use in thousands upon
thousands of environments, do you think you are the only one witnessing this
"bizarre phenomenen" of SQL Server "stealing" memory and not giving it back?

Besides, what kind of server, that you expect good performance from, has 1GB
of memory these days?  Maybe you should consider adding more memory instead
of intentionally crippling the database.

Do you have Enterprise Manager?  Have you tried right-clicking on the server
name, selecting Properties, and moving to the Memory tab?  Now, you can
change these settings and restart SQL Server, but let me be the first to
predict that you will be back here complaining that it made matters worse.
SQL Server is using 675 MB because it NEEDS it.  Why should it give it back?
If you run a query that returns a resultset that is sized 500 MB, SQL Server
may be able to keep that in memory so that the *next* time you call the same
query, it will be faster.  If you go and do something foolish like limit the
amount of memory SQL Server can use, to something less than this query
requires, (a) it is going to be slower because it may have to page some of
the results to disk, and (b) successive invocations of the same query will
have to do the same thing, so you lose economies of scale, too.  Unless you
have SQL Server for no reason and can eliminate it from the equation, I
don't see how this can possibly make the system faster.

Maybe you could focus on tuning your queries and limiting the amount of
sorting and data retrieval that is going on so that SQL Server uses less
memory.  How much memory do you think is appropriate?  What kind of testing
and benchmarking have you done?  What kind of testing and benchmarking are
you going to do afteryou make the change?  Or are you just going to walk
away, happy knowing that your app will always have free reign over the
memory on the system?

Yes, this surprises a lot of people.  Unfortunately for you, this is how it
works.  And the only solution you have come up with so far is *not* going to
make things better.

That is correct.  Next you'll be asking how to limit the disk space and
number of CPUs SQL Server uses, because all of your other apps need that
stuff sometimes too.  :-(

A

Aaron Bertrand [SQL Server MVP] wrote:
Show quoteHide quoteOK, I understand that and it is reasonable.

Nothing else needs that much memory but I was experiencing a great
slowdown in my main application which was using Sql Server when its
memory was in the 675 MB range.

Show quoteHide quoteIt is not a bad idea to add at least another GB of memory. I will look
into that.

I get the idea.

I would say because it needed it, not because it needs it. Evidently a
good chunk of that memory is for cacheing the results of inserts/updates
which my application does when a particular connection required it. Then
the connection is closed, to that particular database and whatever
table(s) were being used when the inserts/updates were being made, and
Sql Server still keeps the cached results in memory. At least that is my
guess of why so much memory is being used. Wouldn't it be smarter for it
to notice that a particular connection is closed and therefore release
the cached memory which it has ? Granted another connection could be
made to the same database, and access to the same table(s) on that
database could be made, and the cached results might be valuable then,
but how likely is that given all the many databases/tables combinations
which would occur during a particular session in which Sql Server is
running ? Or I should say, while it is likely to some extent, shouldn't
a more subtle algorithmic use of memory management for Sql Server be
made than just "let's hold on to all cached results" as long as Sql
Server is running ? Or perhaps something on the order of a parameter to
determine whether Sql Server is the one dedicated service on a database
server and if it is, behave the way it does but it if it is not, act a
little more responsibly, and cleverly, with dynamic memory.

Yes, this is all understood. Still it seems that programatically at
least I should be able to tell Sql Server to release all its cached
memory associated with a connection when the connection closes, unless
of course another connection which is still open is accessing some same
piece of cached memory which my now closed connection was accessing
also. In other words it seems poor that the module accessing Sql Server
can not control programatically, on a connection by connection basis,
its single way of holding on to all dynamic memory.

Be that as it may, thanks for your answers.

NO!!!!!!

Why does that query only belong to a single connection?  If no other
connection is ever going to call the query again, then stop usinga database
altogether and put this stuff into flat files.  Otherwise, let the database
do its job.

We have SQL Servers that have been up for months and years.  Why should SQL
Server just say, oh forget it, he's going to reboot the box next week, why
bother caching any data at all?

That's absolutely not what it does.  It caches the most frequently used data
pages.

If you don't like the default behavior (and I will suggest again that you
are only GUESSING that this has anything at all to do with any perceived
slowdown), YOU need to act more resonsibly and "cleverly" and configure the
server the way you seem to want to "fix" it.  But before you do that, I
*STRONGLY, STRONGLY, STRONGLY* recommend you go do some reading on it first.
I provided plenty of material in another post.

You are far too buried in this "connection" nonsense.  SQL Server is
optimized to serve up data, not to serve up individual connections.  It does
not (and should not) assume that the data a connection is accessing is only
going to be used once in SQL Server's uptime!  Do you reboot SQL Server
every five minutes?  PLEASE!  GO READ!


Why should a module dictate the memory being used by SQL Server?  PLEASE!
GO READ!

A

Edward Diener (eddielee_no_spam_here@tropicsoft.com) writes:
Extremely likely. I would submit that in an OLTP database where queries
are generally well-tuned and uses indexes, you can get extremely high
cache hit ratio if the available memory, if 5% of the size of the
database. That is, most of the queries and operations are against that
portion of the data.

Edward Diener (eddielee_no_spam_here@tropicsoft.com) writes:
Not really. It appears from your post that you have other applications
on your machine than just SQL Server. But normally, SQL Server runs on
a decicated machine, and the operating system is the only to compete for
memory.

This is not an issue of being lazy of releasing memory. Most of that
memory has useful contents, to wit cache. SQL Server likes to put as
much as possible in cache, because reading from memory is so much
faster than from disk.

So for this reason SQL Server only yields memory, if there is pressure
from other applications. It may not yield fast enough, though, and if
you run SQL Server on your workstation with web browsers and other
programs running, you probably don't want them to be paged out. So in
this type of environment it is a good idea to limit how much memory
SQL Server can use.

The simplest to do this is to open Enterprise Manager, right-click
the server, choose Properties, and go the Memory tab. There are a
couple of possible settings. On my machine - which has 1GB of memory -
I've set SQL Server to use a fixed memory of 134 MB.

Note that when you run SQL Server on a production server, you should
not change the memory setting. That will only cause harm.

multiple inserts in transaction
Joing two tables but avoid cartesian product
Dynamic SQL Column Total Help
Datetime constraint: is there a better way to do this?
Add a field