Memory leak in SQL Server

[Bardolator]

I have a stand alone Java application that uses MS SQL Server 2000 and
Microsoft JDBC ODBC drivers to access it. Before I run the application,
I open the task Manager to monitor the memory used by the SQL Server.
Generally it begins 21 MB and very soon it reaches around 100 MB if I
do some operations in the application. The thing is that even if I
close the application, the memory taken by SQL Server doesn't go down.
What could be the reason behind it? Java code or MD drivers or SQL
Server?
I checked the code and we are closing the statements and result set and
commiting everywhere.

Please let me know...I am trying to refactor as much as I can but still
out of luck.

[Gang He [MSFT]]

It is not a memory leak. It is most likely due to the buffer pool growth.
When the server is started, buffer pool reserves a lot of memory but not
committed. The buffer page memory is committed only one-by-one upon demand
when the app issues queries to read/write data. TaskMgr only shows the
committed memory, which is why you saw the memory used by sqlservr
increased. And once the buffer page memory is committed, normally buffer
pool doesn't give them back unless under memory pressure. This is why after
the app ends, the memory used by sqlservr in TaskMgr doesn't go down.

Other usage of memory in the server can also contribute here: for example,
the query plans used by the app can be cached by the procedure cache for
furure usage, etc.

--
Gang He
Software Design Engineer
Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.
"Bardolator" <Ba********@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

I have a stand alone Java application that uses MS SQL Server 2000 and
Microsoft JDBC ODBC drivers to access it. Before I run the application,
I open the task Manager to monitor the memory used by the SQL Server.
Generally it begins 21 MB and very soon it reaches around 100 MB if I
do some operations in the application. The thing is that even if I
close the application, the memory taken by SQL Server doesn't go down.
What could be the reason behind it? Java code or MD drivers or SQL
Server?
I checked the code and we are closing the statements and result set and
commiting everywhere.

Please let me know...I am trying to refactor as much as I can but still
out of luck.

[Bardolator]

If that is the thing then is there a way to keep the memory of SQL
server down? Any settings in the SQL server or any code to clear the
buffer? The reason is that if the machine or SQL server isn't started
for few days on a machine, SQL server tends to take more than 200 MB of
memory which slows down the computer.
(Wonder why no one else complains of this memory issue on the net)

[rudianus]

The amount of memory used by sql 2000 is definable. Enterprise
mgr>right mouse properties on server in question>memory tab.

No one complains because it is not a problem. Caching selected
data/procedure plans/etc... is a good thing in databases.

-scott

[Greg D. Moore \(Strider\)]

"Bardolator" <Ba********@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

If that is the thing then is there a way to keep the memory of SQL
server down? Any settings in the SQL server or any code to clear the
buffer? The reason is that if the machine or SQL server isn't started
for few days on a machine, SQL server tends to take more than 200 MB of
memory which slows down the computer.
(Wonder why no one else complains of this memory issue on the net)
Because you want to give a DB server as much memory as possible.

My guess is that you're not running on a dedicated machine?

Add memory if at all possible.

[Robert Klemme]

Bardolator wrote:

If that is the thing then is there a way to keep the memory of SQL
server down? Any settings in the SQL server or any code to clear the
buffer? The reason is that if the machine or SQL server isn't started
for few days on a machine, SQL server tends to take more than 200 MB
of memory which slows down the computer.
(Wonder why no one else complains of this memory issue on the net)

Even if it takes 200MB this is not a problem as long as the DB is not
used. The OS will evetually page out unused mem to disk. You might want
to increase virtual memory.

Alternatively you can decrease memory settings for SQL Server but this
impacts the DB's performance.

OTOH, 200MB isn't really much for a DB. Buying some additional mem is not
a bad idea.

Kind regards

robert

[Bardolator]

This is a stand alone java application using the stand alone sql server
(actually MSDE for the end users) that is resident on their PCs. So 200
MB is a lot if only one user is connected to it. I will try to change
the settings and will see what happens.

[Robert Klemme]

Bardolator wrote:

This is a stand alone java application using the stand alone sql
server (actually MSDE for the end users) that is resident on their
PCs. So 200 MB is a lot if only one user is connected to it. I will
try to change the settings and will see what happens.

It does seem much to you - it doesn't to me. :-)

Note also that the memory usage is mainly determined by the amount of data
you touch and not by the sheer number of users.

Kind regards

robert

[Bardolator]

But I thought that the whole point of having a scale down version of
SQL Server (which is MSDE) is that it can be installed on individual
PCs. Also, MSDE 1.0 (which is the scale down version of SQL Server 7.0)
did not take this much of memory.

[Malcolm]

Is there actually a real problem with the performance of your
application or are you just curious about the memory usage of SQL
Server?

It is always better to make questions clearer as they elicit more
specific responses.

i.e. the subject of the internal workings of SQL Server and Windows
virtual memory allocation could take a great many posts to explain and
you would probably be better off reading books such as Inside SQL
Server or The Gurus Guide To SQL Server Architecture and Internals.

[Bardolator]

Its both curiosity and a bug. A machine with 512 MB (this is all we can
ask our customer) that has MSDE and it's database is just 26 MB takes
around 200 MB of RAM. This seems to be a problem. SQL 7.0 doesn't use
to do this.

[Malcolm]

You haven't answered my question you have simply restated the memory
usage observation. Is this actually a performance problem?

Is your application noticably slower with the 2K version of MSDE?

Are other applications running on the same machine crawling to a halt?

SQL Server will rightly take as much memory as it can but should be a
good citizen when it comes to playing nicely with other apps demands.

Malc
www.dbghost.com - Build, Compare and Synchronize = Database Change
Management for SQL Server

[Erland Sommarskog]

Bardolator (Ba********@gmail.com) writes:

Its both curiosity and a bug. A machine with 512 MB (this is all we can
ask our customer) that has MSDE and it's database is just 26 MB takes
around 200 MB of RAM. This seems to be a problem. SQL 7.0 doesn't use
to do this.

Just because you don't like, does not mean that it is a bug.

Use sp_configure to constrain how much memory SQL Server may use.

exec sp_configure 'max server memory', 100
go
reconfigure

100 is 100 megabyte.

You have have to first say:

exec sp_configure 'show advanced', 1
go
reconfigure
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp