I am experiencing the following problem;
I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
Task manager. It is ca 20 mb, everything is OK. Then I run this query;
select postoffice, count(*) as counter from tblTable
where postoffice is not null
group by postoffice
order by counter DESC
There's no index or constraint on field "postoffice" and tblTable contains
ca 916.000 rows. I thought this query would still execute in only a few
seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
memory usage grows to about 300-400 mb when the query runs. What am I doing
wrong?
Tnx in advance!
/Magnus
Hi
916'000 rows and no index? It is best to have an index for larger tables
else it has to load each row and look at it.
Have a look at http://www.sql-server-performance.com/ for some ideas.
INF: SQL Server Memory Usage:
http://support.microsoft.com/default...;en-us;q321363
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Magnus ?sterberg" wrote:
> I am experiencing the following problem;
> I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
> Task manager. It is ca 20 mb, everything is OK. Then I run this query;
> select postoffice, count(*) as counter from tblTable
> where postoffice is not null
> group by postoffice
> order by counter DESC
> There's no index or constraint on field "postoffice" and tblTable contains
> ca 916.000 rows. I thought this query would still execute in only a few
> seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
> memory usage grows to about 300-400 mb when the query runs. What am I doing
> wrong?
> Tnx in advance!
> /Magnus
>
>
|||On Mon, 29 Aug 2005 12:31:12 +0300, "Magnus sterberg"
<magnus.osterberg@.abo.fi> wrote:
>I am experiencing the following problem;
>I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
>Task manager. It is ca 20 mb, everything is OK. Then I run this query;
>select postoffice, count(*) as counter from tblTable
>where postoffice is not null
>group by postoffice
>order by counter DESC
>There's no index or constraint on field "postoffice" and tblTable contains
>ca 916.000 rows. I thought this query would still execute in only a few
>seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
>memory usage grows to about 300-400 mb when the query runs. What am I doing
>wrong?
That's the entire query? Nobody else on the machine? Machine has how
much ram, 512mb? 1gb? More? Hmm. I'm going to guess your machine
is only 512mb, that that table totals more than 512mb, on a cold run
of course none of the data will be cached in RAM, and yes, SQLServer
can be quite slow when it has to do physical IO, that is, far slower
than when the data is in cache. SQLServer is designed to grab all
available memory if it has even the least interest in doing so, so the
growth to 400mb doesn't surprise me at all.
You may also be getting an "autostats" on the postoffice column which
will take some time, you can turn on the profiler, make sure you
capture the object/autostats event, and get some idea.
How long does it take if you immediately rerun the same query? Lot
faster?
J.
J.
Showing posts with label usage. Show all posts
Showing posts with label usage. Show all posts
Monday, March 19, 2012
Memory usage\Performance problem
I am experiencing the following problem;
I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
Task manager. It is ca 20 mb, everything is OK. Then I run this query;
select postoffice, count(*) as counter from tblTable
where postoffice is not null
group by postoffice
order by counter DESC
There's no index or constraint on field "postoffice" and tblTable contains
ca 916.000 rows. I thought this query would still execute in only a few
seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
memory usage grows to about 300-400 mb when the query runs. What am I doing
wrong'
Tnx in advance!
/MagnusHi
916'000 rows and no index? It is best to have an index for larger tables
else it has to load each row and look at it.
Have a look at http://www.sql-server-performance.com/ for some ideas.
INF: SQL Server Memory Usage:
http://support.microsoft.com/defaul...b;en-us;q321363
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Magnus ?sterberg" wrote:
> I am experiencing the following problem;
> I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage i
n
> Task manager. It is ca 20 mb, everything is OK. Then I run this query;
> select postoffice, count(*) as counter from tblTable
> where postoffice is not null
> group by postoffice
> order by counter DESC
> There's no index or constraint on field "postoffice" and tblTable contains
> ca 916.000 rows. I thought this query would still execute in only a few
> seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
> memory usage grows to about 300-400 mb when the query runs. What am I doin
g
> wrong'
> Tnx in advance!
> /Magnus
>
>|||On Mon, 29 Aug 2005 12:31:12 +0300, "Magnus sterberg"
<magnus.osterberg@.abo.fi> wrote:
>I am experiencing the following problem;
>I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
>Task manager. It is ca 20 mb, everything is OK. Then I run this query;
>select postoffice, count(*) as counter from tblTable
>where postoffice is not null
>group by postoffice
>order by counter DESC
>There's no index or constraint on field "postoffice" and tblTable contains
>ca 916.000 rows. I thought this query would still execute in only a few
>seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
>memory usage grows to about 300-400 mb when the query runs. What am I doing
>wrong'
That's the entire query? Nobody else on the machine? Machine has how
much ram, 512mb? 1gb? More? Hmm. I'm going to guess your machine
is only 512mb, that that table totals more than 512mb, on a cold run
of course none of the data will be cached in RAM, and yes, SQLServer
can be quite slow when it has to do physical IO, that is, far slower
than when the data is in cache. SQLServer is designed to grab all
available memory if it has even the least interest in doing so, so the
growth to 400mb doesn't surprise me at all.
You may also be getting an "autostats" on the postoffice column which
will take some time, you can turn on the profiler, make sure you
capture the object/autostats event, and get some idea.
How long does it take if you immediately rerun the same query? Lot
faster?
J.
J.
I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
Task manager. It is ca 20 mb, everything is OK. Then I run this query;
select postoffice, count(*) as counter from tblTable
where postoffice is not null
group by postoffice
order by counter DESC
There's no index or constraint on field "postoffice" and tblTable contains
ca 916.000 rows. I thought this query would still execute in only a few
seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
memory usage grows to about 300-400 mb when the query runs. What am I doing
wrong'
Tnx in advance!
/MagnusHi
916'000 rows and no index? It is best to have an index for larger tables
else it has to load each row and look at it.
Have a look at http://www.sql-server-performance.com/ for some ideas.
INF: SQL Server Memory Usage:
http://support.microsoft.com/defaul...b;en-us;q321363
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Magnus ?sterberg" wrote:
> I am experiencing the following problem;
> I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage i
n
> Task manager. It is ca 20 mb, everything is OK. Then I run this query;
> select postoffice, count(*) as counter from tblTable
> where postoffice is not null
> group by postoffice
> order by counter DESC
> There's no index or constraint on field "postoffice" and tblTable contains
> ca 916.000 rows. I thought this query would still execute in only a few
> seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
> memory usage grows to about 300-400 mb when the query runs. What am I doin
g
> wrong'
> Tnx in advance!
> /Magnus
>
>|||On Mon, 29 Aug 2005 12:31:12 +0300, "Magnus sterberg"
<magnus.osterberg@.abo.fi> wrote:
>I am experiencing the following problem;
>I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
>Task manager. It is ca 20 mb, everything is OK. Then I run this query;
>select postoffice, count(*) as counter from tblTable
>where postoffice is not null
>group by postoffice
>order by counter DESC
>There's no index or constraint on field "postoffice" and tblTable contains
>ca 916.000 rows. I thought this query would still execute in only a few
>seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
>memory usage grows to about 300-400 mb when the query runs. What am I doing
>wrong'
That's the entire query? Nobody else on the machine? Machine has how
much ram, 512mb? 1gb? More? Hmm. I'm going to guess your machine
is only 512mb, that that table totals more than 512mb, on a cold run
of course none of the data will be cached in RAM, and yes, SQLServer
can be quite slow when it has to do physical IO, that is, far slower
than when the data is in cache. SQLServer is designed to grab all
available memory if it has even the least interest in doing so, so the
growth to 400mb doesn't surprise me at all.
You may also be getting an "autostats" on the postoffice column which
will take some time, you can turn on the profiler, make sure you
capture the object/autostats event, and get some idea.
How long does it take if you immediately rerun the same query? Lot
faster?
J.
J.
Memory usage\Performance problem
I am experiencing the following problem;
I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
Task manager. It is ca 20 mb, everything is OK. Then I run this query;
select postoffice, count(*) as counter from tblTable
where postoffice is not null
group by postoffice
order by counter DESC
There's no index or constraint on field "postoffice" and tblTable contains
ca 916.000 rows. I thought this query would still execute in only a few
seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
memory usage grows to about 300-400 mb when the query runs. What am I doing
wrong??
Tnx in advance!
/MagnusMagnus sterberg (magnus.osterberg@.abo.fi) writes:
> I am experiencing the following problem;
> I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage
> in Task manager. It is ca 20 mb, everything is OK. Then I run this
> query;
> select postoffice, count(*) as counter from tblTable
> where postoffice is not null
> group by postoffice
> order by counter DESC
> There's no index or constraint on field "postoffice" and tblTable
> contains ca 916.000 rows. I thought this query would still execute in
> only a few seconds, but it takes minutes. And the worst thing is that
> sqlserver.exe's memory usage grows to about 300-400 mb when the query
> runs. What am I doing wrong??
So what is the average row size of this table? Say that is 300 bytes,
then that is 300 MB of data to read. That is not very likely to be done
instantly.
SQL Server's memory consumption will increase, as it will read the entire
table into cache, and the table will stay in the cache as long as no
other data competes about the space. This means that if you resubmit the
query, the response time will be significantly shorter.
SQL Server is designed to grab as much memory it can, as the more data
in can have in cache, the better the response times. If there are other
applications competing for memory on the machine, SQL Server will yield,
but in this case it may be better to configure how much memory you want
SQL Server to use.
Note also that framgmenation could cause extra delay. Use DBCC SHOWCONTIG
to see what shape the table is in. To defragment it, you would have to
create a clustered index on the table, and then drop that index.
The query itself would benefit enormously by a non-clustered index on
postoffice.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96C183A0F9A0DYazorman@.127.0.0.1...
> Magnus sterberg (magnus.osterberg@.abo.fi) writes:
>> I am experiencing the following problem;
>>
>> I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage
>> in Task manager. It is ca 20 mb, everything is OK. Then I run this
>> query;
>>
>> select postoffice, count(*) as counter from tblTable
>> where postoffice is not null
>> group by postoffice
>> order by counter DESC
>>
>> There's no index or constraint on field "postoffice" and tblTable
>> contains ca 916.000 rows. I thought this query would still execute in
>> only a few seconds, but it takes minutes. And the worst thing is that
>> sqlserver.exe's memory usage grows to about 300-400 mb when the query
>> runs. What am I doing wrong??
> So what is the average row size of this table? Say that is 300 bytes,
> then that is 300 MB of data to read. That is not very likely to be done
> instantly.
> SQL Server's memory consumption will increase, as it will read the entire
> table into cache, and the table will stay in the cache as long as no
> other data competes about the space. This means that if you resubmit the
> query, the response time will be significantly shorter.
> SQL Server is designed to grab as much memory it can, as the more data
> in can have in cache, the better the response times. If there are other
> applications competing for memory on the machine, SQL Server will yield,
> but in this case it may be better to configure how much memory you want
> SQL Server to use.
> Note also that framgmenation could cause extra delay. Use DBCC SHOWCONTIG
> to see what shape the table is in. To defragment it, you would have to
> create a clustered index on the table, and then drop that index.
> The query itself would benefit enormously by a non-clustered index on
> postoffice.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
Thanks for a well-written answer!
I guess my expectations on selecting based on a non-indexed column were
somewhat high. When I added an index, my query executes in seconds.
One index solved this problem, but my table contains 30-40 similar columns,
and I don't think indexing every column is a good idea.
Isn't there any other ways of speeding up selects? Well, I guess not...
/Magnus|||Magnus sterberg (magnus.osterberg@.abo.fi) writes:
> I guess my expectations on selecting based on a non-indexed column were
> somewhat high. When I added an index, my query executes in seconds. One
> index solved this problem, but my table contains 30-40 similar columns,
> and I don't think indexing every column is a good idea.
> Isn't there any other ways of speeding up selects? Well, I guess not...
Well, once data is in cache it will be faster. Or at least less slow.
But if you need to do this on every column, it sounds to me like one
of those things Analysis Services is good for.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Magnus sterberg wrote:
> I guess my expectations on selecting based on a non-indexed column
> were somewhat high. When I added an index, my query executes in
> seconds.
> One index solved this problem, but my table contains 30-40 similar
> columns, and I don't think indexing every column is a good idea.
Two things come to mind:
1. rethink your table design. If these columns are so similar you might
be better off with a second table which is joined. But that of course
depends on your data - just an idea.
2. Create indexes (possibly composite indexes) for most used queries. You
might even get away with a single (or few) composite index if your queries
only use a leading portion of this index's fields.
> Isn't there any other ways of speeding up selects? Well, I guess
> not...
Well, there are tons of other options, like having data files on several
disks, putting tx log on a separate disk, adding memory, ... It all
depends. :-)
Kind regards
robert|||Thank you Robert, these all are things that I'll keep in mind.
One more thing though; why is the sqlservr.exe process consuming 395 mb RAM
when the entire server is idle?
My "cron job" is inserting a few 1000 rows of data at night, but now it is
late morning here in Finland, and the entire machine is more or less idle.
On my other servers, the RAM consumption on idle is about 20-30 mb only.
Anyone got any ideas?
/Magnus
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:3ngkc3F1dnm7U1@.individual.net...
> Magnus sterberg wrote:
>> I guess my expectations on selecting based on a non-indexed column
>> were somewhat high. When I added an index, my query executes in
>> seconds.
>> One index solved this problem, but my table contains 30-40 similar
>> columns, and I don't think indexing every column is a good idea.
> Two things come to mind:
> 1. rethink your table design. If these columns are so similar you might
> be better off with a second table which is joined. But that of course
> depends on your data - just an idea.
> 2. Create indexes (possibly composite indexes) for most used queries. You
> might even get away with a single (or few) composite index if your queries
> only use a leading portion of this index's fields.
>> Isn't there any other ways of speeding up selects? Well, I guess
>> not...
> Well, there are tons of other options, like having data files on several
> disks, putting tx log on a separate disk, adding memory, ... It all
> depends. :-)
> Kind regards
> robert|||Magnus sterberg (magnus.osterberg@.abo.fi) writes:
> One more thing though; why is the sqlservr.exe process consuming 395 mb
> RAM when the entire server is idle?
I believe that was in my first reply.
SQL Server is designed to get as much memory as it can, and only yield
if an another application needs it. This is because it keeps data in
cache so that future requests for the same data can be answered without
reading from disk.
Thus, this is perfectly normal behaviour.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> Magnus sterberg (magnus.osterberg@.abo.fi) writes:
>> One more thing though; why is the sqlservr.exe process consuming 395
>> mb RAM when the entire server is idle?
> I believe that was in my first reply.
> SQL Server is designed to get as much memory as it can, and only yield
> if an another application needs it. This is because it keeps data in
> cache so that future requests for the same data can be answered
> without reading from disk.
> Thus, this is perfectly normal behaviour.
Adding to that max memory consumption is easily configurable so if 400MB
is too much for you then simply turn that down.
Kind regards
robert
Memory usage\Performance problem
I am experiencing the following problem;
I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
Task manager. It is ca 20 mb, everything is OK. Then I run this query;
select postoffice, count(*) as counter from tblTable
where postoffice is not null
group by postoffice
order by counter DESC
There's no index or constraint on field "postoffice" and tblTable contains
ca 916.000 rows. I thought this query would still execute in only a few
seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
memory usage grows to about 300-400 mb when the query runs. What am I doing
wrong'
Tnx in advance!
/MagnusHi
916'000 rows and no index? It is best to have an index for larger tables
else it has to load each row and look at it.
Have a look at http://www.sql-server-performance.com/ for some ideas.
INF: SQL Server Memory Usage:
http://support.microsoft.com/default.aspx?scid=kb;en-us;q321363
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Magnus Ã?sterberg" wrote:
> I am experiencing the following problem;
> I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
> Task manager. It is ca 20 mb, everything is OK. Then I run this query;
> select postoffice, count(*) as counter from tblTable
> where postoffice is not null
> group by postoffice
> order by counter DESC
> There's no index or constraint on field "postoffice" and tblTable contains
> ca 916.000 rows. I thought this query would still execute in only a few
> seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
> memory usage grows to about 300-400 mb when the query runs. What am I doing
> wrong'
> Tnx in advance!
> /Magnus
>
>|||On Mon, 29 Aug 2005 12:31:12 +0300, "Magnus Österberg"
<magnus.osterberg@.abo.fi> wrote:
>I am experiencing the following problem;
>I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
>Task manager. It is ca 20 mb, everything is OK. Then I run this query;
>select postoffice, count(*) as counter from tblTable
>where postoffice is not null
>group by postoffice
>order by counter DESC
>There's no index or constraint on field "postoffice" and tblTable contains
>ca 916.000 rows. I thought this query would still execute in only a few
>seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
>memory usage grows to about 300-400 mb when the query runs. What am I doing
>wrong'
That's the entire query? Nobody else on the machine? Machine has how
much ram, 512mb? 1gb? More? Hmm. I'm going to guess your machine
is only 512mb, that that table totals more than 512mb, on a cold run
of course none of the data will be cached in RAM, and yes, SQLServer
can be quite slow when it has to do physical IO, that is, far slower
than when the data is in cache. SQLServer is designed to grab all
available memory if it has even the least interest in doing so, so the
growth to 400mb doesn't surprise me at all.
You may also be getting an "autostats" on the postoffice column which
will take some time, you can turn on the profiler, make sure you
capture the object/autostats event, and get some idea.
How long does it take if you immediately rerun the same query? Lot
faster?
J.
J.
I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
Task manager. It is ca 20 mb, everything is OK. Then I run this query;
select postoffice, count(*) as counter from tblTable
where postoffice is not null
group by postoffice
order by counter DESC
There's no index or constraint on field "postoffice" and tblTable contains
ca 916.000 rows. I thought this query would still execute in only a few
seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
memory usage grows to about 300-400 mb when the query runs. What am I doing
wrong'
Tnx in advance!
/MagnusHi
916'000 rows and no index? It is best to have an index for larger tables
else it has to load each row and look at it.
Have a look at http://www.sql-server-performance.com/ for some ideas.
INF: SQL Server Memory Usage:
http://support.microsoft.com/default.aspx?scid=kb;en-us;q321363
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Magnus Ã?sterberg" wrote:
> I am experiencing the following problem;
> I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
> Task manager. It is ca 20 mb, everything is OK. Then I run this query;
> select postoffice, count(*) as counter from tblTable
> where postoffice is not null
> group by postoffice
> order by counter DESC
> There's no index or constraint on field "postoffice" and tblTable contains
> ca 916.000 rows. I thought this query would still execute in only a few
> seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
> memory usage grows to about 300-400 mb when the query runs. What am I doing
> wrong'
> Tnx in advance!
> /Magnus
>
>|||On Mon, 29 Aug 2005 12:31:12 +0300, "Magnus Österberg"
<magnus.osterberg@.abo.fi> wrote:
>I am experiencing the following problem;
>I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
>Task manager. It is ca 20 mb, everything is OK. Then I run this query;
>select postoffice, count(*) as counter from tblTable
>where postoffice is not null
>group by postoffice
>order by counter DESC
>There's no index or constraint on field "postoffice" and tblTable contains
>ca 916.000 rows. I thought this query would still execute in only a few
>seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
>memory usage grows to about 300-400 mb when the query runs. What am I doing
>wrong'
That's the entire query? Nobody else on the machine? Machine has how
much ram, 512mb? 1gb? More? Hmm. I'm going to guess your machine
is only 512mb, that that table totals more than 512mb, on a cold run
of course none of the data will be cached in RAM, and yes, SQLServer
can be quite slow when it has to do physical IO, that is, far slower
than when the data is in cache. SQLServer is designed to grab all
available memory if it has even the least interest in doing so, so the
growth to 400mb doesn't surprise me at all.
You may also be getting an "autostats" on the postoffice column which
will take some time, you can turn on the profiler, make sure you
capture the object/autostats event, and get some idea.
How long does it take if you immediately rerun the same query? Lot
faster?
J.
J.
memory usage??
Hello:
I have a server with almost 4 Gigs of RAM. Though SQL
Server's memory config option allows me to allocate all of
the available memory, I understand that Std. Ed. can only
address upto a max. of 2 GB.
So, I set this option to 2 GB and lo and behold, once the
application floodgates were opened, SQL's target and total
server memory consumption rose to and continues to hover
around the 1.7 GB mark.
At this time, I try to run a 'select' against a view and
after a minute of execution, I get error 701 (There is
insufficient system memory to run this query).
My understanding is that, while SQL will take as much
memory as it can and will probably hold on to it as well,
it will release chunks of memory back to the OS for other
processes. In this though, the process call was from
within SQL Server during a low peak application usage time.
To make my long story short, I'm not sure if SQL Server is
holding on to something, which is avoiding this query to
be executed successfully... OR, if the application has
some lingering processes against SQL, which coul be
causing it grief.
I hope this makes sense. And thank you in advance for all
your responses.to be more precise, SE allows 2GB of address space, which
can be mapped to physical memory, but not always.
by default, s2k allocates 256MB of address space to memory
structures other than the buffer cache. In many cases,
only a small amount of the 256M of address space for other
structures actually gets mapped to physical memory.
hence the common observation of 1.74GB of memory actually
being used by sql svr.
what is your query doing?
does it access a very large number of rows?
does it involve a large amount of intermediate data?
it could be your query requires a very large amount of the
memory structures other than the buffer cache.
does your query run immediately after SQL starts up,
before other users drive up memory usage (to the buffer
cache) ie, does this error message only happen after a
very large amount of memory is used by SQL.
if so, then your app would probably run best on a full 64-
bit OS/APP, the AWE is of no use
>--Original Message--
>Hello:
>I have a server with almost 4 Gigs of RAM. Though SQL
>Server's memory config option allows me to allocate all
of
>the available memory, I understand that Std. Ed. can only
>address upto a max. of 2 GB.
>So, I set this option to 2 GB and lo and behold, once the
>application floodgates were opened, SQL's target and
total
>server memory consumption rose to and continues to hover
>around the 1.7 GB mark.
>At this time, I try to run a 'select' against a view and
>after a minute of execution, I get error 701 (There is
>insufficient system memory to run this query).
>My understanding is that, while SQL will take as much
>memory as it can and will probably hold on to it as well,
>it will release chunks of memory back to the OS for other
>processes. In this though, the process call was from
>within SQL Server during a low peak application usage
time.
>To make my long story short, I'm not sure if SQL Server
is
>holding on to something, which is avoiding this query to
>be executed successfully... OR, if the application has
>some lingering processes against SQL, which coul be
>causing it grief.
>I hope this makes sense. And thank you in advance for all
>your responses.
>.
>
I have a server with almost 4 Gigs of RAM. Though SQL
Server's memory config option allows me to allocate all of
the available memory, I understand that Std. Ed. can only
address upto a max. of 2 GB.
So, I set this option to 2 GB and lo and behold, once the
application floodgates were opened, SQL's target and total
server memory consumption rose to and continues to hover
around the 1.7 GB mark.
At this time, I try to run a 'select' against a view and
after a minute of execution, I get error 701 (There is
insufficient system memory to run this query).
My understanding is that, while SQL will take as much
memory as it can and will probably hold on to it as well,
it will release chunks of memory back to the OS for other
processes. In this though, the process call was from
within SQL Server during a low peak application usage time.
To make my long story short, I'm not sure if SQL Server is
holding on to something, which is avoiding this query to
be executed successfully... OR, if the application has
some lingering processes against SQL, which coul be
causing it grief.
I hope this makes sense. And thank you in advance for all
your responses.to be more precise, SE allows 2GB of address space, which
can be mapped to physical memory, but not always.
by default, s2k allocates 256MB of address space to memory
structures other than the buffer cache. In many cases,
only a small amount of the 256M of address space for other
structures actually gets mapped to physical memory.
hence the common observation of 1.74GB of memory actually
being used by sql svr.
what is your query doing?
does it access a very large number of rows?
does it involve a large amount of intermediate data?
it could be your query requires a very large amount of the
memory structures other than the buffer cache.
does your query run immediately after SQL starts up,
before other users drive up memory usage (to the buffer
cache) ie, does this error message only happen after a
very large amount of memory is used by SQL.
if so, then your app would probably run best on a full 64-
bit OS/APP, the AWE is of no use
>--Original Message--
>Hello:
>I have a server with almost 4 Gigs of RAM. Though SQL
>Server's memory config option allows me to allocate all
of
>the available memory, I understand that Std. Ed. can only
>address upto a max. of 2 GB.
>So, I set this option to 2 GB and lo and behold, once the
>application floodgates were opened, SQL's target and
total
>server memory consumption rose to and continues to hover
>around the 1.7 GB mark.
>At this time, I try to run a 'select' against a view and
>after a minute of execution, I get error 701 (There is
>insufficient system memory to run this query).
>My understanding is that, while SQL will take as much
>memory as it can and will probably hold on to it as well,
>it will release chunks of memory back to the OS for other
>processes. In this though, the process call was from
>within SQL Server during a low peak application usage
time.
>To make my long story short, I'm not sure if SQL Server
is
>holding on to something, which is avoiding this query to
>be executed successfully... OR, if the application has
>some lingering processes against SQL, which coul be
>causing it grief.
>I hope this makes sense. And thank you in advance for all
>your responses.
>.
>
Memory Usage/Hog
I have a database that starts out as 9Mb. Never grows to more than 15Mb.
However, after running an endurance test for 5 days, the sqlservr process in
TaskMan has grown to over 550Mb VM size.
Can anyone help me understand how a 15 Mb database can consume 550Mb of VM
space and never give it back? And/or how to encourage sqlservr to return
memory that it shouldn't be using?
Thanks in advance,
Dan
P.S. BTW I already know that min & max server memory configuration settings
are really just "suggestions"!
Daniel,
The min and max settings are for the memory pool only and there is another
section of memory called the MemToLeave area that is not included in those
limits. This includes such things as the SQL Server exe, dll', extended
procs, net libs etc. Even the worker threads can use up to 128MB by
default. But more than likely you are seeing the results of the procedure
cache (which is included in the memory pool). Especiallyif you are stress
testing with adhoc queries. SQL Server will use as much memory as is
available up until the limits if any are set. Even if your data only
amounts to 15MB you can have many thousands of cached plans in the procedure
cache and they will stay there until the OS asks for some of SQL Servers
memory. By default the OS and SS will dynamically share the available
memory but if the OS never asks for it SS will keep it. So if you never set
a limit SS expects to use all that it can and that is by design and usually
works best.
Andrew J. Kelly SQL MVP
"daniel.ferguson" <daniel.ferguson@.discussions.microsoft.com> wrote in
message news:8A46C920-A536-42BC-8E26-B71D0ABB9648@.microsoft.com...
>I have a database that starts out as 9Mb. Never grows to more than 15Mb.
> However, after running an endurance test for 5 days, the sqlservr process
> in
> TaskMan has grown to over 550Mb VM size.
> Can anyone help me understand how a 15 Mb database can consume 550Mb of VM
> space and never give it back? And/or how to encourage sqlservr to return
> memory that it shouldn't be using?
> Thanks in advance,
> Dan
> P.S. BTW I already know that min & max server memory configuration
> settings
> are really just "suggestions"!
>
|||Andrew,
Thanks for the response. Your information agrees with what I have run
across searching the Net and the KB. If you don't mind fielding a couple
more questions, I'm curious about the mechanism that the OS uses to ask SQL
Server for the memory back. Is there a 'secret handshake' between the OS
and the service that's not disclosed to the public? I'm pretty familiar
with the Win32 SDK and the only things I know of is the WM_COMPACTING
message - but it's only sent to top-level windows - something that SQL
Server running as a service doesn't have.
Secondly, without having more detail information about my application, it
would be difficult to predict how many "cached plans" there actually are
would be, with about 18 to 20 queries with nearly identical predicates, why
wouldn't SQL Server "reuse" the cached plans? Isn't that what the "cache"
is for?
Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
64Mb max - after about 18 hrs, sqlservr.exe had grown to a little over 79 Mb
VM without any increase today. So far, performance of the overall
application hasn't shown any degradation. This is on a 512Mb box -getting
SQL Server to 'play well with others' is critical because our target
platform is 256Mb! (No choice and not open to discussion)
BTW: Is cascading deletes as resource intensive as it appears to be?
Thanks,
Dan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uIVG7ds8EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Daniel,
> The min and max settings are for the memory pool only and there is another
> section of memory called the MemToLeave area that is not included in those
> limits. This includes such things as the SQL Server exe, dll', extended
> procs, net libs etc. Even the worker threads can use up to 128MB by
> default. But more than likely you are seeing the results of the procedure
> cache (which is included in the memory pool). Especiallyif you are stress
> testing with adhoc queries. SQL Server will use as much memory as is
> available up until the limits if any are set. Even if your data only
> amounts to 15MB you can have many thousands of cached plans in the
> procedure cache and they will stay there until the OS asks for some of SQL
> Servers memory. By default the OS and SS will dynamically share the
> available memory but if the OS never asks for it SS will keep it. So if
> you never set a limit SS expects to use all that it can and that is by
> design and usually works best.
> --
> Andrew J. Kelly SQL MVP
>
> "daniel.ferguson" <daniel.ferguson@.discussions.microsoft.com> wrote in
> message news:8A46C920-A536-42BC-8E26-B71D0ABB9648@.microsoft.com...
>
|||> and the service that's not disclosed to the public? I'm pretty familiar
> with the Win32 SDK and the only things I know of is the WM_COMPACTING
> message - but it's only sent to top-level windows - something that SQL
> Server running as a service doesn't have.
To be honest I knew exactlkly what it was at one time as one of the SQL
Server developers told me but don't recall what it is. Other than the
settings in SQL Server (and maybe some of the Win2003 management features)
you can't really do anything special otherwise.
> Secondly, without having more detail information about my application, it
> would be difficult to predict how many "cached plans" there actually are
> would be, with about 18 to 20 queries with nearly identical predicates,
> why wouldn't SQL Server "reuse" the cached plans? Isn't that what the
> "cache" is for?
Nearly identical is not good enough if the calls are not done properly. If
you use RPC calls and specify parameters properly the plans should be cached
and reused. Sending in a SQL string and executing it will result in a new
plan for each variation of the string including spaces. If you only have 20
different types of queries then syscacheobjects ideally should only have
about 100 rows or so in it at any particular time. What does select
COUNT(*) FROM Master..Syscacheobjects give you?
> Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
> 64Mb max - after about 18 hrs, sqlservr.exe had grown to a little over 79
> Mb
Well here is the official minimum requirements from BOL:
Enterprise Edition and Enterprise Evaluation Edition: 64 MB minimum, 128 MB
or more recommended
Standard Edition and Developer Edition: 64 MB minimum
Personal Edition and Desktop Engine (MSDE 2000):
a.. 128 MB minimum on Windows XP
b.. 64 MB minimum on Windows 2000
c.. 32 MB minimum on all other operating systems
Don't know what edition you plan to run and what you are doing with it but
good luck with PC's with 256MB's<g>.
> BTW: Is cascading deletes as resource intensive as it appears to be?
It all depends. You need proper indexes and RI to make it work properly but
it is simply a series of index seeks and deletes.
Andrew J. Kelly SQL MVP
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:e7XQNI28EHA.1524@.TK2MSFTNGP09.phx.gbl...
|||> Secondly, without having more detail information about my application, it would be difficult to
> predict how many "cached plans" there actually are would be, with about 18 to 20 queries with
> nearly identical predicates, why wouldn't SQL Server "reuse" the cached plans?
The key word here is "nearly". There are several ways a query plan can be re-used. Stored procedure
(best), explicit prepare and then execute, auto parameteration, and ad-hoc (worst). My guess is that
you have ad-hoc for which the query text has to be *identical*. I suggest you investigate using the
system table syscacheobjects in the master database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:e7XQNI28EHA.1524@.TK2MSFTNGP09.phx.gbl...
> Andrew,
> Thanks for the response. Your information agrees with what I have run across searching the Net
> and the KB. If you don't mind fielding a couple more questions, I'm curious about the mechanism
> that the OS uses to ask SQL Server for the memory back. Is there a 'secret handshake' between the
> OS and the service that's not disclosed to the public? I'm pretty familiar with the Win32 SDK and
> the only things I know of is the WM_COMPACTING message - but it's only sent to top-level windows -
> something that SQL Server running as a service doesn't have.
> Secondly, without having more detail information about my application, it would be difficult to
> predict how many "cached plans" there actually are would be, with about 18 to 20 queries with
> nearly identical predicates, why wouldn't SQL Server "reuse" the cached plans? Isn't that what
> the "cache" is for?
> Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and 64Mb max - after about
> 18 hrs, sqlservr.exe had grown to a little over 79 Mb VM without any increase today. So far,
> performance of the overall application hasn't shown any degradation. This is on a 512Mb
> box -getting SQL Server to 'play well with others' is critical because our target platform is
> 256Mb! (No choice and not open to discussion)
> BTW: Is cascading deletes as resource intensive as it appears to be?
> Thanks,
> Dan
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uIVG7ds8EHA.1408@.TK2MSFTNGP10.phx.gbl...
>
|||Hi
No secret handshake. SQL Server only used publicly published API's (AFAIK,
SQL server calls malloc to allocate memory)
I can't find the API, but the OS sends a message requesting memory release
and then SQL Server tries to release it if possible (realloc or free).
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:e7XQNI28EHA.1524@.TK2MSFTNGP09.phx.gbl...
> Andrew,
> Thanks for the response. Your information agrees with what I have run
> across searching the Net and the KB. If you don't mind fielding a couple
> more questions, I'm curious about the mechanism that the OS uses to ask
SQL
> Server for the memory back. Is there a 'secret handshake' between the OS
> and the service that's not disclosed to the public? I'm pretty familiar
> with the Win32 SDK and the only things I know of is the WM_COMPACTING
> message - but it's only sent to top-level windows - something that SQL
> Server running as a service doesn't have.
> Secondly, without having more detail information about my application, it
> would be difficult to predict how many "cached plans" there actually are
> would be, with about 18 to 20 queries with nearly identical predicates,
why
> wouldn't SQL Server "reuse" the cached plans? Isn't that what the "cache"
> is for?
> Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
> 64Mb max - after about 18 hrs, sqlservr.exe had grown to a little over 79
Mb[vbcol=seagreen]
> VM without any increase today. So far, performance of the overall
> application hasn't shown any degradation. This is on a 512Mb box -getting
> SQL Server to 'play well with others' is critical because our target
> platform is 256Mb! (No choice and not open to discussion)
> BTW: Is cascading deletes as resource intensive as it appears to be?
> Thanks,
> Dan
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uIVG7ds8EHA.1408@.TK2MSFTNGP10.phx.gbl...
another[vbcol=seagreen]
those[vbcol=seagreen]
procedure[vbcol=seagreen]
stress[vbcol=seagreen]
SQL[vbcol=seagreen]
15Mb.[vbcol=seagreen]
process[vbcol=seagreen]
return
>
However, after running an endurance test for 5 days, the sqlservr process in
TaskMan has grown to over 550Mb VM size.
Can anyone help me understand how a 15 Mb database can consume 550Mb of VM
space and never give it back? And/or how to encourage sqlservr to return
memory that it shouldn't be using?
Thanks in advance,
Dan
P.S. BTW I already know that min & max server memory configuration settings
are really just "suggestions"!
Daniel,
The min and max settings are for the memory pool only and there is another
section of memory called the MemToLeave area that is not included in those
limits. This includes such things as the SQL Server exe, dll', extended
procs, net libs etc. Even the worker threads can use up to 128MB by
default. But more than likely you are seeing the results of the procedure
cache (which is included in the memory pool). Especiallyif you are stress
testing with adhoc queries. SQL Server will use as much memory as is
available up until the limits if any are set. Even if your data only
amounts to 15MB you can have many thousands of cached plans in the procedure
cache and they will stay there until the OS asks for some of SQL Servers
memory. By default the OS and SS will dynamically share the available
memory but if the OS never asks for it SS will keep it. So if you never set
a limit SS expects to use all that it can and that is by design and usually
works best.
Andrew J. Kelly SQL MVP
"daniel.ferguson" <daniel.ferguson@.discussions.microsoft.com> wrote in
message news:8A46C920-A536-42BC-8E26-B71D0ABB9648@.microsoft.com...
>I have a database that starts out as 9Mb. Never grows to more than 15Mb.
> However, after running an endurance test for 5 days, the sqlservr process
> in
> TaskMan has grown to over 550Mb VM size.
> Can anyone help me understand how a 15 Mb database can consume 550Mb of VM
> space and never give it back? And/or how to encourage sqlservr to return
> memory that it shouldn't be using?
> Thanks in advance,
> Dan
> P.S. BTW I already know that min & max server memory configuration
> settings
> are really just "suggestions"!
>
|||Andrew,
Thanks for the response. Your information agrees with what I have run
across searching the Net and the KB. If you don't mind fielding a couple
more questions, I'm curious about the mechanism that the OS uses to ask SQL
Server for the memory back. Is there a 'secret handshake' between the OS
and the service that's not disclosed to the public? I'm pretty familiar
with the Win32 SDK and the only things I know of is the WM_COMPACTING
message - but it's only sent to top-level windows - something that SQL
Server running as a service doesn't have.
Secondly, without having more detail information about my application, it
would be difficult to predict how many "cached plans" there actually are
would be, with about 18 to 20 queries with nearly identical predicates, why
wouldn't SQL Server "reuse" the cached plans? Isn't that what the "cache"
is for?
Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
64Mb max - after about 18 hrs, sqlservr.exe had grown to a little over 79 Mb
VM without any increase today. So far, performance of the overall
application hasn't shown any degradation. This is on a 512Mb box -getting
SQL Server to 'play well with others' is critical because our target
platform is 256Mb! (No choice and not open to discussion)
BTW: Is cascading deletes as resource intensive as it appears to be?
Thanks,
Dan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uIVG7ds8EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Daniel,
> The min and max settings are for the memory pool only and there is another
> section of memory called the MemToLeave area that is not included in those
> limits. This includes such things as the SQL Server exe, dll', extended
> procs, net libs etc. Even the worker threads can use up to 128MB by
> default. But more than likely you are seeing the results of the procedure
> cache (which is included in the memory pool). Especiallyif you are stress
> testing with adhoc queries. SQL Server will use as much memory as is
> available up until the limits if any are set. Even if your data only
> amounts to 15MB you can have many thousands of cached plans in the
> procedure cache and they will stay there until the OS asks for some of SQL
> Servers memory. By default the OS and SS will dynamically share the
> available memory but if the OS never asks for it SS will keep it. So if
> you never set a limit SS expects to use all that it can and that is by
> design and usually works best.
> --
> Andrew J. Kelly SQL MVP
>
> "daniel.ferguson" <daniel.ferguson@.discussions.microsoft.com> wrote in
> message news:8A46C920-A536-42BC-8E26-B71D0ABB9648@.microsoft.com...
>
|||> and the service that's not disclosed to the public? I'm pretty familiar
> with the Win32 SDK and the only things I know of is the WM_COMPACTING
> message - but it's only sent to top-level windows - something that SQL
> Server running as a service doesn't have.
To be honest I knew exactlkly what it was at one time as one of the SQL
Server developers told me but don't recall what it is. Other than the
settings in SQL Server (and maybe some of the Win2003 management features)
you can't really do anything special otherwise.
> Secondly, without having more detail information about my application, it
> would be difficult to predict how many "cached plans" there actually are
> would be, with about 18 to 20 queries with nearly identical predicates,
> why wouldn't SQL Server "reuse" the cached plans? Isn't that what the
> "cache" is for?
Nearly identical is not good enough if the calls are not done properly. If
you use RPC calls and specify parameters properly the plans should be cached
and reused. Sending in a SQL string and executing it will result in a new
plan for each variation of the string including spaces. If you only have 20
different types of queries then syscacheobjects ideally should only have
about 100 rows or so in it at any particular time. What does select
COUNT(*) FROM Master..Syscacheobjects give you?
> Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
> 64Mb max - after about 18 hrs, sqlservr.exe had grown to a little over 79
> Mb
Well here is the official minimum requirements from BOL:
Enterprise Edition and Enterprise Evaluation Edition: 64 MB minimum, 128 MB
or more recommended
Standard Edition and Developer Edition: 64 MB minimum
Personal Edition and Desktop Engine (MSDE 2000):
a.. 128 MB minimum on Windows XP
b.. 64 MB minimum on Windows 2000
c.. 32 MB minimum on all other operating systems
Don't know what edition you plan to run and what you are doing with it but
good luck with PC's with 256MB's<g>.
> BTW: Is cascading deletes as resource intensive as it appears to be?
It all depends. You need proper indexes and RI to make it work properly but
it is simply a series of index seeks and deletes.
Andrew J. Kelly SQL MVP
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:e7XQNI28EHA.1524@.TK2MSFTNGP09.phx.gbl...
|||> Secondly, without having more detail information about my application, it would be difficult to
> predict how many "cached plans" there actually are would be, with about 18 to 20 queries with
> nearly identical predicates, why wouldn't SQL Server "reuse" the cached plans?
The key word here is "nearly". There are several ways a query plan can be re-used. Stored procedure
(best), explicit prepare and then execute, auto parameteration, and ad-hoc (worst). My guess is that
you have ad-hoc for which the query text has to be *identical*. I suggest you investigate using the
system table syscacheobjects in the master database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:e7XQNI28EHA.1524@.TK2MSFTNGP09.phx.gbl...
> Andrew,
> Thanks for the response. Your information agrees with what I have run across searching the Net
> and the KB. If you don't mind fielding a couple more questions, I'm curious about the mechanism
> that the OS uses to ask SQL Server for the memory back. Is there a 'secret handshake' between the
> OS and the service that's not disclosed to the public? I'm pretty familiar with the Win32 SDK and
> the only things I know of is the WM_COMPACTING message - but it's only sent to top-level windows -
> something that SQL Server running as a service doesn't have.
> Secondly, without having more detail information about my application, it would be difficult to
> predict how many "cached plans" there actually are would be, with about 18 to 20 queries with
> nearly identical predicates, why wouldn't SQL Server "reuse" the cached plans? Isn't that what
> the "cache" is for?
> Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and 64Mb max - after about
> 18 hrs, sqlservr.exe had grown to a little over 79 Mb VM without any increase today. So far,
> performance of the overall application hasn't shown any degradation. This is on a 512Mb
> box -getting SQL Server to 'play well with others' is critical because our target platform is
> 256Mb! (No choice and not open to discussion)
> BTW: Is cascading deletes as resource intensive as it appears to be?
> Thanks,
> Dan
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uIVG7ds8EHA.1408@.TK2MSFTNGP10.phx.gbl...
>
|||Hi
No secret handshake. SQL Server only used publicly published API's (AFAIK,
SQL server calls malloc to allocate memory)
I can't find the API, but the OS sends a message requesting memory release
and then SQL Server tries to release it if possible (realloc or free).
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:e7XQNI28EHA.1524@.TK2MSFTNGP09.phx.gbl...
> Andrew,
> Thanks for the response. Your information agrees with what I have run
> across searching the Net and the KB. If you don't mind fielding a couple
> more questions, I'm curious about the mechanism that the OS uses to ask
SQL
> Server for the memory back. Is there a 'secret handshake' between the OS
> and the service that's not disclosed to the public? I'm pretty familiar
> with the Win32 SDK and the only things I know of is the WM_COMPACTING
> message - but it's only sent to top-level windows - something that SQL
> Server running as a service doesn't have.
> Secondly, without having more detail information about my application, it
> would be difficult to predict how many "cached plans" there actually are
> would be, with about 18 to 20 queries with nearly identical predicates,
why
> wouldn't SQL Server "reuse" the cached plans? Isn't that what the "cache"
> is for?
> Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
> 64Mb max - after about 18 hrs, sqlservr.exe had grown to a little over 79
Mb[vbcol=seagreen]
> VM without any increase today. So far, performance of the overall
> application hasn't shown any degradation. This is on a 512Mb box -getting
> SQL Server to 'play well with others' is critical because our target
> platform is 256Mb! (No choice and not open to discussion)
> BTW: Is cascading deletes as resource intensive as it appears to be?
> Thanks,
> Dan
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uIVG7ds8EHA.1408@.TK2MSFTNGP10.phx.gbl...
another[vbcol=seagreen]
those[vbcol=seagreen]
procedure[vbcol=seagreen]
stress[vbcol=seagreen]
SQL[vbcol=seagreen]
15Mb.[vbcol=seagreen]
process[vbcol=seagreen]
return
>
Memory Usage/Hog
I have a database that starts out as 9Mb. Never grows to more than 15Mb.
However, after running an endurance test for 5 days, the sqlservr process in
TaskMan has grown to over 550Mb VM size.
Can anyone help me understand how a 15 Mb database can consume 550Mb of VM
space and never give it back? And/or how to encourage sqlservr to return
memory that it shouldn't be using?
Thanks in advance,
Dan
P.S. BTW I already know that min & max server memory configuration settings
are really just "suggestions"!Daniel,
The min and max settings are for the memory pool only and there is another
section of memory called the MemToLeave area that is not included in those
limits. This includes such things as the SQL Server exe, dll', extended
procs, net libs etc. Even the worker threads can use up to 128MB by
default. But more than likely you are seeing the results of the procedure
cache (which is included in the memory pool). Especiallyif you are stress
testing with adhoc queries. SQL Server will use as much memory as is
available up until the limits if any are set. Even if your data only
amounts to 15MB you can have many thousands of cached plans in the procedure
cache and they will stay there until the OS asks for some of SQL Servers
memory. By default the OS and SS will dynamically share the available
memory but if the OS never asks for it SS will keep it. So if you never set
a limit SS expects to use all that it can and that is by design and usually
works best.
Andrew J. Kelly SQL MVP
"daniel.ferguson" <daniel.ferguson@.discussions.microsoft.com> wrote in
message news:8A46C920-A536-42BC-8E26-B71D0ABB9648@.microsoft.com...
>I have a database that starts out as 9Mb. Never grows to more than 15Mb.
> However, after running an endurance test for 5 days, the sqlservr process
> in
> TaskMan has grown to over 550Mb VM size.
> Can anyone help me understand how a 15 Mb database can consume 550Mb of VM
> space and never give it back? And/or how to encourage sqlservr to return
> memory that it shouldn't be using?
> Thanks in advance,
> Dan
> P.S. BTW I already know that min & max server memory configuration
> settings
> are really just "suggestions"!
>|||Andrew,
Thanks for the response. Your information agrees with what I have run
across searching the Net and the KB. If you don't mind fielding a couple
more questions, I'm curious about the mechanism that the OS uses to ask SQL
Server for the memory back. Is there a 'secret handshake' between the OS
and the service that's not disclosed to the public? I'm pretty familiar
with the Win32 SDK and the only things I know of is the WM_COMPACTING
message - but it's only sent to top-level windows - something that SQL
Server running as a service doesn't have.
Secondly, without having more detail information about my application, it
would be difficult to predict how many "cached plans" there actually are
would be, with about 18 to 20 queries with nearly identical predicates, why
wouldn't SQL Server "reuse" the cached plans? Isn't that what the "cache"
is for?
Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
64Mb max - after about 18 hrs, sqlservr.exe had grown to a little over 79 Mb
VM without any increase today. So far, performance of the overall
application hasn't shown any degradation. This is on a 512Mb box -getting
SQL Server to 'play well with others' is critical because our target
platform is 256Mb! (No choice and not open to discussion)
BTW: Is cascading deletes as resource intensive as it appears to be?
Thanks,
Dan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uIVG7ds8EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Daniel,
> The min and max settings are for the memory pool only and there is another
> section of memory called the MemToLeave area that is not included in those
> limits. This includes such things as the SQL Server exe, dll', extended
> procs, net libs etc. Even the worker threads can use up to 128MB by
> default. But more than likely you are seeing the results of the procedure
> cache (which is included in the memory pool). Especiallyif you are stress
> testing with adhoc queries. SQL Server will use as much memory as is
> available up until the limits if any are set. Even if your data only
> amounts to 15MB you can have many thousands of cached plans in the
> procedure cache and they will stay there until the OS asks for some of SQL
> Servers memory. By default the OS and SS will dynamically share the
> available memory but if the OS never asks for it SS will keep it. So if
> you never set a limit SS expects to use all that it can and that is by
> design and usually works best.
> --
> Andrew J. Kelly SQL MVP
>
> "daniel.ferguson" <daniel.ferguson@.discussions.microsoft.com> wrote in
> message news:8A46C920-A536-42BC-8E26-B71D0ABB9648@.microsoft.com...
>|||> and the service that's not disclosed to the public? I'm pretty familiar
> with the Win32 SDK and the only things I know of is the WM_COMPACTING
> message - but it's only sent to top-level windows - something that SQL
> Server running as a service doesn't have.
To be honest I knew exactlkly what it was at one time as one of the SQL
Server developers told me but don't recall what it is. Other than the
settings in SQL Server (and maybe some of the Win2003 management features)
you can't really do anything special otherwise.
> Secondly, without having more detail information about my application, it
> would be difficult to predict how many "cached plans" there actually are
> would be, with about 18 to 20 queries with nearly identical predicates,
> why wouldn't SQL Server "reuse" the cached plans? Isn't that what the
> "cache" is for?
Nearly identical is not good enough if the calls are not done properly. If
you use RPC calls and specify parameters properly the plans should be cached
and reused. Sending in a SQL string and executing it will result in a new
plan for each variation of the string including spaces. If you only have 20
different types of queries then syscacheobjects ideally should only have
about 100 rows or so in it at any particular time. What does select
COUNT(*) FROM Master..Syscacheobjects give you?
> Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
> 64Mb max - after about 18 hrs, sqlservr.exe had grown to a little over 79
> Mb
Well here is the official minimum requirements from BOL:
Enterprise Edition and Enterprise Evaluation Edition: 64 MB minimum, 128 MB
or more recommended
Standard Edition and Developer Edition: 64 MB minimum
Personal Edition and Desktop Engine (MSDE 2000):
a.. 128 MB minimum on Windows XP
b.. 64 MB minimum on Windows 2000
c.. 32 MB minimum on all other operating systems
Don't know what edition you plan to run and what you are doing with it but
good luck with PC's with 256MB's<g>.
> BTW: Is cascading deletes as resource intensive as it appears to be?
It all depends. You need proper indexes and RI to make it work properly but
it is simply a series of index seeks and deletes.
Andrew J. Kelly SQL MVP
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:e7XQNI28EHA.1524@.TK2MSFTNGP09.phx.gbl...|||> Secondly, without having more detail information about my application, it would be difficu
lt to
> predict how many "cached plans" there actually are would be, with about 18
to 20 queries with
> nearly identical predicates, why wouldn't SQL Server "reuse" the cached plans?[/vb
col]
The key word here is "nearly". There are several ways a query plan can be re
-used. Stored procedure
(best), explicit prepare and then execute, auto parameteration, and ad-hoc (
worst). My guess is that
you have ad-hoc for which the query text has to be *identical*. I suggest yo
u investigate using the
system table syscacheobjects in the master database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:e7XQNI28EHA.1524@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Andrew,
> Thanks for the response. Your information agrees with what I have run acr
oss searching the Net
> and the KB. If you don't mind fielding a couple more questions, I'm curio
us about the mechanism
> that the OS uses to ask SQL Server for the memory back. Is there a 'secre
t handshake' between the
> OS and the service that's not disclosed to the public? I'm pretty familia
r with the Win32 SDK and
> the only things I know of is the WM_COMPACTING message - but it's only sen
t to top-level windows -
> something that SQL Server running as a service doesn't have.
> Secondly, without having more detail information about my application, it
would be difficult to
> predict how many "cached plans" there actually are would be, with about 18
to 20 queries with
> nearly identical predicates, why wouldn't SQL Server "reuse" the cached pl
ans? Isn't that what
> the "cache" is for?
> Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
64Mb max - after about
> 18 hrs, sqlservr.exe had grown to a little over 79 Mb VM without any incre
ase today. So far,
> performance of the overall application hasn't shown any degradation. This
is on a 512Mb
> box -getting SQL Server to 'play well with others' is critical because our
target platform is
> 256Mb! (No choice and not open to discussion)
> BTW: Is cascading deletes as resource intensive as it appears to be?
> Thanks,
> Dan
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uIVG7ds8EHA.1408@.TK2MSFTNGP10.phx.gbl...
>|||Hi
No secret handshake. SQL Server only used publicly published API's (AFAIK,
SQL server calls malloc to allocate memory)
I can't find the API, but the OS sends a message requesting memory release
and then SQL Server tries to release it if possible (realloc or free).
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:e7XQNI28EHA.1524@.TK2MSFTNGP09.phx.gbl...
> Andrew,
> Thanks for the response. Your information agrees with what I have run
> across searching the Net and the KB. If you don't mind fielding a couple
> more questions, I'm curious about the mechanism that the OS uses to ask
SQL
> Server for the memory back. Is there a 'secret handshake' between the OS
> and the service that's not disclosed to the public? I'm pretty familiar
> with the Win32 SDK and the only things I know of is the WM_COMPACTING
> message - but it's only sent to top-level windows - something that SQL
> Server running as a service doesn't have.
> Secondly, without having more detail information about my application, it
> would be difficult to predict how many "cached plans" there actually are
> would be, with about 18 to 20 queries with nearly identical predicates,
why
> wouldn't SQL Server "reuse" the cached plans? Isn't that what the "cache"
> is for?
> Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
> 64Mb max - after about 18 hrs, sqlservr.exe had grown to a little over 79
Mb
> VM without any increase today. So far, performance of the overall
> application hasn't shown any degradation. This is on a 512Mb box -getting
> SQL Server to 'play well with others' is critical because our target
> platform is 256Mb! (No choice and not open to discussion)
> BTW: Is cascading deletes as resource intensive as it appears to be?
> Thanks,
> Dan
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uIVG7ds8EHA.1408@.TK2MSFTNGP10.phx.gbl...
another[vbcol=seagreen]
those[vbcol=seagreen]
procedure[vbcol=seagreen]
stress[vbcol=seagreen]
SQL[vbcol=seagreen]
15Mb.[vbcol=seagreen]
process[vbcol=seagreen]
return[vbcol=seagreen]
>
However, after running an endurance test for 5 days, the sqlservr process in
TaskMan has grown to over 550Mb VM size.
Can anyone help me understand how a 15 Mb database can consume 550Mb of VM
space and never give it back? And/or how to encourage sqlservr to return
memory that it shouldn't be using?
Thanks in advance,
Dan
P.S. BTW I already know that min & max server memory configuration settings
are really just "suggestions"!Daniel,
The min and max settings are for the memory pool only and there is another
section of memory called the MemToLeave area that is not included in those
limits. This includes such things as the SQL Server exe, dll', extended
procs, net libs etc. Even the worker threads can use up to 128MB by
default. But more than likely you are seeing the results of the procedure
cache (which is included in the memory pool). Especiallyif you are stress
testing with adhoc queries. SQL Server will use as much memory as is
available up until the limits if any are set. Even if your data only
amounts to 15MB you can have many thousands of cached plans in the procedure
cache and they will stay there until the OS asks for some of SQL Servers
memory. By default the OS and SS will dynamically share the available
memory but if the OS never asks for it SS will keep it. So if you never set
a limit SS expects to use all that it can and that is by design and usually
works best.
Andrew J. Kelly SQL MVP
"daniel.ferguson" <daniel.ferguson@.discussions.microsoft.com> wrote in
message news:8A46C920-A536-42BC-8E26-B71D0ABB9648@.microsoft.com...
>I have a database that starts out as 9Mb. Never grows to more than 15Mb.
> However, after running an endurance test for 5 days, the sqlservr process
> in
> TaskMan has grown to over 550Mb VM size.
> Can anyone help me understand how a 15 Mb database can consume 550Mb of VM
> space and never give it back? And/or how to encourage sqlservr to return
> memory that it shouldn't be using?
> Thanks in advance,
> Dan
> P.S. BTW I already know that min & max server memory configuration
> settings
> are really just "suggestions"!
>|||Andrew,
Thanks for the response. Your information agrees with what I have run
across searching the Net and the KB. If you don't mind fielding a couple
more questions, I'm curious about the mechanism that the OS uses to ask SQL
Server for the memory back. Is there a 'secret handshake' between the OS
and the service that's not disclosed to the public? I'm pretty familiar
with the Win32 SDK and the only things I know of is the WM_COMPACTING
message - but it's only sent to top-level windows - something that SQL
Server running as a service doesn't have.
Secondly, without having more detail information about my application, it
would be difficult to predict how many "cached plans" there actually are
would be, with about 18 to 20 queries with nearly identical predicates, why
wouldn't SQL Server "reuse" the cached plans? Isn't that what the "cache"
is for?
Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
64Mb max - after about 18 hrs, sqlservr.exe had grown to a little over 79 Mb
VM without any increase today. So far, performance of the overall
application hasn't shown any degradation. This is on a 512Mb box -getting
SQL Server to 'play well with others' is critical because our target
platform is 256Mb! (No choice and not open to discussion)
BTW: Is cascading deletes as resource intensive as it appears to be?
Thanks,
Dan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uIVG7ds8EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Daniel,
> The min and max settings are for the memory pool only and there is another
> section of memory called the MemToLeave area that is not included in those
> limits. This includes such things as the SQL Server exe, dll', extended
> procs, net libs etc. Even the worker threads can use up to 128MB by
> default. But more than likely you are seeing the results of the procedure
> cache (which is included in the memory pool). Especiallyif you are stress
> testing with adhoc queries. SQL Server will use as much memory as is
> available up until the limits if any are set. Even if your data only
> amounts to 15MB you can have many thousands of cached plans in the
> procedure cache and they will stay there until the OS asks for some of SQL
> Servers memory. By default the OS and SS will dynamically share the
> available memory but if the OS never asks for it SS will keep it. So if
> you never set a limit SS expects to use all that it can and that is by
> design and usually works best.
> --
> Andrew J. Kelly SQL MVP
>
> "daniel.ferguson" <daniel.ferguson@.discussions.microsoft.com> wrote in
> message news:8A46C920-A536-42BC-8E26-B71D0ABB9648@.microsoft.com...
>|||> and the service that's not disclosed to the public? I'm pretty familiar
> with the Win32 SDK and the only things I know of is the WM_COMPACTING
> message - but it's only sent to top-level windows - something that SQL
> Server running as a service doesn't have.
To be honest I knew exactlkly what it was at one time as one of the SQL
Server developers told me but don't recall what it is. Other than the
settings in SQL Server (and maybe some of the Win2003 management features)
you can't really do anything special otherwise.
> Secondly, without having more detail information about my application, it
> would be difficult to predict how many "cached plans" there actually are
> would be, with about 18 to 20 queries with nearly identical predicates,
> why wouldn't SQL Server "reuse" the cached plans? Isn't that what the
> "cache" is for?
Nearly identical is not good enough if the calls are not done properly. If
you use RPC calls and specify parameters properly the plans should be cached
and reused. Sending in a SQL string and executing it will result in a new
plan for each variation of the string including spaces. If you only have 20
different types of queries then syscacheobjects ideally should only have
about 100 rows or so in it at any particular time. What does select
COUNT(*) FROM Master..Syscacheobjects give you?
> Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
> 64Mb max - after about 18 hrs, sqlservr.exe had grown to a little over 79
> Mb
Well here is the official minimum requirements from BOL:
Enterprise Edition and Enterprise Evaluation Edition: 64 MB minimum, 128 MB
or more recommended
Standard Edition and Developer Edition: 64 MB minimum
Personal Edition and Desktop Engine (MSDE 2000):
a.. 128 MB minimum on Windows XP
b.. 64 MB minimum on Windows 2000
c.. 32 MB minimum on all other operating systems
Don't know what edition you plan to run and what you are doing with it but
good luck with PC's with 256MB's<g>.
> BTW: Is cascading deletes as resource intensive as it appears to be?
It all depends. You need proper indexes and RI to make it work properly but
it is simply a series of index seeks and deletes.
Andrew J. Kelly SQL MVP
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:e7XQNI28EHA.1524@.TK2MSFTNGP09.phx.gbl...|||> Secondly, without having more detail information about my application, it would be difficu
lt to
> predict how many "cached plans" there actually are would be, with about 18
to 20 queries with
> nearly identical predicates, why wouldn't SQL Server "reuse" the cached plans?[/vb
col]
The key word here is "nearly". There are several ways a query plan can be re
-used. Stored procedure
(best), explicit prepare and then execute, auto parameteration, and ad-hoc (
worst). My guess is that
you have ad-hoc for which the query text has to be *identical*. I suggest yo
u investigate using the
system table syscacheobjects in the master database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:e7XQNI28EHA.1524@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Andrew,
> Thanks for the response. Your information agrees with what I have run acr
oss searching the Net
> and the KB. If you don't mind fielding a couple more questions, I'm curio
us about the mechanism
> that the OS uses to ask SQL Server for the memory back. Is there a 'secre
t handshake' between the
> OS and the service that's not disclosed to the public? I'm pretty familia
r with the Win32 SDK and
> the only things I know of is the WM_COMPACTING message - but it's only sen
t to top-level windows -
> something that SQL Server running as a service doesn't have.
> Secondly, without having more detail information about my application, it
would be difficult to
> predict how many "cached plans" there actually are would be, with about 18
to 20 queries with
> nearly identical predicates, why wouldn't SQL Server "reuse" the cached pl
ans? Isn't that what
> the "cache" is for?
> Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
64Mb max - after about
> 18 hrs, sqlservr.exe had grown to a little over 79 Mb VM without any incre
ase today. So far,
> performance of the overall application hasn't shown any degradation. This
is on a 512Mb
> box -getting SQL Server to 'play well with others' is critical because our
target platform is
> 256Mb! (No choice and not open to discussion)
> BTW: Is cascading deletes as resource intensive as it appears to be?
> Thanks,
> Dan
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uIVG7ds8EHA.1408@.TK2MSFTNGP10.phx.gbl...
>|||Hi
No secret handshake. SQL Server only used publicly published API's (AFAIK,
SQL server calls malloc to allocate memory)
I can't find the API, but the OS sends a message requesting memory release
and then SQL Server tries to release it if possible (realloc or free).
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:e7XQNI28EHA.1524@.TK2MSFTNGP09.phx.gbl...
> Andrew,
> Thanks for the response. Your information agrees with what I have run
> across searching the Net and the KB. If you don't mind fielding a couple
> more questions, I'm curious about the mechanism that the OS uses to ask
SQL
> Server for the memory back. Is there a 'secret handshake' between the OS
> and the service that's not disclosed to the public? I'm pretty familiar
> with the Win32 SDK and the only things I know of is the WM_COMPACTING
> message - but it's only sent to top-level windows - something that SQL
> Server running as a service doesn't have.
> Secondly, without having more detail information about my application, it
> would be difficult to predict how many "cached plans" there actually are
> would be, with about 18 to 20 queries with nearly identical predicates,
why
> wouldn't SQL Server "reuse" the cached plans? Isn't that what the "cache"
> is for?
> Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
> 64Mb max - after about 18 hrs, sqlservr.exe had grown to a little over 79
Mb
> VM without any increase today. So far, performance of the overall
> application hasn't shown any degradation. This is on a 512Mb box -getting
> SQL Server to 'play well with others' is critical because our target
> platform is 256Mb! (No choice and not open to discussion)
> BTW: Is cascading deletes as resource intensive as it appears to be?
> Thanks,
> Dan
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uIVG7ds8EHA.1408@.TK2MSFTNGP10.phx.gbl...
another[vbcol=seagreen]
those[vbcol=seagreen]
procedure[vbcol=seagreen]
stress[vbcol=seagreen]
SQL[vbcol=seagreen]
15Mb.[vbcol=seagreen]
process[vbcol=seagreen]
return[vbcol=seagreen]
>
Memory Usage/Hog
I have a database that starts out as 9Mb. Never grows to more than 15Mb.
However, after running an endurance test for 5 days, the sqlservr process in
TaskMan has grown to over 550Mb VM size.
Can anyone help me understand how a 15 Mb database can consume 550Mb of VM
space and never give it back? And/or how to encourage sqlservr to return
memory that it shouldn't be using?
Thanks in advance,
Dan
P.S. BTW I already know that min & max server memory configuration settings
are really just "suggestions"!Daniel,
The min and max settings are for the memory pool only and there is another
section of memory called the MemToLeave area that is not included in those
limits. This includes such things as the SQL Server exe, dll', extended
procs, net libs etc. Even the worker threads can use up to 128MB by
default. But more than likely you are seeing the results of the procedure
cache (which is included in the memory pool). Especiallyif you are stress
testing with adhoc queries. SQL Server will use as much memory as is
available up until the limits if any are set. Even if your data only
amounts to 15MB you can have many thousands of cached plans in the procedure
cache and they will stay there until the OS asks for some of SQL Servers
memory. By default the OS and SS will dynamically share the available
memory but if the OS never asks for it SS will keep it. So if you never set
a limit SS expects to use all that it can and that is by design and usually
works best.
--
Andrew J. Kelly SQL MVP
"daniel.ferguson" <daniel.ferguson@.discussions.microsoft.com> wrote in
message news:8A46C920-A536-42BC-8E26-B71D0ABB9648@.microsoft.com...
>I have a database that starts out as 9Mb. Never grows to more than 15Mb.
> However, after running an endurance test for 5 days, the sqlservr process
> in
> TaskMan has grown to over 550Mb VM size.
> Can anyone help me understand how a 15 Mb database can consume 550Mb of VM
> space and never give it back? And/or how to encourage sqlservr to return
> memory that it shouldn't be using?
> Thanks in advance,
> Dan
> P.S. BTW I already know that min & max server memory configuration
> settings
> are really just "suggestions"!
>|||Andrew,
Thanks for the response. Your information agrees with what I have run
across searching the Net and the KB. If you don't mind fielding a couple
more questions, I'm curious about the mechanism that the OS uses to ask SQL
Server for the memory back. Is there a 'secret handshake' between the OS
and the service that's not disclosed to the public? I'm pretty familiar
with the Win32 SDK and the only things I know of is the WM_COMPACTING
message - but it's only sent to top-level windows - something that SQL
Server running as a service doesn't have.
Secondly, without having more detail information about my application, it
would be difficult to predict how many "cached plans" there actually are
would be, with about 18 to 20 queries with nearly identical predicates, why
wouldn't SQL Server "reuse" the cached plans? Isn't that what the "cache"
is for?
Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
64Mb max - after about 18 hrs, sqlservr.exe had grown to a little over 79 Mb
VM without any increase today. So far, performance of the overall
application hasn't shown any degradation. This is on a 512Mb box -getting
SQL Server to 'play well with others' is critical because our target
platform is 256Mb! (No choice and not open to discussion)
BTW: Is cascading deletes as resource intensive as it appears to be?
Thanks,
Dan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uIVG7ds8EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Daniel,
> The min and max settings are for the memory pool only and there is another
> section of memory called the MemToLeave area that is not included in those
> limits. This includes such things as the SQL Server exe, dll', extended
> procs, net libs etc. Even the worker threads can use up to 128MB by
> default. But more than likely you are seeing the results of the procedure
> cache (which is included in the memory pool). Especiallyif you are stress
> testing with adhoc queries. SQL Server will use as much memory as is
> available up until the limits if any are set. Even if your data only
> amounts to 15MB you can have many thousands of cached plans in the
> procedure cache and they will stay there until the OS asks for some of SQL
> Servers memory. By default the OS and SS will dynamically share the
> available memory but if the OS never asks for it SS will keep it. So if
> you never set a limit SS expects to use all that it can and that is by
> design and usually works best.
> --
> Andrew J. Kelly SQL MVP
>
> "daniel.ferguson" <daniel.ferguson@.discussions.microsoft.com> wrote in
> message news:8A46C920-A536-42BC-8E26-B71D0ABB9648@.microsoft.com...
>>I have a database that starts out as 9Mb. Never grows to more than 15Mb.
>> However, after running an endurance test for 5 days, the sqlservr process
>> in
>> TaskMan has grown to over 550Mb VM size.
>> Can anyone help me understand how a 15 Mb database can consume 550Mb of
>> VM
>> space and never give it back? And/or how to encourage sqlservr to return
>> memory that it shouldn't be using?
>> Thanks in advance,
>> Dan
>> P.S. BTW I already know that min & max server memory configuration
>> settings
>> are really just "suggestions"!
>|||> and the service that's not disclosed to the public? I'm pretty familiar
> with the Win32 SDK and the only things I know of is the WM_COMPACTING
> message - but it's only sent to top-level windows - something that SQL
> Server running as a service doesn't have.
To be honest I knew exactlkly what it was at one time as one of the SQL
Server developers told me but don't recall what it is. Other than the
settings in SQL Server (and maybe some of the Win2003 management features)
you can't really do anything special otherwise.
> Secondly, without having more detail information about my application, it
> would be difficult to predict how many "cached plans" there actually are
> would be, with about 18 to 20 queries with nearly identical predicates,
> why wouldn't SQL Server "reuse" the cached plans? Isn't that what the
> "cache" is for?
Nearly identical is not good enough if the calls are not done properly. If
you use RPC calls and specify parameters properly the plans should be cached
and reused. Sending in a SQL string and executing it will result in a new
plan for each variation of the string including spaces. If you only have 20
different types of queries then syscacheobjects ideally should only have
about 100 rows or so in it at any particular time. What does select
COUNT(*) FROM Master..Syscacheobjects give you?
> Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
> 64Mb max - after about 18 hrs, sqlservr.exe had grown to a little over 79
> Mb
Well here is the official minimum requirements from BOL:
Enterprise Edition and Enterprise Evaluation Edition: 64 MB minimum, 128 MB
or more recommended
Standard Edition and Developer Edition: 64 MB minimum
Personal Edition and Desktop Engine (MSDE 2000):
a.. 128 MB minimum on Windows XP
b.. 64 MB minimum on Windows 2000
c.. 32 MB minimum on all other operating systems
Don't know what edition you plan to run and what you are doing with it but
good luck with PC's with 256MB's<g>.
> BTW: Is cascading deletes as resource intensive as it appears to be?
It all depends. You need proper indexes and RI to make it work properly but
it is simply a series of index seeks and deletes.
--
Andrew J. Kelly SQL MVP
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:e7XQNI28EHA.1524@.TK2MSFTNGP09.phx.gbl...|||> Secondly, without having more detail information about my application, it would be difficult to
> predict how many "cached plans" there actually are would be, with about 18 to 20 queries with
> nearly identical predicates, why wouldn't SQL Server "reuse" the cached plans?
The key word here is "nearly". There are several ways a query plan can be re-used. Stored procedure
(best), explicit prepare and then execute, auto parameteration, and ad-hoc (worst). My guess is that
you have ad-hoc for which the query text has to be *identical*. I suggest you investigate using the
system table syscacheobjects in the master database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:e7XQNI28EHA.1524@.TK2MSFTNGP09.phx.gbl...
> Andrew,
> Thanks for the response. Your information agrees with what I have run across searching the Net
> and the KB. If you don't mind fielding a couple more questions, I'm curious about the mechanism
> that the OS uses to ask SQL Server for the memory back. Is there a 'secret handshake' between the
> OS and the service that's not disclosed to the public? I'm pretty familiar with the Win32 SDK and
> the only things I know of is the WM_COMPACTING message - but it's only sent to top-level windows -
> something that SQL Server running as a service doesn't have.
> Secondly, without having more detail information about my application, it would be difficult to
> predict how many "cached plans" there actually are would be, with about 18 to 20 queries with
> nearly identical predicates, why wouldn't SQL Server "reuse" the cached plans? Isn't that what
> the "cache" is for?
> Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and 64Mb max - after about
> 18 hrs, sqlservr.exe had grown to a little over 79 Mb VM without any increase today. So far,
> performance of the overall application hasn't shown any degradation. This is on a 512Mb
> box -getting SQL Server to 'play well with others' is critical because our target platform is
> 256Mb! (No choice and not open to discussion)
> BTW: Is cascading deletes as resource intensive as it appears to be?
> Thanks,
> Dan
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uIVG7ds8EHA.1408@.TK2MSFTNGP10.phx.gbl...
>> Daniel,
>> The min and max settings are for the memory pool only and there is another section of memory
>> called the MemToLeave area that is not included in those limits. This includes such things as
>> the SQL Server exe, dll', extended procs, net libs etc. Even the worker threads can use up to
>> 128MB by default. But more than likely you are seeing the results of the procedure cache (which
>> is included in the memory pool). Especiallyif you are stress testing with adhoc queries. SQL
>> Server will use as much memory as is available up until the limits if any are set. Even if your
>> data only amounts to 15MB you can have many thousands of cached plans in the procedure cache and
>> they will stay there until the OS asks for some of SQL Servers memory. By default the OS and SS
>> will dynamically share the available memory but if the OS never asks for it SS will keep it. So
>> if you never set a limit SS expects to use all that it can and that is by design and usually
>> works best.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "daniel.ferguson" <daniel.ferguson@.discussions.microsoft.com> wrote in message
>> news:8A46C920-A536-42BC-8E26-B71D0ABB9648@.microsoft.com...
>>I have a database that starts out as 9Mb. Never grows to more than 15Mb.
>> However, after running an endurance test for 5 days, the sqlservr process in
>> TaskMan has grown to over 550Mb VM size.
>> Can anyone help me understand how a 15 Mb database can consume 550Mb of VM
>> space and never give it back? And/or how to encourage sqlservr to return
>> memory that it shouldn't be using?
>> Thanks in advance,
>> Dan
>> P.S. BTW I already know that min & max server memory configuration settings
>> are really just "suggestions"!
>>
>|||Hi
No secret handshake. SQL Server only used publicly published API's (AFAIK,
SQL server calls malloc to allocate memory)
I can't find the API, but the OS sends a message requesting memory release
and then SQL Server tries to release it if possible (realloc or free).
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:e7XQNI28EHA.1524@.TK2MSFTNGP09.phx.gbl...
> Andrew,
> Thanks for the response. Your information agrees with what I have run
> across searching the Net and the KB. If you don't mind fielding a couple
> more questions, I'm curious about the mechanism that the OS uses to ask
SQL
> Server for the memory back. Is there a 'secret handshake' between the OS
> and the service that's not disclosed to the public? I'm pretty familiar
> with the Win32 SDK and the only things I know of is the WM_COMPACTING
> message - but it's only sent to top-level windows - something that SQL
> Server running as a service doesn't have.
> Secondly, without having more detail information about my application, it
> would be difficult to predict how many "cached plans" there actually are
> would be, with about 18 to 20 queries with nearly identical predicates,
why
> wouldn't SQL Server "reuse" the cached plans? Isn't that what the "cache"
> is for?
> Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
> 64Mb max - after about 18 hrs, sqlservr.exe had grown to a little over 79
Mb
> VM without any increase today. So far, performance of the overall
> application hasn't shown any degradation. This is on a 512Mb box -getting
> SQL Server to 'play well with others' is critical because our target
> platform is 256Mb! (No choice and not open to discussion)
> BTW: Is cascading deletes as resource intensive as it appears to be?
> Thanks,
> Dan
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uIVG7ds8EHA.1408@.TK2MSFTNGP10.phx.gbl...
> > Daniel,
> >
> > The min and max settings are for the memory pool only and there is
another
> > section of memory called the MemToLeave area that is not included in
those
> > limits. This includes such things as the SQL Server exe, dll', extended
> > procs, net libs etc. Even the worker threads can use up to 128MB by
> > default. But more than likely you are seeing the results of the
procedure
> > cache (which is included in the memory pool). Especiallyif you are
stress
> > testing with adhoc queries. SQL Server will use as much memory as is
> > available up until the limits if any are set. Even if your data only
> > amounts to 15MB you can have many thousands of cached plans in the
> > procedure cache and they will stay there until the OS asks for some of
SQL
> > Servers memory. By default the OS and SS will dynamically share the
> > available memory but if the OS never asks for it SS will keep it. So if
> > you never set a limit SS expects to use all that it can and that is by
> > design and usually works best.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "daniel.ferguson" <daniel.ferguson@.discussions.microsoft.com> wrote in
> > message news:8A46C920-A536-42BC-8E26-B71D0ABB9648@.microsoft.com...
> >>I have a database that starts out as 9Mb. Never grows to more than
15Mb.
> >> However, after running an endurance test for 5 days, the sqlservr
process
> >> in
> >> TaskMan has grown to over 550Mb VM size.
> >>
> >> Can anyone help me understand how a 15 Mb database can consume 550Mb of
> >> VM
> >> space and never give it back? And/or how to encourage sqlservr to
return
> >> memory that it shouldn't be using?
> >>
> >> Thanks in advance,
> >>
> >> Dan
> >>
> >> P.S. BTW I already know that min & max server memory configuration
> >> settings
> >> are really just "suggestions"!
> >>
> >
> >
>
However, after running an endurance test for 5 days, the sqlservr process in
TaskMan has grown to over 550Mb VM size.
Can anyone help me understand how a 15 Mb database can consume 550Mb of VM
space and never give it back? And/or how to encourage sqlservr to return
memory that it shouldn't be using?
Thanks in advance,
Dan
P.S. BTW I already know that min & max server memory configuration settings
are really just "suggestions"!Daniel,
The min and max settings are for the memory pool only and there is another
section of memory called the MemToLeave area that is not included in those
limits. This includes such things as the SQL Server exe, dll', extended
procs, net libs etc. Even the worker threads can use up to 128MB by
default. But more than likely you are seeing the results of the procedure
cache (which is included in the memory pool). Especiallyif you are stress
testing with adhoc queries. SQL Server will use as much memory as is
available up until the limits if any are set. Even if your data only
amounts to 15MB you can have many thousands of cached plans in the procedure
cache and they will stay there until the OS asks for some of SQL Servers
memory. By default the OS and SS will dynamically share the available
memory but if the OS never asks for it SS will keep it. So if you never set
a limit SS expects to use all that it can and that is by design and usually
works best.
--
Andrew J. Kelly SQL MVP
"daniel.ferguson" <daniel.ferguson@.discussions.microsoft.com> wrote in
message news:8A46C920-A536-42BC-8E26-B71D0ABB9648@.microsoft.com...
>I have a database that starts out as 9Mb. Never grows to more than 15Mb.
> However, after running an endurance test for 5 days, the sqlservr process
> in
> TaskMan has grown to over 550Mb VM size.
> Can anyone help me understand how a 15 Mb database can consume 550Mb of VM
> space and never give it back? And/or how to encourage sqlservr to return
> memory that it shouldn't be using?
> Thanks in advance,
> Dan
> P.S. BTW I already know that min & max server memory configuration
> settings
> are really just "suggestions"!
>|||Andrew,
Thanks for the response. Your information agrees with what I have run
across searching the Net and the KB. If you don't mind fielding a couple
more questions, I'm curious about the mechanism that the OS uses to ask SQL
Server for the memory back. Is there a 'secret handshake' between the OS
and the service that's not disclosed to the public? I'm pretty familiar
with the Win32 SDK and the only things I know of is the WM_COMPACTING
message - but it's only sent to top-level windows - something that SQL
Server running as a service doesn't have.
Secondly, without having more detail information about my application, it
would be difficult to predict how many "cached plans" there actually are
would be, with about 18 to 20 queries with nearly identical predicates, why
wouldn't SQL Server "reuse" the cached plans? Isn't that what the "cache"
is for?
Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
64Mb max - after about 18 hrs, sqlservr.exe had grown to a little over 79 Mb
VM without any increase today. So far, performance of the overall
application hasn't shown any degradation. This is on a 512Mb box -getting
SQL Server to 'play well with others' is critical because our target
platform is 256Mb! (No choice and not open to discussion)
BTW: Is cascading deletes as resource intensive as it appears to be?
Thanks,
Dan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uIVG7ds8EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Daniel,
> The min and max settings are for the memory pool only and there is another
> section of memory called the MemToLeave area that is not included in those
> limits. This includes such things as the SQL Server exe, dll', extended
> procs, net libs etc. Even the worker threads can use up to 128MB by
> default. But more than likely you are seeing the results of the procedure
> cache (which is included in the memory pool). Especiallyif you are stress
> testing with adhoc queries. SQL Server will use as much memory as is
> available up until the limits if any are set. Even if your data only
> amounts to 15MB you can have many thousands of cached plans in the
> procedure cache and they will stay there until the OS asks for some of SQL
> Servers memory. By default the OS and SS will dynamically share the
> available memory but if the OS never asks for it SS will keep it. So if
> you never set a limit SS expects to use all that it can and that is by
> design and usually works best.
> --
> Andrew J. Kelly SQL MVP
>
> "daniel.ferguson" <daniel.ferguson@.discussions.microsoft.com> wrote in
> message news:8A46C920-A536-42BC-8E26-B71D0ABB9648@.microsoft.com...
>>I have a database that starts out as 9Mb. Never grows to more than 15Mb.
>> However, after running an endurance test for 5 days, the sqlservr process
>> in
>> TaskMan has grown to over 550Mb VM size.
>> Can anyone help me understand how a 15 Mb database can consume 550Mb of
>> VM
>> space and never give it back? And/or how to encourage sqlservr to return
>> memory that it shouldn't be using?
>> Thanks in advance,
>> Dan
>> P.S. BTW I already know that min & max server memory configuration
>> settings
>> are really just "suggestions"!
>|||> and the service that's not disclosed to the public? I'm pretty familiar
> with the Win32 SDK and the only things I know of is the WM_COMPACTING
> message - but it's only sent to top-level windows - something that SQL
> Server running as a service doesn't have.
To be honest I knew exactlkly what it was at one time as one of the SQL
Server developers told me but don't recall what it is. Other than the
settings in SQL Server (and maybe some of the Win2003 management features)
you can't really do anything special otherwise.
> Secondly, without having more detail information about my application, it
> would be difficult to predict how many "cached plans" there actually are
> would be, with about 18 to 20 queries with nearly identical predicates,
> why wouldn't SQL Server "reuse" the cached plans? Isn't that what the
> "cache" is for?
Nearly identical is not good enough if the calls are not done properly. If
you use RPC calls and specify parameters properly the plans should be cached
and reused. Sending in a SQL string and executing it will result in a new
plan for each variation of the string including spaces. If you only have 20
different types of queries then syscacheobjects ideally should only have
about 100 rows or so in it at any particular time. What does select
COUNT(*) FROM Master..Syscacheobjects give you?
> Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
> 64Mb max - after about 18 hrs, sqlservr.exe had grown to a little over 79
> Mb
Well here is the official minimum requirements from BOL:
Enterprise Edition and Enterprise Evaluation Edition: 64 MB minimum, 128 MB
or more recommended
Standard Edition and Developer Edition: 64 MB minimum
Personal Edition and Desktop Engine (MSDE 2000):
a.. 128 MB minimum on Windows XP
b.. 64 MB minimum on Windows 2000
c.. 32 MB minimum on all other operating systems
Don't know what edition you plan to run and what you are doing with it but
good luck with PC's with 256MB's<g>.
> BTW: Is cascading deletes as resource intensive as it appears to be?
It all depends. You need proper indexes and RI to make it work properly but
it is simply a series of index seeks and deletes.
--
Andrew J. Kelly SQL MVP
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:e7XQNI28EHA.1524@.TK2MSFTNGP09.phx.gbl...|||> Secondly, without having more detail information about my application, it would be difficult to
> predict how many "cached plans" there actually are would be, with about 18 to 20 queries with
> nearly identical predicates, why wouldn't SQL Server "reuse" the cached plans?
The key word here is "nearly". There are several ways a query plan can be re-used. Stored procedure
(best), explicit prepare and then execute, auto parameteration, and ad-hoc (worst). My guess is that
you have ad-hoc for which the query text has to be *identical*. I suggest you investigate using the
system table syscacheobjects in the master database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:e7XQNI28EHA.1524@.TK2MSFTNGP09.phx.gbl...
> Andrew,
> Thanks for the response. Your information agrees with what I have run across searching the Net
> and the KB. If you don't mind fielding a couple more questions, I'm curious about the mechanism
> that the OS uses to ask SQL Server for the memory back. Is there a 'secret handshake' between the
> OS and the service that's not disclosed to the public? I'm pretty familiar with the Win32 SDK and
> the only things I know of is the WM_COMPACTING message - but it's only sent to top-level windows -
> something that SQL Server running as a service doesn't have.
> Secondly, without having more detail information about my application, it would be difficult to
> predict how many "cached plans" there actually are would be, with about 18 to 20 queries with
> nearly identical predicates, why wouldn't SQL Server "reuse" the cached plans? Isn't that what
> the "cache" is for?
> Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and 64Mb max - after about
> 18 hrs, sqlservr.exe had grown to a little over 79 Mb VM without any increase today. So far,
> performance of the overall application hasn't shown any degradation. This is on a 512Mb
> box -getting SQL Server to 'play well with others' is critical because our target platform is
> 256Mb! (No choice and not open to discussion)
> BTW: Is cascading deletes as resource intensive as it appears to be?
> Thanks,
> Dan
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uIVG7ds8EHA.1408@.TK2MSFTNGP10.phx.gbl...
>> Daniel,
>> The min and max settings are for the memory pool only and there is another section of memory
>> called the MemToLeave area that is not included in those limits. This includes such things as
>> the SQL Server exe, dll', extended procs, net libs etc. Even the worker threads can use up to
>> 128MB by default. But more than likely you are seeing the results of the procedure cache (which
>> is included in the memory pool). Especiallyif you are stress testing with adhoc queries. SQL
>> Server will use as much memory as is available up until the limits if any are set. Even if your
>> data only amounts to 15MB you can have many thousands of cached plans in the procedure cache and
>> they will stay there until the OS asks for some of SQL Servers memory. By default the OS and SS
>> will dynamically share the available memory but if the OS never asks for it SS will keep it. So
>> if you never set a limit SS expects to use all that it can and that is by design and usually
>> works best.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "daniel.ferguson" <daniel.ferguson@.discussions.microsoft.com> wrote in message
>> news:8A46C920-A536-42BC-8E26-B71D0ABB9648@.microsoft.com...
>>I have a database that starts out as 9Mb. Never grows to more than 15Mb.
>> However, after running an endurance test for 5 days, the sqlservr process in
>> TaskMan has grown to over 550Mb VM size.
>> Can anyone help me understand how a 15 Mb database can consume 550Mb of VM
>> space and never give it back? And/or how to encourage sqlservr to return
>> memory that it shouldn't be using?
>> Thanks in advance,
>> Dan
>> P.S. BTW I already know that min & max server memory configuration settings
>> are really just "suggestions"!
>>
>|||Hi
No secret handshake. SQL Server only used publicly published API's (AFAIK,
SQL server calls malloc to allocate memory)
I can't find the API, but the OS sends a message requesting memory release
and then SQL Server tries to release it if possible (realloc or free).
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:e7XQNI28EHA.1524@.TK2MSFTNGP09.phx.gbl...
> Andrew,
> Thanks for the response. Your information agrees with what I have run
> across searching the Net and the KB. If you don't mind fielding a couple
> more questions, I'm curious about the mechanism that the OS uses to ask
SQL
> Server for the memory back. Is there a 'secret handshake' between the OS
> and the service that's not disclosed to the public? I'm pretty familiar
> with the Win32 SDK and the only things I know of is the WM_COMPACTING
> message - but it's only sent to top-level windows - something that SQL
> Server running as a service doesn't have.
> Secondly, without having more detail information about my application, it
> would be difficult to predict how many "cached plans" there actually are
> would be, with about 18 to 20 queries with nearly identical predicates,
why
> wouldn't SQL Server "reuse" the cached plans? Isn't that what the "cache"
> is for?
> Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
> 64Mb max - after about 18 hrs, sqlservr.exe had grown to a little over 79
Mb
> VM without any increase today. So far, performance of the overall
> application hasn't shown any degradation. This is on a 512Mb box -getting
> SQL Server to 'play well with others' is critical because our target
> platform is 256Mb! (No choice and not open to discussion)
> BTW: Is cascading deletes as resource intensive as it appears to be?
> Thanks,
> Dan
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uIVG7ds8EHA.1408@.TK2MSFTNGP10.phx.gbl...
> > Daniel,
> >
> > The min and max settings are for the memory pool only and there is
another
> > section of memory called the MemToLeave area that is not included in
those
> > limits. This includes such things as the SQL Server exe, dll', extended
> > procs, net libs etc. Even the worker threads can use up to 128MB by
> > default. But more than likely you are seeing the results of the
procedure
> > cache (which is included in the memory pool). Especiallyif you are
stress
> > testing with adhoc queries. SQL Server will use as much memory as is
> > available up until the limits if any are set. Even if your data only
> > amounts to 15MB you can have many thousands of cached plans in the
> > procedure cache and they will stay there until the OS asks for some of
SQL
> > Servers memory. By default the OS and SS will dynamically share the
> > available memory but if the OS never asks for it SS will keep it. So if
> > you never set a limit SS expects to use all that it can and that is by
> > design and usually works best.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "daniel.ferguson" <daniel.ferguson@.discussions.microsoft.com> wrote in
> > message news:8A46C920-A536-42BC-8E26-B71D0ABB9648@.microsoft.com...
> >>I have a database that starts out as 9Mb. Never grows to more than
15Mb.
> >> However, after running an endurance test for 5 days, the sqlservr
process
> >> in
> >> TaskMan has grown to over 550Mb VM size.
> >>
> >> Can anyone help me understand how a 15 Mb database can consume 550Mb of
> >> VM
> >> space and never give it back? And/or how to encourage sqlservr to
return
> >> memory that it shouldn't be using?
> >>
> >> Thanks in advance,
> >>
> >> Dan
> >>
> >> P.S. BTW I already know that min & max server memory configuration
> >> settings
> >> are really just "suggestions"!
> >>
> >
> >
>
memory usage/allocation/paging question
Are there any *negative* consequences to SQL Server 2000 Standard
paging to disk more often if one reduces the amount of available RAM
from 2.0 GB to 1.5 GB to give the OS and other apps enough RAM, other
than things possibly being "slower" and whatever wear and tear could
happen to the disk drives??
paging to disk more often if one reduces the amount of available RAM
from 2.0 GB to 1.5 GB to give the OS and other apps enough RAM, other
than things possibly being "slower" and whatever wear and tear could
happen to the disk drives??
Or is it better to add in more RAM per se to bring the server up to 4
GB (Win2k3 Svr SP1)?? (it has a 4GB pagefile)
Is any information available about judging how much RAM the OS (Windows
2003 Server) should have available?? Does IIS require a lot of RAM??
Thank you, Tomtlyczko (tlyczko@.gmail.com) writes:
> Are there any *negative* consequences to SQL Server 2000 Standard
> paging to disk more often if one reduces the amount of available RAM
> from 2.0 GB to 1.5 GB to give the OS and other apps enough RAM, other
> than things possibly being "slower" and whatever wear and tear could
> happen to the disk drives??
>
> Or is it better to add in more RAM per se to bring the server up to 4
> GB (Win2k3 Svr SP1)?? (it has a 4GB pagefile)
>
> Is any information available about judging how much RAM the OS (Windows
> 2003 Server) should have available?? Does IIS require a lot of RAM??
SQL Server is designed to get as much memory it can get and with a
reason: the more data and execution plans SQL Server can hold in cache,
the better it will perform.
If other applications on the same box calls for more memory, SQL Server
will yield, but it may not yield fast enough, so if that is a concern
it may be an idea to constraint the amount of memory that SQL Server
can get.
Then again, this is mainly something would do on a developer machine,
where you don't want broswers, Visual Studio, Word or whatever to be
swapped out, because you run a query from hell. Constrainting the amount
of memory box, is like driving your BMW on the third gear only.
If you are running IIS on the same box, you should either move IIS
to another box (I have no idea how much memory IIS needs, but it's
probably more than 100 MB than less) or get more memory. SQL Server
2000 Standard Edition can only use 2 GB of memory, so if you add 4GB
and turn on the /3GB switch, there will be some breathing room for
IIS.
I should add that the database size matters a bit. If your database
is small, say 1GB, it will fit entirely in cache, and you may need
all 2GB for memory.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Memory Usage, SBS 2000 & SQL 2000
Hello,
I am noticing that memory on our SQL 2000 (on an SBS 2000 box, a dual
processor box with 2GB of RAM) continues to grow over a few day period. I
have double-checked to make sure that SP3a is installed and am trying to
double-check to make sure that MDAC 2.71 is installed (correctly). First,
what's the best way to assure that each of these are installed without
errors or issues? Second, if those are then, what else could be causing the
memory to continue to grow over time?
I know that I can limit the SQL configuration to 1GB (for example) but
that's not really the key problem here. As I write this the memory has
grown from 287MB to 314MB and by the weekend will likely be over 1GB thus
forcing the box to be using 2.2 - 2.8 GB of RAM when it only has 2GB's.
Adding RAM is obviously an option but that looks like it's only going to be
a band-aid to the problem because it will just continue to eat the
additional memory unless I lock the SQL RAM usage however I feel the key
issue to determine why the RAM is continuing to grow day-by-day.
Thanks in advance!
ChrisI expect that the behavior you are seeing is normal. There is no cause for
alarm.
SQL Server 2000 Standard Edition is able to use up to 2GB of RAM. It will
not use more than that. SQL Server stores data and query plans in RAM. As
you use SQL Server it caches data and query plans to memory.
If SQL Server is the only thing running on the box you might want to leave
it as is. If you have other server apps running and consuming resources you
might want to limit memory usage to something that allows your other apps to
have enough memory. The other option, of course is to leave SQL Server
as-is and add additional memory for the other apps that you may have on this
server.
By the way, how big are your databases? If all of your user databases are
"small" (they all add up to 1GB or LESS) you should not see SQL Server
consume much over 1GB.
Bottom line: don't worry unless your server is RAM hungry. If it is RAM
hungry add some or limit SQL Server to "enough" memory.
--
Keith
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:OOfxH9alEHA.3968@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> I am noticing that memory on our SQL 2000 (on an SBS 2000 box, a dual
> processor box with 2GB of RAM) continues to grow over a few day period. I
> have double-checked to make sure that SP3a is installed and am trying to
> double-check to make sure that MDAC 2.71 is installed (correctly). First,
> what's the best way to assure that each of these are installed without
> errors or issues? Second, if those are then, what else could be causing
the
> memory to continue to grow over time?
>
> I know that I can limit the SQL configuration to 1GB (for example) but
> that's not really the key problem here. As I write this the memory has
> grown from 287MB to 314MB and by the weekend will likely be over 1GB thus
> forcing the box to be using 2.2 - 2.8 GB of RAM when it only has 2GB's.
> Adding RAM is obviously an option but that looks like it's only going to
be
> a band-aid to the problem because it will just continue to eat the
> additional memory unless I lock the SQL RAM usage however I feel the key
> issue to determine why the RAM is continuing to grow day-by-day.
>
> Thanks in advance!
>
> Chris
>|||Thank you...
The database(s) right now are just about a 1/2GB and I've noticed the SQL
box hitting about 1GB of RAM. I guess I will limit it for the moment so
that other apps don't eatup too much or go past the physical limit.
Chris
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:e1VmGYblEHA.2968@.TK2MSFTNGP14.phx.gbl...
>I expect that the behavior you are seeing is normal. There is no cause for
> alarm.
> SQL Server 2000 Standard Edition is able to use up to 2GB of RAM. It will
> not use more than that. SQL Server stores data and query plans in RAM.
> As
> you use SQL Server it caches data and query plans to memory.
> If SQL Server is the only thing running on the box you might want to leave
> it as is. If you have other server apps running and consuming resources
> you
> might want to limit memory usage to something that allows your other apps
> to
> have enough memory. The other option, of course is to leave SQL Server
> as-is and add additional memory for the other apps that you may have on
> this
> server.
> By the way, how big are your databases? If all of your user databases are
> "small" (they all add up to 1GB or LESS) you should not see SQL Server
> consume much over 1GB.
> Bottom line: don't worry unless your server is RAM hungry. If it is RAM
> hungry add some or limit SQL Server to "enough" memory.
> --
> Keith
>
> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
> news:OOfxH9alEHA.3968@.TK2MSFTNGP11.phx.gbl...
>> Hello,
>>
>> I am noticing that memory on our SQL 2000 (on an SBS 2000 box, a dual
>> processor box with 2GB of RAM) continues to grow over a few day period.
>> I
>> have double-checked to make sure that SP3a is installed and am trying to
>> double-check to make sure that MDAC 2.71 is installed (correctly).
>> First,
>> what's the best way to assure that each of these are installed without
>> errors or issues? Second, if those are then, what else could be causing
> the
>> memory to continue to grow over time?
>>
>> I know that I can limit the SQL configuration to 1GB (for example) but
>> that's not really the key problem here. As I write this the memory has
>> grown from 287MB to 314MB and by the weekend will likely be over 1GB thus
>> forcing the box to be using 2.2 - 2.8 GB of RAM when it only has 2GB's.
>> Adding RAM is obviously an option but that looks like it's only going to
> be
>> a band-aid to the problem because it will just continue to eat the
>> additional memory unless I lock the SQL RAM usage however I feel the key
>> issue to determine why the RAM is continuing to grow day-by-day.
>>
>> Thanks in advance!
>>
>> Chris
>>
>
I am noticing that memory on our SQL 2000 (on an SBS 2000 box, a dual
processor box with 2GB of RAM) continues to grow over a few day period. I
have double-checked to make sure that SP3a is installed and am trying to
double-check to make sure that MDAC 2.71 is installed (correctly). First,
what's the best way to assure that each of these are installed without
errors or issues? Second, if those are then, what else could be causing the
memory to continue to grow over time?
I know that I can limit the SQL configuration to 1GB (for example) but
that's not really the key problem here. As I write this the memory has
grown from 287MB to 314MB and by the weekend will likely be over 1GB thus
forcing the box to be using 2.2 - 2.8 GB of RAM when it only has 2GB's.
Adding RAM is obviously an option but that looks like it's only going to be
a band-aid to the problem because it will just continue to eat the
additional memory unless I lock the SQL RAM usage however I feel the key
issue to determine why the RAM is continuing to grow day-by-day.
Thanks in advance!
ChrisI expect that the behavior you are seeing is normal. There is no cause for
alarm.
SQL Server 2000 Standard Edition is able to use up to 2GB of RAM. It will
not use more than that. SQL Server stores data and query plans in RAM. As
you use SQL Server it caches data and query plans to memory.
If SQL Server is the only thing running on the box you might want to leave
it as is. If you have other server apps running and consuming resources you
might want to limit memory usage to something that allows your other apps to
have enough memory. The other option, of course is to leave SQL Server
as-is and add additional memory for the other apps that you may have on this
server.
By the way, how big are your databases? If all of your user databases are
"small" (they all add up to 1GB or LESS) you should not see SQL Server
consume much over 1GB.
Bottom line: don't worry unless your server is RAM hungry. If it is RAM
hungry add some or limit SQL Server to "enough" memory.
--
Keith
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:OOfxH9alEHA.3968@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> I am noticing that memory on our SQL 2000 (on an SBS 2000 box, a dual
> processor box with 2GB of RAM) continues to grow over a few day period. I
> have double-checked to make sure that SP3a is installed and am trying to
> double-check to make sure that MDAC 2.71 is installed (correctly). First,
> what's the best way to assure that each of these are installed without
> errors or issues? Second, if those are then, what else could be causing
the
> memory to continue to grow over time?
>
> I know that I can limit the SQL configuration to 1GB (for example) but
> that's not really the key problem here. As I write this the memory has
> grown from 287MB to 314MB and by the weekend will likely be over 1GB thus
> forcing the box to be using 2.2 - 2.8 GB of RAM when it only has 2GB's.
> Adding RAM is obviously an option but that looks like it's only going to
be
> a band-aid to the problem because it will just continue to eat the
> additional memory unless I lock the SQL RAM usage however I feel the key
> issue to determine why the RAM is continuing to grow day-by-day.
>
> Thanks in advance!
>
> Chris
>|||Thank you...
The database(s) right now are just about a 1/2GB and I've noticed the SQL
box hitting about 1GB of RAM. I guess I will limit it for the moment so
that other apps don't eatup too much or go past the physical limit.
Chris
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:e1VmGYblEHA.2968@.TK2MSFTNGP14.phx.gbl...
>I expect that the behavior you are seeing is normal. There is no cause for
> alarm.
> SQL Server 2000 Standard Edition is able to use up to 2GB of RAM. It will
> not use more than that. SQL Server stores data and query plans in RAM.
> As
> you use SQL Server it caches data and query plans to memory.
> If SQL Server is the only thing running on the box you might want to leave
> it as is. If you have other server apps running and consuming resources
> you
> might want to limit memory usage to something that allows your other apps
> to
> have enough memory. The other option, of course is to leave SQL Server
> as-is and add additional memory for the other apps that you may have on
> this
> server.
> By the way, how big are your databases? If all of your user databases are
> "small" (they all add up to 1GB or LESS) you should not see SQL Server
> consume much over 1GB.
> Bottom line: don't worry unless your server is RAM hungry. If it is RAM
> hungry add some or limit SQL Server to "enough" memory.
> --
> Keith
>
> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
> news:OOfxH9alEHA.3968@.TK2MSFTNGP11.phx.gbl...
>> Hello,
>>
>> I am noticing that memory on our SQL 2000 (on an SBS 2000 box, a dual
>> processor box with 2GB of RAM) continues to grow over a few day period.
>> I
>> have double-checked to make sure that SP3a is installed and am trying to
>> double-check to make sure that MDAC 2.71 is installed (correctly).
>> First,
>> what's the best way to assure that each of these are installed without
>> errors or issues? Second, if those are then, what else could be causing
> the
>> memory to continue to grow over time?
>>
>> I know that I can limit the SQL configuration to 1GB (for example) but
>> that's not really the key problem here. As I write this the memory has
>> grown from 287MB to 314MB and by the weekend will likely be over 1GB thus
>> forcing the box to be using 2.2 - 2.8 GB of RAM when it only has 2GB's.
>> Adding RAM is obviously an option but that looks like it's only going to
> be
>> a band-aid to the problem because it will just continue to eat the
>> additional memory unless I lock the SQL RAM usage however I feel the key
>> issue to determine why the RAM is continuing to grow day-by-day.
>>
>> Thanks in advance!
>>
>> Chris
>>
>
Memory Usage, SBS 2000 & SQL 2000
Hello,
I am noticing that memory on our SQL 2000 (on an SBS 2000 box, a dual
processor box with 2GB of RAM) continues to grow over a few day period. I
have double-checked to make sure that SP3a is installed and am trying to
double-check to make sure that MDAC 2.71 is installed (correctly). First,
what's the best way to assure that each of these are installed without
errors or issues? Second, if those are then, what else could be causing the
memory to continue to grow over time?
I know that I can limit the SQL configuration to 1GB (for example) but
that's not really the key problem here. As I write this the memory has
grown from 287MB to 314MB and by the weekend will likely be over 1GB thus
forcing the box to be using 2.2 - 2.8 GB of RAM when it only has 2GB's.
Adding RAM is obviously an option but that looks like it's only going to be
a band-aid to the problem because it will just continue to eat the
additional memory unless I lock the SQL RAM usage however I feel the key
issue to determine why the RAM is continuing to grow day-by-day.
Thanks in advance!
Chris
I expect that the behavior you are seeing is normal. There is no cause for
alarm.
SQL Server 2000 Standard Edition is able to use up to 2GB of RAM. It will
not use more than that. SQL Server stores data and query plans in RAM. As
you use SQL Server it caches data and query plans to memory.
If SQL Server is the only thing running on the box you might want to leave
it as is. If you have other server apps running and consuming resources you
might want to limit memory usage to something that allows your other apps to
have enough memory. The other option, of course is to leave SQL Server
as-is and add additional memory for the other apps that you may have on this
server.
By the way, how big are your databases? If all of your user databases are
"small" (they all add up to 1GB or LESS) you should not see SQL Server
consume much over 1GB.
Bottom line: don't worry unless your server is RAM hungry. If it is RAM
hungry add some or limit SQL Server to "enough" memory.
Keith
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:OOfxH9alEHA.3968@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> I am noticing that memory on our SQL 2000 (on an SBS 2000 box, a dual
> processor box with 2GB of RAM) continues to grow over a few day period. I
> have double-checked to make sure that SP3a is installed and am trying to
> double-check to make sure that MDAC 2.71 is installed (correctly). First,
> what's the best way to assure that each of these are installed without
> errors or issues? Second, if those are then, what else could be causing
the
> memory to continue to grow over time?
>
> I know that I can limit the SQL configuration to 1GB (for example) but
> that's not really the key problem here. As I write this the memory has
> grown from 287MB to 314MB and by the weekend will likely be over 1GB thus
> forcing the box to be using 2.2 - 2.8 GB of RAM when it only has 2GB's.
> Adding RAM is obviously an option but that looks like it's only going to
be
> a band-aid to the problem because it will just continue to eat the
> additional memory unless I lock the SQL RAM usage however I feel the key
> issue to determine why the RAM is continuing to grow day-by-day.
>
> Thanks in advance!
>
> Chris
>
|||Thank you...
The database(s) right now are just about a 1/2GB and I've noticed the SQL
box hitting about 1GB of RAM. I guess I will limit it for the moment so
that other apps don't eatup too much or go past the physical limit.
Chris
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:e1VmGYblEHA.2968@.TK2MSFTNGP14.phx.gbl...
>I expect that the behavior you are seeing is normal. There is no cause for
> alarm.
> SQL Server 2000 Standard Edition is able to use up to 2GB of RAM. It will
> not use more than that. SQL Server stores data and query plans in RAM.
> As
> you use SQL Server it caches data and query plans to memory.
> If SQL Server is the only thing running on the box you might want to leave
> it as is. If you have other server apps running and consuming resources
> you
> might want to limit memory usage to something that allows your other apps
> to
> have enough memory. The other option, of course is to leave SQL Server
> as-is and add additional memory for the other apps that you may have on
> this
> server.
> By the way, how big are your databases? If all of your user databases are
> "small" (they all add up to 1GB or LESS) you should not see SQL Server
> consume much over 1GB.
> Bottom line: don't worry unless your server is RAM hungry. If it is RAM
> hungry add some or limit SQL Server to "enough" memory.
> --
> Keith
>
> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
> news:OOfxH9alEHA.3968@.TK2MSFTNGP11.phx.gbl...
> the
> be
>
I am noticing that memory on our SQL 2000 (on an SBS 2000 box, a dual
processor box with 2GB of RAM) continues to grow over a few day period. I
have double-checked to make sure that SP3a is installed and am trying to
double-check to make sure that MDAC 2.71 is installed (correctly). First,
what's the best way to assure that each of these are installed without
errors or issues? Second, if those are then, what else could be causing the
memory to continue to grow over time?
I know that I can limit the SQL configuration to 1GB (for example) but
that's not really the key problem here. As I write this the memory has
grown from 287MB to 314MB and by the weekend will likely be over 1GB thus
forcing the box to be using 2.2 - 2.8 GB of RAM when it only has 2GB's.
Adding RAM is obviously an option but that looks like it's only going to be
a band-aid to the problem because it will just continue to eat the
additional memory unless I lock the SQL RAM usage however I feel the key
issue to determine why the RAM is continuing to grow day-by-day.
Thanks in advance!
Chris
I expect that the behavior you are seeing is normal. There is no cause for
alarm.
SQL Server 2000 Standard Edition is able to use up to 2GB of RAM. It will
not use more than that. SQL Server stores data and query plans in RAM. As
you use SQL Server it caches data and query plans to memory.
If SQL Server is the only thing running on the box you might want to leave
it as is. If you have other server apps running and consuming resources you
might want to limit memory usage to something that allows your other apps to
have enough memory. The other option, of course is to leave SQL Server
as-is and add additional memory for the other apps that you may have on this
server.
By the way, how big are your databases? If all of your user databases are
"small" (they all add up to 1GB or LESS) you should not see SQL Server
consume much over 1GB.
Bottom line: don't worry unless your server is RAM hungry. If it is RAM
hungry add some or limit SQL Server to "enough" memory.
Keith
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:OOfxH9alEHA.3968@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> I am noticing that memory on our SQL 2000 (on an SBS 2000 box, a dual
> processor box with 2GB of RAM) continues to grow over a few day period. I
> have double-checked to make sure that SP3a is installed and am trying to
> double-check to make sure that MDAC 2.71 is installed (correctly). First,
> what's the best way to assure that each of these are installed without
> errors or issues? Second, if those are then, what else could be causing
the
> memory to continue to grow over time?
>
> I know that I can limit the SQL configuration to 1GB (for example) but
> that's not really the key problem here. As I write this the memory has
> grown from 287MB to 314MB and by the weekend will likely be over 1GB thus
> forcing the box to be using 2.2 - 2.8 GB of RAM when it only has 2GB's.
> Adding RAM is obviously an option but that looks like it's only going to
be
> a band-aid to the problem because it will just continue to eat the
> additional memory unless I lock the SQL RAM usage however I feel the key
> issue to determine why the RAM is continuing to grow day-by-day.
>
> Thanks in advance!
>
> Chris
>
|||Thank you...
The database(s) right now are just about a 1/2GB and I've noticed the SQL
box hitting about 1GB of RAM. I guess I will limit it for the moment so
that other apps don't eatup too much or go past the physical limit.
Chris
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:e1VmGYblEHA.2968@.TK2MSFTNGP14.phx.gbl...
>I expect that the behavior you are seeing is normal. There is no cause for
> alarm.
> SQL Server 2000 Standard Edition is able to use up to 2GB of RAM. It will
> not use more than that. SQL Server stores data and query plans in RAM.
> As
> you use SQL Server it caches data and query plans to memory.
> If SQL Server is the only thing running on the box you might want to leave
> it as is. If you have other server apps running and consuming resources
> you
> might want to limit memory usage to something that allows your other apps
> to
> have enough memory. The other option, of course is to leave SQL Server
> as-is and add additional memory for the other apps that you may have on
> this
> server.
> By the way, how big are your databases? If all of your user databases are
> "small" (they all add up to 1GB or LESS) you should not see SQL Server
> consume much over 1GB.
> Bottom line: don't worry unless your server is RAM hungry. If it is RAM
> hungry add some or limit SQL Server to "enough" memory.
> --
> Keith
>
> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
> news:OOfxH9alEHA.3968@.TK2MSFTNGP11.phx.gbl...
> the
> be
>
Memory usage SQL SERVER ENTERPRISE EDITION
Hi,
I have SQL SERVER enterprise edition on MIcrosoft windows 2003
enterprise.and we have about 4GB of RAM on it...also I have enabled
AWE...still SQL Server not using more then 1.86GB...
Do i have to use anything for SQL server to use more memory or SQL server
will use what it needs
Any suggestion appreciated
- NT
Did you modify the boot.ini to include /3GB and /PAE?
Keith
"NT" <NT@.discussions.microsoft.com> wrote in message
news:4ABC910A-7853-4BC4-B751-A66C9FA8C9E7@.microsoft.com...
> Hi,
> I have SQL SERVER enterprise edition on MIcrosoft windows 2003
> enterprise.and we have about 4GB of RAM on it...also I have enabled
> AWE...still SQL Server not using more then 1.86GB...
> Do i have to use anything for SQL server to use more memory or SQL server
> will use what it needs
> Any suggestion appreciated
> --
> - NT
|||Enabling AWE with 4GB of ram is pretty much useless. You can set the /3GB
switch in the boot.ini file to allow SQL Server to use 3GB but it is best to
leave the rest for the OS and other apps that will need to run on the
server.
Andrew J. Kelly SQL MVP
"NT" <NT@.discussions.microsoft.com> wrote in message
news:4ABC910A-7853-4BC4-B751-A66C9FA8C9E7@.microsoft.com...
> Hi,
> I have SQL SERVER enterprise edition on MIcrosoft windows 2003
> enterprise.and we have about 4GB of RAM on it...also I have enabled
> AWE...still SQL Server not using more then 1.86GB...
> Do i have to use anything for SQL server to use more memory or SQL server
> will use what it needs
> Any suggestion appreciated
> --
> - NT
|||Thanks for the ...ACTUALLY WE HAVE 8GB RAM...
We have put /3GB /PAE in our boot.ini...we will reboot server tonight...
Does this will help SQL server to use more then 2GB? correct?
Thanks
"Andrew J. Kelly" wrote:
> Enabling AWE with 4GB of ram is pretty much useless. You can set the /3GB
> switch in the boot.ini file to allow SQL Server to use 3GB but it is best to
> leave the rest for the OS and other apps that will need to run on the
> server.
> --
> Andrew J. Kelly SQL MVP
>
> "NT" <NT@.discussions.microsoft.com> wrote in message
> news:4ABC910A-7853-4BC4-B751-A66C9FA8C9E7@.microsoft.com...
>
>
|||Flags for memory usage
System memory Flag to add to boot.ini file
4GB /3GB
8GB /3GB /PAE
16GB /3GB /PAE
16-64GB /PAE
/3GB limitS OS memory to 1GB and is optional.
When max memory >16GB, OS needs more than 1GB since it needs to manage AWE,
therefore /3GB can not be used there.
/PAE enables the use of memory more than 3GB.
In such situation, AWE also needs to be enabled by
run the following in QA:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 6144 --(6144 the number of memory for SQL
Server in MB)
RECONFIGURE
GO
"NT" <NT@.discussions.microsoft.com> wrote in message
news:CD4C56E5-8558-4979-AB24-1477CB7D5A6E@.microsoft.com...[vbcol=seagreen]
> Thanks for the ...ACTUALLY WE HAVE 8GB RAM...
> We have put /3GB /PAE in our boot.ini...we will reboot server tonight...
> Does this will help SQL server to use more then 2GB? correct?
> Thanks
> "Andrew J. Kelly" wrote:
/3GB[vbcol=seagreen]
best to[vbcol=seagreen]
server[vbcol=seagreen]
|||THe /3GB lets SQL Server (or any application) up to 3GB of directly
addressable memory and gives the OS 1GB. It does not allow you to address
more than 3GB that is what AWE and PAE are for. Just remember that if you
do turn on AWE you set your max memory to an amount less than the max since
AWE is not dynamic and will not play well with the OS if you grab all the
memory.
Andrew J. Kelly SQL MVP
"NT" <NT@.discussions.microsoft.com> wrote in message
news:CD4C56E5-8558-4979-AB24-1477CB7D5A6E@.microsoft.com...[vbcol=seagreen]
> Thanks for the ...ACTUALLY WE HAVE 8GB RAM...
> We have put /3GB /PAE in our boot.ini...we will reboot server tonight...
> Does this will help SQL server to use more then 2GB? correct?
> Thanks
> "Andrew J. Kelly" wrote:
/3GB[vbcol=seagreen]
best to[vbcol=seagreen]
server[vbcol=seagreen]
I have SQL SERVER enterprise edition on MIcrosoft windows 2003
enterprise.and we have about 4GB of RAM on it...also I have enabled
AWE...still SQL Server not using more then 1.86GB...
Do i have to use anything for SQL server to use more memory or SQL server
will use what it needs
Any suggestion appreciated
- NT
Did you modify the boot.ini to include /3GB and /PAE?
Keith
"NT" <NT@.discussions.microsoft.com> wrote in message
news:4ABC910A-7853-4BC4-B751-A66C9FA8C9E7@.microsoft.com...
> Hi,
> I have SQL SERVER enterprise edition on MIcrosoft windows 2003
> enterprise.and we have about 4GB of RAM on it...also I have enabled
> AWE...still SQL Server not using more then 1.86GB...
> Do i have to use anything for SQL server to use more memory or SQL server
> will use what it needs
> Any suggestion appreciated
> --
> - NT
|||Enabling AWE with 4GB of ram is pretty much useless. You can set the /3GB
switch in the boot.ini file to allow SQL Server to use 3GB but it is best to
leave the rest for the OS and other apps that will need to run on the
server.
Andrew J. Kelly SQL MVP
"NT" <NT@.discussions.microsoft.com> wrote in message
news:4ABC910A-7853-4BC4-B751-A66C9FA8C9E7@.microsoft.com...
> Hi,
> I have SQL SERVER enterprise edition on MIcrosoft windows 2003
> enterprise.and we have about 4GB of RAM on it...also I have enabled
> AWE...still SQL Server not using more then 1.86GB...
> Do i have to use anything for SQL server to use more memory or SQL server
> will use what it needs
> Any suggestion appreciated
> --
> - NT
|||Thanks for the ...ACTUALLY WE HAVE 8GB RAM...
We have put /3GB /PAE in our boot.ini...we will reboot server tonight...
Does this will help SQL server to use more then 2GB? correct?
Thanks
"Andrew J. Kelly" wrote:
> Enabling AWE with 4GB of ram is pretty much useless. You can set the /3GB
> switch in the boot.ini file to allow SQL Server to use 3GB but it is best to
> leave the rest for the OS and other apps that will need to run on the
> server.
> --
> Andrew J. Kelly SQL MVP
>
> "NT" <NT@.discussions.microsoft.com> wrote in message
> news:4ABC910A-7853-4BC4-B751-A66C9FA8C9E7@.microsoft.com...
>
>
|||Flags for memory usage
System memory Flag to add to boot.ini file
4GB /3GB
8GB /3GB /PAE
16GB /3GB /PAE
16-64GB /PAE
/3GB limitS OS memory to 1GB and is optional.
When max memory >16GB, OS needs more than 1GB since it needs to manage AWE,
therefore /3GB can not be used there.
/PAE enables the use of memory more than 3GB.
In such situation, AWE also needs to be enabled by
run the following in QA:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 6144 --(6144 the number of memory for SQL
Server in MB)
RECONFIGURE
GO
"NT" <NT@.discussions.microsoft.com> wrote in message
news:CD4C56E5-8558-4979-AB24-1477CB7D5A6E@.microsoft.com...[vbcol=seagreen]
> Thanks for the ...ACTUALLY WE HAVE 8GB RAM...
> We have put /3GB /PAE in our boot.ini...we will reboot server tonight...
> Does this will help SQL server to use more then 2GB? correct?
> Thanks
> "Andrew J. Kelly" wrote:
/3GB[vbcol=seagreen]
best to[vbcol=seagreen]
server[vbcol=seagreen]
|||THe /3GB lets SQL Server (or any application) up to 3GB of directly
addressable memory and gives the OS 1GB. It does not allow you to address
more than 3GB that is what AWE and PAE are for. Just remember that if you
do turn on AWE you set your max memory to an amount less than the max since
AWE is not dynamic and will not play well with the OS if you grab all the
memory.
Andrew J. Kelly SQL MVP
"NT" <NT@.discussions.microsoft.com> wrote in message
news:CD4C56E5-8558-4979-AB24-1477CB7D5A6E@.microsoft.com...[vbcol=seagreen]
> Thanks for the ...ACTUALLY WE HAVE 8GB RAM...
> We have put /3GB /PAE in our boot.ini...we will reboot server tonight...
> Does this will help SQL server to use more then 2GB? correct?
> Thanks
> "Andrew J. Kelly" wrote:
/3GB[vbcol=seagreen]
best to[vbcol=seagreen]
server[vbcol=seagreen]
Memory usage SQL Server 2000
Hi,
We have a scenario wherein RAM usage is creeping up until the DB has to be restarted (SQL server 2000 no service packs). There are 15 concurrent users connecting via ODBC (TCP/IP).
Please can anyone give any tips as to how we can track down the problem?
Thanks.We are also having the same problem on a couple of our MSSQL Server 2000 machines. All help in this would be greatly appreciated.
Sam|||You need to have a server with the following configuration:
It must include Backup Drive PV110T DLT1 40/80GB INTERNAL
and also the server preferrably must contain the following:
PowerEdge 6400 PIII Xeon 700MHz/2MB
2 x Terminator Card PE64x0
Add. CPU PIII Xeon 700MHz/2MB
2GB (4*512) 133MHz SDRAM
48x IDE CD-ROM
PERC3/DC 128MB RAID, U160 SCSI
4 x 36GB SCSI HDD, 15K RPM, 80-pin
Embedded Intel Pro 100+ PCI Ethernet NIC
and other ordinary devices.
You must have a very excellent RAM Capacity as mentioned above to avoid the slow performance of your service and to allow all users maintain their own work without any complaints.
Wish you good luck.
We have a scenario wherein RAM usage is creeping up until the DB has to be restarted (SQL server 2000 no service packs). There are 15 concurrent users connecting via ODBC (TCP/IP).
Please can anyone give any tips as to how we can track down the problem?
Thanks.We are also having the same problem on a couple of our MSSQL Server 2000 machines. All help in this would be greatly appreciated.
Sam|||You need to have a server with the following configuration:
It must include Backup Drive PV110T DLT1 40/80GB INTERNAL
and also the server preferrably must contain the following:
PowerEdge 6400 PIII Xeon 700MHz/2MB
2 x Terminator Card PE64x0
Add. CPU PIII Xeon 700MHz/2MB
2GB (4*512) 133MHz SDRAM
48x IDE CD-ROM
PERC3/DC 128MB RAID, U160 SCSI
4 x 36GB SCSI HDD, 15K RPM, 80-pin
Embedded Intel Pro 100+ PCI Ethernet NIC
and other ordinary devices.
You must have a very excellent RAM Capacity as mentioned above to avoid the slow performance of your service and to allow all users maintain their own work without any complaints.
Wish you good luck.
Memory Usage Problem on 64 Bit SQL Server 2005 ent Cluster
My SQL Server Ent 2005 64Bit Active / Passive Cluster is using 16.3GB of RAM
and there is only 16GB in the server.
I have the max memory for SQL server set at 12582912 (12GB) but the process
is running at 15,388,052 ignoring the maximum settings.
Thus the server is swapping memory to disk.
How can i make sure the SQL server is limited in its memory usgage if it is
ignoring the setting.
Regards
TobyDid you change the setting after the server was already running? Decreasing
memory won't necessarily release reserved mem without a reboot.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"TobiR" <TobiR@.discussions.microsoft.com> wrote in message
news:147CE1A0-1D90-431F-960A-E44BF36B695F@.microsoft.com...
> My SQL Server Ent 2005 64Bit Active / Passive Cluster is using 16.3GB of
> RAM
> and there is only 16GB in the server.
> I have the max memory for SQL server set at 12582912 (12GB) but the
> process
> is running at 15,388,052 ignoring the maximum settings.
> Thus the server is swapping memory to disk.
> How can i make sure the SQL server is limited in its memory usgage if it
> is
> ignoring the setting.
> Regards
> Toby
>|||Also note that max memory setting applies to the buffer pool only. The SQL
Server process consumes more memory than just its buffer pool, although the
buffer pool is often the largest, or at least should be, memory consumer.
Linchi
"TobiR" wrote:
> My SQL Server Ent 2005 64Bit Active / Passive Cluster is using 16.3GB of RAM
> and there is only 16GB in the server.
> I have the max memory for SQL server set at 12582912 (12GB) but the process
> is running at 15,388,052 ignoring the maximum settings.
> Thus the server is swapping memory to disk.
> How can i make sure the SQL server is limited in its memory usgage if it is
> ignoring the setting.
> Regards
> Toby
>|||On Jan 23, 2:12 am, TobiR <To...@.discussions.microsoft.com> wrote:
> My SQL Server Ent 2005 64Bit Active / Passive Cluster is using 16.3GB of RAM
> and there is only 16GB in the server.
> I have the max memory for SQL server set at 12582912 (12GB) but the process
> is running at 15,388,052 ignoring the maximum settings.
> Thus the server is swapping memory to disk.
> How can i make sure the SQL server is limited in its memory usgage if it is
> ignoring the setting.
> Regards
> Toby
12GB is 12288MB (which is what the max memory setting is in). Where
are you seeing max memory set to 12582912?|||Thanks for spotting my math issues! I had it set to 12TB instead of 12GB,
noticed it the other day, no wonder it was using eything it could get.
Thanks
Toby
"Jeffrey Williams" wrote:
> On Jan 23, 2:12 am, TobiR <To...@.discussions.microsoft.com> wrote:
> > My SQL Server Ent 2005 64Bit Active / Passive Cluster is using 16.3GB of RAM
> > and there is only 16GB in the server.
> >
> > I have the max memory for SQL server set at 12582912 (12GB) but the process
> > is running at 15,388,052 ignoring the maximum settings.
> >
> > Thus the server is swapping memory to disk.
> >
> > How can i make sure the SQL server is limited in its memory usgage if it is
> > ignoring the setting.
> >
> > Regards
> >
> > Toby
> 12GB is 12288MB (which is what the max memory setting is in). Where
> are you seeing max memory set to 12582912?
>
and there is only 16GB in the server.
I have the max memory for SQL server set at 12582912 (12GB) but the process
is running at 15,388,052 ignoring the maximum settings.
Thus the server is swapping memory to disk.
How can i make sure the SQL server is limited in its memory usgage if it is
ignoring the setting.
Regards
TobyDid you change the setting after the server was already running? Decreasing
memory won't necessarily release reserved mem without a reboot.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"TobiR" <TobiR@.discussions.microsoft.com> wrote in message
news:147CE1A0-1D90-431F-960A-E44BF36B695F@.microsoft.com...
> My SQL Server Ent 2005 64Bit Active / Passive Cluster is using 16.3GB of
> RAM
> and there is only 16GB in the server.
> I have the max memory for SQL server set at 12582912 (12GB) but the
> process
> is running at 15,388,052 ignoring the maximum settings.
> Thus the server is swapping memory to disk.
> How can i make sure the SQL server is limited in its memory usgage if it
> is
> ignoring the setting.
> Regards
> Toby
>|||Also note that max memory setting applies to the buffer pool only. The SQL
Server process consumes more memory than just its buffer pool, although the
buffer pool is often the largest, or at least should be, memory consumer.
Linchi
"TobiR" wrote:
> My SQL Server Ent 2005 64Bit Active / Passive Cluster is using 16.3GB of RAM
> and there is only 16GB in the server.
> I have the max memory for SQL server set at 12582912 (12GB) but the process
> is running at 15,388,052 ignoring the maximum settings.
> Thus the server is swapping memory to disk.
> How can i make sure the SQL server is limited in its memory usgage if it is
> ignoring the setting.
> Regards
> Toby
>|||On Jan 23, 2:12 am, TobiR <To...@.discussions.microsoft.com> wrote:
> My SQL Server Ent 2005 64Bit Active / Passive Cluster is using 16.3GB of RAM
> and there is only 16GB in the server.
> I have the max memory for SQL server set at 12582912 (12GB) but the process
> is running at 15,388,052 ignoring the maximum settings.
> Thus the server is swapping memory to disk.
> How can i make sure the SQL server is limited in its memory usgage if it is
> ignoring the setting.
> Regards
> Toby
12GB is 12288MB (which is what the max memory setting is in). Where
are you seeing max memory set to 12582912?|||Thanks for spotting my math issues! I had it set to 12TB instead of 12GB,
noticed it the other day, no wonder it was using eything it could get.
Thanks
Toby
"Jeffrey Williams" wrote:
> On Jan 23, 2:12 am, TobiR <To...@.discussions.microsoft.com> wrote:
> > My SQL Server Ent 2005 64Bit Active / Passive Cluster is using 16.3GB of RAM
> > and there is only 16GB in the server.
> >
> > I have the max memory for SQL server set at 12582912 (12GB) but the process
> > is running at 15,388,052 ignoring the maximum settings.
> >
> > Thus the server is swapping memory to disk.
> >
> > How can i make sure the SQL server is limited in its memory usgage if it is
> > ignoring the setting.
> >
> > Regards
> >
> > Toby
> 12GB is 12288MB (which is what the max memory setting is in). Where
> are you seeing max memory set to 12582912?
>
Memory Usage per DB-Instance
Hi,
I'm using SQL 2000 Standard and Windows 2000 Server Standard on one server
machine. Ther real RAM is 3,25GB. I'm using TWO DB-instances.
My question is, how much memory can I allocate (reserve) per instance?
Due to limitations in 2000 Standard Server i cannot use parameters like
/3GB, AWE or other in BOOT.INI
Can I use per example for instance1 2GB-RAM, and for instance2 512MB-RAM, or
both instances can only use TOGHETHER 2GB of RAM?
Thanks,
NickDo you have two SQL Server Instances or simply two Databases? If it is two
instances each one can use up to 2GB each if you had that much memory
available on the server minus what the OS needs. By default each instance
will dynamically manage the available memory with the OS and each instance
as needed. But you can specify a MAX Memory setting for each instance as
well. If you are talking about two databases in a single SQL Server
instance they all share the same memory.
Andrew J. Kelly SQL MVP
"NickD" <NickD@.discussions.microsoft.com> wrote in message
news:0BBD1416-C163-44C5-851E-21CF7EFC8C74@.microsoft.com...
> Hi,
> I'm using SQL 2000 Standard and Windows 2000 Server Standard on one server
> machine. Ther real RAM is 3,25GB. I'm using TWO DB-instances.
> My question is, how much memory can I allocate (reserve) per instance?
> Due to limitations in 2000 Standard Server i cannot use parameters like
> /3GB, AWE or other in BOOT.INI
> Can I use per example for instance1 2GB-RAM, and for instance2 512MB-RAM,
> or
> both instances can only use TOGHETHER 2GB of RAM?
> Thanks,
> Nick
>|||Yes, I have two instances not only simply two databases. I thought that
normally the OS uses 2GB for user progs (like SQL 2000) and the other 2Gb fo
r
kernel-mode.
Thanks,
Nick
"Andrew J. Kelly" wrote:
> Do you have two SQL Server Instances or simply two Databases? If it is tw
o
> instances each one can use up to 2GB each if you had that much memory
> available on the server minus what the OS needs. By default each instance
> will dynamically manage the available memory with the OS and each instance
> as needed. But you can specify a MAX Memory setting for each instance as
> well. If you are talking about two databases in a single SQL Server
> instance they all share the same memory.
> --
> Andrew J. Kelly SQL MVP
>
> "NickD" <NickD@.discussions.microsoft.com> wrote in message
> news:0BBD1416-C163-44C5-851E-21CF7EFC8C74@.microsoft.com...
>
>|||Every application such as SQL Server, Notepad<g> etc. has the ability to use
2GB each and the OS can use 2GB as well. Again assuming there is enough to
go around.
Andrew J. Kelly SQL MVP
"NickD" <NickD@.discussions.microsoft.com> wrote in message
news:47C7774C-43BC-4E2E-8705-44BB6324F107@.microsoft.com...[vbcol=seagreen]
> Yes, I have two instances not only simply two databases. I thought that
> normally the OS uses 2GB for user progs (like SQL 2000) and the other 2Gb
> for
> kernel-mode.
> Thanks,
> Nick
> "Andrew J. Kelly" wrote:
>
I'm using SQL 2000 Standard and Windows 2000 Server Standard on one server
machine. Ther real RAM is 3,25GB. I'm using TWO DB-instances.
My question is, how much memory can I allocate (reserve) per instance?
Due to limitations in 2000 Standard Server i cannot use parameters like
/3GB, AWE or other in BOOT.INI
Can I use per example for instance1 2GB-RAM, and for instance2 512MB-RAM, or
both instances can only use TOGHETHER 2GB of RAM?
Thanks,
NickDo you have two SQL Server Instances or simply two Databases? If it is two
instances each one can use up to 2GB each if you had that much memory
available on the server minus what the OS needs. By default each instance
will dynamically manage the available memory with the OS and each instance
as needed. But you can specify a MAX Memory setting for each instance as
well. If you are talking about two databases in a single SQL Server
instance they all share the same memory.
Andrew J. Kelly SQL MVP
"NickD" <NickD@.discussions.microsoft.com> wrote in message
news:0BBD1416-C163-44C5-851E-21CF7EFC8C74@.microsoft.com...
> Hi,
> I'm using SQL 2000 Standard and Windows 2000 Server Standard on one server
> machine. Ther real RAM is 3,25GB. I'm using TWO DB-instances.
> My question is, how much memory can I allocate (reserve) per instance?
> Due to limitations in 2000 Standard Server i cannot use parameters like
> /3GB, AWE or other in BOOT.INI
> Can I use per example for instance1 2GB-RAM, and for instance2 512MB-RAM,
> or
> both instances can only use TOGHETHER 2GB of RAM?
> Thanks,
> Nick
>|||Yes, I have two instances not only simply two databases. I thought that
normally the OS uses 2GB for user progs (like SQL 2000) and the other 2Gb fo
r
kernel-mode.
Thanks,
Nick
"Andrew J. Kelly" wrote:
> Do you have two SQL Server Instances or simply two Databases? If it is tw
o
> instances each one can use up to 2GB each if you had that much memory
> available on the server minus what the OS needs. By default each instance
> will dynamically manage the available memory with the OS and each instance
> as needed. But you can specify a MAX Memory setting for each instance as
> well. If you are talking about two databases in a single SQL Server
> instance they all share the same memory.
> --
> Andrew J. Kelly SQL MVP
>
> "NickD" <NickD@.discussions.microsoft.com> wrote in message
> news:0BBD1416-C163-44C5-851E-21CF7EFC8C74@.microsoft.com...
>
>|||Every application such as SQL Server, Notepad<g> etc. has the ability to use
2GB each and the OS can use 2GB as well. Again assuming there is enough to
go around.
Andrew J. Kelly SQL MVP
"NickD" <NickD@.discussions.microsoft.com> wrote in message
news:47C7774C-43BC-4E2E-8705-44BB6324F107@.microsoft.com...[vbcol=seagreen]
> Yes, I have two instances not only simply two databases. I thought that
> normally the OS uses 2GB for user progs (like SQL 2000) and the other 2Gb
> for
> kernel-mode.
> Thanks,
> Nick
> "Andrew J. Kelly" wrote:
>
Memory Usage per DB-Instance
Hi,
I'm using SQL 2000 Standard and Windows 2000 Server Standard on one server
machine. Ther real RAM is 3,25GB. I'm using TWO DB-instances.
My question is, how much memory can I allocate (reserve) per instance?
Due to limitations in 2000 Standard Server i cannot use parameters like
/3GB, AWE or other in BOOT.INI
Can I use per example for instance1 2GB-RAM, and for instance2 512MB-RAM, or
both instances can only use TOGHETHER 2GB of RAM?
Thanks,
Nick
Do you have two SQL Server Instances or simply two Databases? If it is two
instances each one can use up to 2GB each if you had that much memory
available on the server minus what the OS needs. By default each instance
will dynamically manage the available memory with the OS and each instance
as needed. But you can specify a MAX Memory setting for each instance as
well. If you are talking about two databases in a single SQL Server
instance they all share the same memory.
Andrew J. Kelly SQL MVP
"NickD" <NickD@.discussions.microsoft.com> wrote in message
news:0BBD1416-C163-44C5-851E-21CF7EFC8C74@.microsoft.com...
> Hi,
> I'm using SQL 2000 Standard and Windows 2000 Server Standard on one server
> machine. Ther real RAM is 3,25GB. I'm using TWO DB-instances.
> My question is, how much memory can I allocate (reserve) per instance?
> Due to limitations in 2000 Standard Server i cannot use parameters like
> /3GB, AWE or other in BOOT.INI
> Can I use per example for instance1 2GB-RAM, and for instance2 512MB-RAM,
> or
> both instances can only use TOGHETHER 2GB of RAM?
> Thanks,
> Nick
>
|||Yes, I have two instances not only simply two databases. I thought that
normally the OS uses 2GB for user progs (like SQL 2000) and the other 2Gb for
kernel-mode.
Thanks,
Nick
"Andrew J. Kelly" wrote:
> Do you have two SQL Server Instances or simply two Databases? If it is two
> instances each one can use up to 2GB each if you had that much memory
> available on the server minus what the OS needs. By default each instance
> will dynamically manage the available memory with the OS and each instance
> as needed. But you can specify a MAX Memory setting for each instance as
> well. If you are talking about two databases in a single SQL Server
> instance they all share the same memory.
> --
> Andrew J. Kelly SQL MVP
>
> "NickD" <NickD@.discussions.microsoft.com> wrote in message
> news:0BBD1416-C163-44C5-851E-21CF7EFC8C74@.microsoft.com...
>
>
|||Every application such as SQL Server, Notepad<g> etc. has the ability to use
2GB each and the OS can use 2GB as well. Again assuming there is enough to
go around.
Andrew J. Kelly SQL MVP
"NickD" <NickD@.discussions.microsoft.com> wrote in message
news:47C7774C-43BC-4E2E-8705-44BB6324F107@.microsoft.com...[vbcol=seagreen]
> Yes, I have two instances not only simply two databases. I thought that
> normally the OS uses 2GB for user progs (like SQL 2000) and the other 2Gb
> for
> kernel-mode.
> Thanks,
> Nick
> "Andrew J. Kelly" wrote:
I'm using SQL 2000 Standard and Windows 2000 Server Standard on one server
machine. Ther real RAM is 3,25GB. I'm using TWO DB-instances.
My question is, how much memory can I allocate (reserve) per instance?
Due to limitations in 2000 Standard Server i cannot use parameters like
/3GB, AWE or other in BOOT.INI
Can I use per example for instance1 2GB-RAM, and for instance2 512MB-RAM, or
both instances can only use TOGHETHER 2GB of RAM?
Thanks,
Nick
Do you have two SQL Server Instances or simply two Databases? If it is two
instances each one can use up to 2GB each if you had that much memory
available on the server minus what the OS needs. By default each instance
will dynamically manage the available memory with the OS and each instance
as needed. But you can specify a MAX Memory setting for each instance as
well. If you are talking about two databases in a single SQL Server
instance they all share the same memory.
Andrew J. Kelly SQL MVP
"NickD" <NickD@.discussions.microsoft.com> wrote in message
news:0BBD1416-C163-44C5-851E-21CF7EFC8C74@.microsoft.com...
> Hi,
> I'm using SQL 2000 Standard and Windows 2000 Server Standard on one server
> machine. Ther real RAM is 3,25GB. I'm using TWO DB-instances.
> My question is, how much memory can I allocate (reserve) per instance?
> Due to limitations in 2000 Standard Server i cannot use parameters like
> /3GB, AWE or other in BOOT.INI
> Can I use per example for instance1 2GB-RAM, and for instance2 512MB-RAM,
> or
> both instances can only use TOGHETHER 2GB of RAM?
> Thanks,
> Nick
>
|||Yes, I have two instances not only simply two databases. I thought that
normally the OS uses 2GB for user progs (like SQL 2000) and the other 2Gb for
kernel-mode.
Thanks,
Nick
"Andrew J. Kelly" wrote:
> Do you have two SQL Server Instances or simply two Databases? If it is two
> instances each one can use up to 2GB each if you had that much memory
> available on the server minus what the OS needs. By default each instance
> will dynamically manage the available memory with the OS and each instance
> as needed. But you can specify a MAX Memory setting for each instance as
> well. If you are talking about two databases in a single SQL Server
> instance they all share the same memory.
> --
> Andrew J. Kelly SQL MVP
>
> "NickD" <NickD@.discussions.microsoft.com> wrote in message
> news:0BBD1416-C163-44C5-851E-21CF7EFC8C74@.microsoft.com...
>
>
|||Every application such as SQL Server, Notepad<g> etc. has the ability to use
2GB each and the OS can use 2GB as well. Again assuming there is enough to
go around.
Andrew J. Kelly SQL MVP
"NickD" <NickD@.discussions.microsoft.com> wrote in message
news:47C7774C-43BC-4E2E-8705-44BB6324F107@.microsoft.com...[vbcol=seagreen]
> Yes, I have two instances not only simply two databases. I thought that
> normally the OS uses 2GB for user progs (like SQL 2000) and the other 2Gb
> for
> kernel-mode.
> Thanks,
> Nick
> "Andrew J. Kelly" wrote:
Subscribe to:
Posts (Atom)