Memory Use

Hi there.
I have an application that connects to a SQL Server using ODBC (and
CRecordSets) to peruse and retrieve data from a number of large database
tables. As a result, SQL Server seems to be constantly using up a very large
amount of memory - even when my application doesn't seem to be doing anythin
g.
Q: Is this behaviour expected? The memory settings on the server are
configured to allow SQL Server to allocate the memory Dynamically, using the
total physical RAM.
Does SQL Server try to keep copies of database tables in memory if they've
been queried and the memory is available (in case they're queried again)? -
or should it relinquish that memory once the query has returned (implying
that my application must still have some connections/queries open)?
Any help would be appreciated!> I have an application that connects to a SQL Server using ODBC (and
> CRecordSets) to peruse and retrieve data from a number of large database
> tables. As a result, SQL Server seems to be constantly using up a very
> large
> amount of memory - even when my application doesn't seem to be doing
> anything.

> Q: Is this behaviour expected?
Yes. This behavior is expected, and is in fact, by design, on purpose:
Anything you SELECT is handed back to your application, but then SQL Server
holds that data in a cache, because it thinks that if you SELECT'ed it once,
you might do it again.
There are a couple things I know that you can do to reduce it:
1. Configure the server to use a specified maximum amount of RAM
2. Execute DBCC DROPCLEANBUFFERS to release some? most? of the cache
But be very careful about doing that on a production server. When you're a
developer (like me), you typically have sql server, IIS, outlook, visual
studio, enterprise manager, query analyser, outlook express, windows
explorer, and internet explorer all running at the same time, so this
becomes useful. If you fold back the memory on a production server, you
could very likely cripple the whole network, and wind up in the bosses
office for a meeting.
Guess how I know. <g>
Peace & happy computing,
Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSensei|||Great stuff thanks - that's just what I needed!
Actually, one more question... If SQL server is taking up a huge amount of
memory with this cached data, would that affect the performance of other
applications that may start up and need to use some of that memory? Will it
relinquish some of the memory without a struggle?
"Mike Labosh" wrote:

>
> Yes. This behavior is expected, and is in fact, by design, on purpose:
> Anything you SELECT is handed back to your application, but then SQL Serve
r
> holds that data in a cache, because it thinks that if you SELECT'ed it onc
e,
> you might do it again.
> There are a couple things I know that you can do to reduce it:
> 1. Configure the server to use a specified maximum amount of RAM
> 2. Execute DBCC DROPCLEANBUFFERS to release some? most? of the cache
> But be very careful about doing that on a production server. When you're
a
> developer (like me), you typically have sql server, IIS, outlook, visual
> studio, enterprise manager, query analyser, outlook express, windows
> explorer, and internet explorer all running at the same time, so this
> becomes useful. If you fold back the memory on a production server, you
> could very likely cripple the whole network, and wind up in the bosses
> office for a meeting.
> Guess how I know. <g>
> --
>
> Peace & happy computing,
> Mike Labosh, MCSD MCT
> Owner, vbSensei.Com
> "Escriba coda ergo sum." -- vbSensei
>
>|||SQL Server is very greedy about the memory it eats. ...Just imagine that
Tiger you saw on the Discovery Channel last w.
If you have other things that need to run on the same server where SQL is
installed, you need to administratively tell the different packages of
software how much memory, threads, etc. they are allowed to consume.
Example:
Server abc has SQL Server 2000 and IIS 4. I can set these up to behave
nicely by setting the specific maximum amount of RAM consumed by SQL, and I
can configure the RAM and Thread Pool of IIS within the IIS MMC console and
also the COM+ Console.
But I warn you again, Be VERY careful about doing that on a production
server. On a dev-box, nobody cares.
My laptop has 1 GB of memory, so I have SQL & IIS throttled WAY BACK. That
way, I have enough memory to develop applications in Visual Studio.NET,
check my mail, read these damn groups :) and still run Linux RedHat on a
virtual PC inside a window.
But nobody (hopefully) does that kind of stuff on a production server. If
you are talking about a production SQL Server, it should be setup to have
Dynamic Memory Allocation and eat all the RAM and CPU that it needs.
Peace & happy computing,
Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSensei