Thursday, March 22, 2012

300MB DB uses 1.4GB RAM!?

SQL Server 2000 running nothing but a 300MB database over the course of a
month of continuous use from a single application winds up grabbing 1.4GB of
RAM. Is that normal or excessive? (No performance degradation at all, jut a
lot of RAM allocated.)
This is clustered, but the problem has occurred separately on each node.
Yes, that took over 2 months to test.William Bernat wrote:
> SQL Server 2000 running nothing but a 300MB database over the course of a
> month of continuous use from a single application winds up grabbing 1.4GB
of
> RAM. Is that normal or excessive? (No performance degradation at all, jut
a
> lot of RAM allocated.)
>
> This is clustered, but the problem has occurred separately on each node.
> Yes, that took over 2 months to test.
>
This is normal behavior. SQL Server likes memory, and will use as much
as it can to cache data pages so that it doesn't have to make repeated
trips to disk...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||If the data is only 300mb...what's taking the extra 900mb?
I've seen this before, but nobody ever wanted to pay me to find the answer

Short of mem-to-leave being huge, I can't think of a valid reason for what
William is seeing...
Kevin Hill
3NF Consulting
www.3nf-inc.com
http://kevin3nf.blogspot.com
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:453FB984.1010509@.realsqlguy.com...
> William Bernat wrote:
> This is normal behavior. SQL Server likes memory, and will use as much as
> it can to cache data pages so that it doesn't have to make repeated trips
> to disk...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Kevin3NF wrote:
> If the data is only 300mb...what's taking the extra 900mb?
> I've seen this before, but nobody ever wanted to pay me to find the answer
>
> Short of mem-to-leave being huge, I can't think of a valid reason for what
> William is seeing...
>
Funny how 300mb looks like 300GB when you're reading fast...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Could be execution plans. A quick way to check is using dbcc memorystatus, G
oogle for articles and
usage of that command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"William Bernat" <nospamplease@.unavil.com> wrote in message
news:Oh7MikG%23GHA.4740@.TK2MSFTNGP03.phx.gbl...
> SQL Server 2000 running nothing but a 300MB database over the course of a
> month of continuous use from a single application winds up grabbing 1.4GB
of
> RAM. Is that normal or excessive? (No performance degradation at all, jut
a
> lot of RAM allocated.)
>
> This is clustered, but the problem has occurred separately on each node.
> Yes, that took over 2 months to test.
>|||Could be intermdiate resultsets as well.
Linchi
"William Bernat" wrote:

> SQL Server 2000 running nothing but a 300MB database over the course of a
> month of continuous use from a single application winds up grabbing 1.4GB
of
> RAM. Is that normal or excessive? (No performance degradation at all, jut
a
> lot of RAM allocated.)
>
> This is clustered, but the problem has occurred separately on each node.
> Yes, that took over 2 months to test.
>
>|||Hi!
Hhm... how to say this.
Oracle rdbms is far more better in this point.
If you plan to increase load (users/jobs) consider
switching to Oracle.
Or, is this any issue. RAM Memory is quite cheap.
Jack
"William Bernat" <nospamplease@.unavil.com> wrote in message
news:Oh7MikG%23GHA.4740@.TK2MSFTNGP03.phx.gbl...
> SQL Server 2000 running nothing but a 300MB database over the course of a
> month of continuous use from a single application winds up grabbing 1.4GB
> of
> RAM. Is that normal or excessive? (No performance degradation at all, jut
> a
> lot of RAM allocated.)
>
> This is clustered, but the problem has occurred separately on each node.
> Yes, that took over 2 months to test.
>|||To add to the other responses, SQL Server likes to keep the acquired memory
because memory allocation and deallocation is a relatively expensive
operation. Memory will be release when SQL Server detects pressure. Since
you have no performance issues, it seems like a good strategy in your case.
If you have other applications on the server that use a significant amount
of memory and observe paging during memory spikes, you might consider
setting the maximum SQL Server memory to reduce contention.
Hope this helps.
Dan Guzman
SQL Server MVP
"William Bernat" <nospamplease@.unavil.com> wrote in message
news:Oh7MikG%23GHA.4740@.TK2MSFTNGP03.phx.gbl...
> SQL Server 2000 running nothing but a 300MB database over the course of a
> month of continuous use from a single application winds up grabbing 1.4GB
> of
> RAM. Is that normal or excessive? (No performance degradation at all, jut
> a
> lot of RAM allocated.)
>
> This is clustered, but the problem has occurred separately on each node.
> Yes, that took over 2 months to test.
>|||> If you plan to increase load (users/jobs) consider
> switching to Oracle.
I don't think anything said here would lend enough support to such a big
leap to this conclusion.
Linchi
"Jack" wrote:

> Hi!
> Hhm... how to say this.
> Oracle rdbms is far more better in this point.
> If you plan to increase load (users/jobs) consider
> switching to Oracle.
> Or, is this any issue. RAM Memory is quite cheap.
> Jack
> "William Bernat" <nospamplease@.unavil.com> wrote in message
> news:Oh7MikG%23GHA.4740@.TK2MSFTNGP03.phx.gbl...
>
>|||TempDb, cached plans, open connections, ...
Kevin3NF wrote:
> If the data is only 300mb...what's taking the extra 900mb?
> I've seen this before, but nobody ever wanted to pay me to find the answer
>
> Short of mem-to-leave being huge, I can't think of a valid reason for what
> William is seeing...
>

No comments:

Post a Comment